SQL UNION

The UNION operator in SQL is used to combine the results of two or more SELECT statements into a single result set. It eliminates duplicate rows by default, and only unique rows are returned. The number of columns and their data types must be the same in all SELECT statements for the UNION to work correctly.

 

Syntax of SQL UNION

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

• SELECT statements: The individual queries whose results you want to combine.
• UNION: The operator used to combine the results.
• columns: Each SELECT statement must have the same number of columns, and the corresponding columns must have compatible data types.

 

Key Points

• The UNION operator removes duplicate records.
• To include duplicates in the result, you can use UNION ALL.
• The order of the columns in each SELECT statement must be the same, and the data types of each corresponding column must be compatible.

 

Example of SQL UNION

Consider the following two tables:

Employees Table 1 (Department 1):

Employees Table 2 (Department 2):

You can use the UNION operator to get a list of all unique employees across both departments:

SELECT name, department
FROM employees_1
UNION
SELECT name, department
FROM employees_2;

Output:

Explanation:

• The result set contains all unique employees from both the employees_1 and employees_2 tables.
• Duplicates (if any) are eliminated.

 

SQL UNION with Different Conditions

You can also apply different conditions on each SELECT statement in a UNION. For example, if you want to get employees who belong to different departments but have a specific condition applied to each query:

SELECT name, department
FROM employees_1
WHERE department = 'HR'
UNION
SELECT name, department
FROM employees_2
WHERE department = 'IT';

Output:

 

Using UNION ALL

The UNION ALL operator is similar to UNION, but it does not remove duplicates. It combines all rows, including duplicates, from the SELECT statements.

SELECT name, department
FROM employees_1
UNION ALL
SELECT name, department
FROM employees_2;

Output:

Explanation:

• This includes all rows, even if there are duplicates between the two tables.

 

Summary

The UNION operator in SQL is used to combine the results of two or more SELECT queries into one result set. By default, it removes duplicate records, but using UNION ALL will retain all rows, including duplicates. To use UNION, the columns selected in each query must have the same number of columns and compatible data types.