9 Difference between UNION and UNION ALL Operator.

Understanding the Difference between UNION and UNION ALL Operator in SQL

When querying data from multiple tables in SQL, there are two operators that can be used to combine the results of two or more SELECT statements: UNION and UNION ALL. While both operators are used to combine the results of two or more SELECT statements, there is a key difference between them that can impact their use in different situations.

UNION Operator

The UNION operator is used to combine the results of two or more SELECT statements into a single result set. The columns returned by each SELECT statement must be of the same data type and in the same order. The UNION operator will remove duplicate rows from the result set, so only distinct rows will be returned.

Let's consider an example where we want to combine the results of two SELECT statements, one that retrieves all the employees who work in the IT department, and the other that retrieves all the employees who work in the HR department. We can use the UNION operator to combine the results of these two SELECT statements into a single result set that contains all the employees who work in either the IT or HR department:

SELECT employee_id, first_name, last_name, department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE department_name = 'IT'
UNION
SELECT employee_id, first_name, last_name, department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE department_name = 'HR';

UNION ALL Operator

The UNION ALL operator is also used to combine the results of two or more SELECT statements into a single result set. However, unlike the UNION operator, the UNION ALL operator will not remove duplicate rows from the result set. This means that if there are any duplicate rows in the result set, they will be included in the final output.

Using the same example as before, let's consider a scenario where we want to retrieve all the employees who work in the IT department, as well as all the employees who work in both the IT and HR departments. In this case, we would want to use the UNION ALL operator instead of the UNION operator, since there may be some employees who work in both departments and we do not want to remove duplicate rows:

SELECT employee_id, first_name, last_name, department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE department_name = 'IT'
UNION ALL
SELECT employee_id, first_name, last_name, department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE department_name IN ('IT', 'HR');