The UNION operator in SQL is used to combine the results of two or more SELECT statements into a single result set. The UNION operator eliminates duplicates from the results and returns only unique rows.
Syntax of SQL UNION Operator
SELECT column1, column2, column3, ... FROM table1 UNION SELECT column1, column2, column3, ... FROM table2;
In the above syntax, the UNION operator is used to combine the results of two SELECT statements. Both SELECT statements must have the same number of columns and the data type of each column in both SELECT statements must be compatible.
Example of SQL UNION Operator
Consider the following two tables, “employees” and “employees_backup”:
| EmployeeID | EmployeeName | Department |
|---|---|---|
| E001 | John | IT |
| E002 | Jane | HR |
| E003 | Bob | Marketing |
| EmployeeID | EmployeeName | Department |
|---|---|---|
| E004 | Alice | Sales |
| E005 | David | IT |
Now, if we want to combine the results of both tables and find all employees, we can use the following SQL UNION statement:
SELECT EmployeeID, EmployeeName, Department FROM employees UNION SELECT EmployeeID, EmployeeName, Department FROM employees_backup;
The output of this SQL UNION statement would be:
| EmployeeID | EmployeeName | Department |
|---|---|---|
| E001 | John | IT |
| E002 | Jane | HR |
| E003 | Bob | Marketing |
| E004 | Alice | Sales |
| E005 | David | IT |
As you can see, the output includes all unique employees from both “employees” and “employees_backup” tables. The duplicates are eliminated and only unique rows are displayed in the result set.
UNION ALL Operator
The UNION ALL operator works similar to the UNION operator, but it returns all the rows including duplicates from the SELECT statements. The syntax of UNION ALL operator is:
SELECT column1, column2, column3, ... FROM table1 UNION ALL SELECT column1, column2, column3, ... FROM table2;
Let’s consider the same two tables as above, “employees” and “employees_backup”. If we want to combine the results of both tables and find all employees including duplicates, we can use the following SQL UNION ALL statement:
SELECT EmployeeID, EmployeeName, Department FROM employees UNION ALL SELECT EmployeeID, EmployeeName, Department FROM employees_backup;
The output of this SQL UNION ALL statement would be:
| EmployeeID | EmployeeName | Department |
|---|---|---|
| E001 | John | IT |
| E002 | Jane | HR |
| E003 | Bob | Marketing |
| E004 | Alice | Sales |
| E005 | David | IT |
As you can see, the output includes all rows from both “employees” and “employees_backup” tables including duplicates. The duplicates are not eliminated in this case.
Conclusion
The UNION operator in SQL is a useful tool to combine the results of two or more SELECT statements and return only unique rows. The UNION ALL operator is similar to the UNION operator but it returns all the rows including duplicates. Both the UNION and UNION ALL operators are used in different scenarios depending on the requirements and the desired output.
