SQL UNION Operator - Complete Beginner Tutorial

What is the SQL UNION Operator?

The UNION operator combines the results of two or more SELECT queries into a single result set.

It automatically removes duplicate rows, ensuring each row in the result is unique.

UNION requires that all SELECT statements have the same number of columns and compatible data types in corresponding positions.

• Combines multiple SELECT query results.
• Removes duplicate rows by default.
• Requires matching column count and compatible data types.
• Returns a distinct set of rows.

SQL UNION Syntax

The basic syntax of the UNION operator is straightforward.

You write multiple SELECT statements separated by the UNION keyword.

Example of SQL UNION

Here is a simple example combining two SELECT queries using UNION.

Difference Between UNION and UNION ALL

While UNION removes duplicate rows, UNION ALL includes all rows from the combined queries, including duplicates.

UNION ALL is faster because it does not perform the duplicate elimination step.

Use UNION when you want unique rows, and UNION ALL when duplicates are acceptable or desired.

• UNION removes duplicates; UNION ALL does not.
• UNION ALL performs better in large datasets.
• Choose based on whether duplicates matter in your result.

Common Errors and How to Fix Them

Errors often occur when the SELECT statements in a UNION have different numbers of columns or incompatible data types.

Ensure all SELECT queries have the same number of columns and matching data types in the same order.

Another common mistake is forgetting that UNION removes duplicates, which can affect expected results.

• Mismatch in column count causes syntax errors.
• Incompatible data types cause conversion errors.
• Unexpected removal of duplicates can confuse results.