Understanding the MySQL UNION Keyword

The MySQL UNION keyword is used to combine the results of two or more SELECT statements into a single result set. This can be particularly useful for retrieving data from multiple tables that share similar structures.

Key Concepts

• Combining Results: UNION allows you to combine results from different queries. Each query must have the same number of columns and corresponding data types.
• Distinct Rows: By default, UNION removes duplicate rows from the result set. If you want to include duplicates, you can use UNION ALL.
• Column Names: The column names in the result set will be taken from the first SELECT statement.

Syntax

The basic syntax for using UNION is:

SELECT column1, column2, ...
FROM table1
WHERE condition1

UNION

SELECT column1, column2, ...
FROM table2
WHERE condition2;

Example

Using UNION

Suppose you have two tables, employees and contractors, both containing names and email addresses. You can combine their email addresses using UNION as follows:

SELECT name, email FROM employees
UNION
SELECT name, email FROM contractors;

Using UNION ALL

If you want to include duplicate email addresses in the result, you can use UNION ALL:

SELECT name, email FROM employees
UNION ALL
SELECT name, email FROM contractors;

Important Notes

• Order of Columns: Ensure that the columns in each SELECT statement match in order and type.
• Performance: Using UNION can be slower than UNION ALL because it requires MySQL to check for and remove duplicates.

Conclusion

The UNION keyword in MySQL is a powerful tool for combining results from multiple queries. It helps in consolidating data from different tables while maintaining the structure of the results. Remember to choose between UNION and UNION ALL based on whether you need to include duplicates in your final output.