SQL Server UNION vs UNION ALL Explained

UNION vs UNION ALL

Both combine the results of two or more SELECT queries.

Feature	`UNION`	`UNION ALL`
Duplicate rows	Removes duplicates	Keeps duplicates
Performance	Slower	Faster
Sorting/comparison	Required to remove duplicates	No duplicate checking
Recommended when	Only unique results are required	All results are required

Example

Table A

Name
Ravi
Priya

Table B

Name
Priya
Ahmed

Using UNION

SELECT Name FROM TableA
UNION
SELECT Name FROM TableB;

Result:

Ravi
Priya
Ahmed

Priya appears only once because UNION removes duplicates.

Using UNION ALL

SELECT Name FROM TableA
UNION ALL
SELECT Name FROM TableB;

Result:

Ravi
Priya
Priya
Ahmed

Priya appears twice because UNION ALL keeps duplicates.

Important rules

For both operators:

Each SELECT must return the same number of columns.
Corresponding columns must have compatible data types.
Column order must match.
The final column names come from the first SELECT.
Use a single ORDER BY after the final query.

SELECT EmployeeId, EmployeeName
FROM PermanentEmployees

UNION ALL

SELECT EmployeeId, EmployeeName
FROM ContractEmployees

ORDER BY EmployeeName;

Interview answer

UNION combines results and removes duplicate rows, while UNION ALL combines results without removing duplicates. Therefore, UNION ALL is generally faster.

UNION vs UNION ALL

UNION vs UNION ALL

Example

Using UNION

Using UNION ALL

Important rules

Interview answer

Keep Learning - More From This Category