What to know about SQL UNION vs UNION ALL

Stephanie Warner headshot
Stephanie Warner|Updated May 7, 2020
image (47)
image (47)

UNION

UNION is one of the four basic ‘set operatorsin SQL.  The UNION operator is used to combine the results of two or more SELECT Statement queries into a single result-set, excluding duplicate values. Using the UNION operator will not allow duplicate values in the results. (See UNION ALL for results allowing duplicate values.) There are specific protocols to follow when using UNION in a query:

  1. Each SELECT statement used with the UNION operator must have the same exact number of columns.

  2. The fields used in the SELECT statements must be in the same order as one another.

  3. The fields used in each SELECT statement must have corresponding data types, meaning they must be alike.

Syntax example

SELECT column_1, column_2, column_N FROM table_A UNION SELECT column_1, column_2, column_N FROM table_B;

Note: ‘column_1’ in each SELECT statement MUST be similar data types, ‘column_2’ in each SELECT statement must also be of similar data types, etc.

image (46)

UNION ALL

UNION ALL is also a ‘set operator’ in SQL Server. The UNION ALL operator performs -in a similar way to the UNION operator, in the sense that it too combines the result sets of two or more SELECT statements. The difference lies within the results; UNION ALL allows duplicate records in the results as UNION does not.

Syntax example

SELECT column_1, column_2, column_N FROM table_A UNION ALL SELECT column_1, column_2, column_N FROM table_B;

This blog post is part of our SQL series:
Stephanie Warner headshot
Stephanie Warner

Stephanie is a big time math nerd, avid snowboarder, and overall winter lover. She enjoys the torture of running and traveling to work Ragnar Relay races. From shredding the gnar to getting far too invested in murder/crime documentaries, you'll also find her petting all the doggos. She was an employee of PDQ.

Related articles