In SQL, the UNION operator is a powerful tool used to combine the result sets of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types. This blog post will guide you through the basics of using the UNION operator, its variations, and practical examples.
The Basics of UNION
The UNION operator allows you to combine the results of multiple SELECT queries into a single result set. By default, the UNION operator removes duplicate rows from the final result set. If you want to include duplicates, you can use the UNION ALL operator.
Syntax
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;Example Tables
Let’s consider two example tables: employees and contractors.
Basic UNION Example
Suppose you want to get a combined list of names and roles from both employees and contractors.
SELECT name, role
FROM employees
UNION
SELECT name, role
FROM contractors;
Using UNION ALL
To include duplicate rows in the result set, use the UNION ALL operator.
SELECT name, role
FROM employees
UNION ALL
SELECT name, role
FROM contractors;
Using ORDER BY with UNION
You can also sort the combined result set using the ORDER BY clause. Note that the ORDER BY clause should be placed at the end of the last SELECT statement.
UNION with Different Number of Columns
The UNION operator requires that each SELECT statement have the same number of columns. If the columns do not match, you can use dummy columns to align them.
Example:
Assume the employees table has an additional column department.
SELECT name, role, department
FROM employees
UNION
SELECT name, role, NULL AS department
FROM contractors;
