SQL UNION

The UNION operator in SQL is 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, and the columns must also be in the same order.

Key Points:

• The UNION operator selects only distinct values by default.
• If you want to include duplicate values, use UNION ALL.
• The column names in the result set are usually taken from the first SELECT statement.

Syntax:

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

Example:

Consider two tables, employees and contractors, with the following data:

Table: employees

Table: contractors

To combine the names from both tables into a single result set, we can use the UNION operator:

SELECT name, department
FROM employees
UNION
SELECT name, department
FROM contractors;

Output:

Using UNION ALL:

If we use UNION ALL instead, all rows including duplicates will be included:

SELECT name, department
FROM employees
UNION ALL
SELECT name, department
FROM contractors;

Output with UNION ALL:

Since there were no duplicates in our example data, the output remains the same. However, if there were any duplicate rows between employees and contractors, UNION ALL would include them all in the final result set, whereas UNION would filter them out.

Practical Example with Duplicate Values:

Updated Table: contractors

Using UNION:

SELECT name, department
FROM employees
UNION
SELECT name, department
FROM contractors;

Output:

Using UNION ALL:

SELECT name, department
FROM employees
UNION ALL
SELECT name, department
FROM contractors;

Output with UNION ALL:

As you can see, UNION ALL includes the duplicate entry of "Charlie" in the final result set.

Summary:

• Use UNION to combine result sets and automatically remove duplicates.
• Use UNION ALL to combine result sets without removing duplicates.