The UNION operator in MySQL is used to combine the results of two or more SELECT queries into a single result set. This operation merges the rows from multiple queries and eliminates duplicate rows, providing a unified view of the data. The UNION operator is useful when you need to aggregate results from different tables or queries that have similar structures.

Syntax

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

• column1, column2, ...: Columns to be selected from each query.
• table1, table2: The tables to be queried.
• condition: The condition to filter the rows.

Key Points

1. Column Matching:

   • The SELECT queries combined with UNION must have the same number of columns.
   • The corresponding columns in each SELECT statement must have compatible data types.

2. Duplicate Rows:

   • UNION removes duplicate rows from the result set.
   • To include duplicate rows, use UNION ALL.

3. Order of Columns:

   • The columns in the result set will be in the same order as the columns in the SELECT statements.

Examples

1. Basic UNION Example

   Combine results from two tables, employees and contractors, to get a list of all names:

   SELECT name
   FROM employees
   UNION
   SELECT name
   FROM contractors;
   

   This query retrieves a list of names from both tables, removing any duplicates.

2. UNION with Different Conditions

   Combine results of employees and contractors who have worked on a specific project:

   SELECT name, 'Employee' AS role
   FROM employees
   WHERE project_id = 1
   UNION
   SELECT name, 'Contractor' AS role
   FROM contractors
   WHERE project_id = 1;
   

   This query retrieves names of individuals from both tables who have worked on project 1, and it also includes a column indicating their role.

3. Using UNION ALL to Include Duplicates

   Retrieve all names from employees and contractors, including duplicates:

   SELECT name
   FROM employees
   UNION ALL
   SELECT name
   FROM contractors;
   

   This query returns all names from both tables, including duplicates.

4. Combining Results with Different Columns

   If the columns have different names but compatible data types, you can still use UNION:

   SELECT employee_id AS id, employee_name AS name
   FROM employees
   UNION
   SELECT contractor_id AS id, contractor_name AS name
   FROM contractors;
   

   Here, UNION is used to combine the results of two queries with differently named columns but with similar data.

Performance Considerations

• Duplicates Removal: The default UNION operation removes duplicate rows, which can add overhead. If duplicates are not a concern, use UNION ALL for better performance.
• Indexing: Proper indexing on the tables involved can improve the performance of queries combined with UNION.