Can you describe the difference between UNION and UNION ALL?

In SQL Server, ‘UNION‘ and ‘UNION ALL‘ are used to combine data from two or more tables.

‘UNION‘ returns only distinct rows from the combined tables, whereas ‘UNION ALL‘ returns all rows, including duplicates, from the combined tables.

Here’s an example to illustrate the difference between the two:

Let’s say we have two tables, ‘employees‘ and ‘staff‘, with the following data:

employees table:
id   name    department
1    John    Sales
2    Jane    Marketing
3    Bob     IT

staff table:
id   name    department
1    John    Sales
3    Bob     IT
4    Sue     HR

If we execute the following ‘UNION‘ query:

SELECT id, name, department FROM employees
UNION
SELECT id, name, department FROM staff

The result would be:

id   name    department
1    John    Sales
2    Jane    Marketing
3    Bob     IT
4    Sue     HR

Notice that only distinct rows are returned, so John and Bob are not duplicated.

If we execute the following ‘UNION ALL‘ query:

SELECT id, name, department FROM employees
UNION ALL
SELECT id, name, department FROM staff

The result would be:

id   name    department
1    John    Sales
2    Jane    Marketing
3    Bob     IT
1    John    Sales
3    Bob     IT
4    Sue     HR

Notice that all rows are returned, including duplicates. In this case, John and Bob appear twice since they are in both tables.

In general, ‘UNION‘ is useful when you want to eliminate duplicates from the combined results, while ‘UNION ALL‘ is useful when you want to include duplicates. However, ‘UNION‘ requires more processing to remove duplicates, so it can be slower than ‘UNION ALL‘ for large datasets.