MySQL UNION Operator

Last Updated: 19 July, 2023

In MySQL, the UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. The UNION operator removes duplicate rows from the combined result set by default.

Each SELECT statement within the UNION operator must have the same number of fields in the result sets with similar data types.

The basic syntax for using UNION in MySQL is as follows:

 SELECT column1, column2, ...
 FROM table1
 WHERE condition
 UNION
 SELECT column1, column2, ...
 FROM table2
 WHERE condition;

Here are a few important points to keep in mind when using UNION in MySQL:

1. The number and data types of columns in each SELECT statement must be the same.
2. The column names in the final result set are determined by the column names in the first SELECT statement.
3. By default, UNION removes duplicate rows from the result set. If we want to include duplicate rows, we can use the UNION ALL operator instead.
4. The column names and data types are determined by the first SELECT statement. If necessary, we can use column aliases to rename the columns in subsequent SELECT statements.
5. The UNION operator evaluates the ORDER BY clause only once, which is applied to the final result set. If we want to apply ordering to individual SELECT statements, we can use parentheses.

Here's an example to illustrate the usage of UNION

 SELECT name, age, city
 FROM employees
 WHERE department = 'Sales'
 UNION
 SELECT name, age, city
 FROM employees
 WHERE department = 'Marketing';

This query combines the result sets of two SELECT statements, retrieving the names, ages, and cities of employees in the Sales and Marketing departments. The UNION operator removes any duplicate rows from the combined result set.

Remember to adjust the table names, column names, and conditions according to our specific database schema and requirements.