The UNION operator in MySQL is used to combine the result sets of two or more SELECT statements into a single result set. This can be useful when you want to retrieve and display data from multiple tables or queries as if it were coming from a single source.
Syntax
The basic syntax for using UNION is as follows:
SELECT column1, column2, ... FROM table1 WHERE condition1 UNION SELECT column1, column2, ... FROM table2 WHERE condition2;
Here’s a breakdown of the syntax:
SELECT column1, column2, …: This part specifies the columns you want to retrieve from the tables.
FROM table1: This part specifies the table from which you want to retrieve the data.
WHERE condition1: This part specifies any conditions you want to apply to filter the data.
UNION: This is the keyword that combines the result sets of the two SELECT statements.
The second SELECT statement, FROM clause, and WHERE condition follow the same structure as the first, indicating the second set of data you want to combine.
It’s important to note a few key points about the UNION operator:
The number of columns selected in each SELECT statement must be the same.
The data types of the corresponding columns in each SELECT statement must be compatible.
By default, UNION removes duplicate rows from the result set. If you want to include duplicates, you can use the UNION ALL operator instead.
Example
Here’s an example:
SELECT employee_id, employee_name FROM employees WHERE department = 'HR' UNION SELECT employee_id, employee_name FROM temp_employees WHERE department = 'HR';
In this example, the UNION operator is used to combine the results of two SELECT statements, one from the “employees” table and another from the “temp_employees” table. The result is a unified set of employee information from both tables for those working in the HR department.
Keep in mind that while UNION is a powerful tool for combining data, it’s essential to ensure that the structure and types of data from different tables align appropriately for meaningful results.