What is the difference between UNION and UNION ALL?

UNION and UNION ALL are SQL operators for combining the results of two or more queries.

• UNION combines results and automatically removes duplicate rows. This can lead to additional sorting and comparison costs.

• UNION ALL combines results without removing duplicates, meaning it returns all rows from all queries, including repetitions.

Example:

SELECT name FROM employees
UNION
SELECT name FROM managers;

Returns unique names from both tables.

SELECT name FROM employees
UNION ALL
SELECT name FROM managers;

Returns all names, including duplicates if they exist in both tables.

Use UNION if you need unique rows, and UNION ALL for better performance when duplicates are acceptable.