Using Unions

Objective

After completing this lesson, you will be able to retrieve data from multiple sets.

Unions Overview

The SELECT statement is more flexible than has been shown so far. It can be used to combine data from one or more tables. This may involve combining rows or columns in novel ways.

You can combine the results of several partial queries to form the union of the results, or to determine their intersection or difference:

UNION ALL and UNION merge the results of partial queries to form their union.
INTERSECT is used to determine the elements contained in all partial query results.
EXCEPT is used to determine the elements contained in one partial result, but not the second partial result.

UNION and UNION ALL

UNION [ALL]

You can combine the result sets of multiple queries using UNION [ALL].

The individual result sets must have the same number of columns.
The corresponding result columns must have compatible data types.
The column names of the resulting output table are based on the first SELECT statement.

Code Snippet

123456789
SELECT PNr, Name
    FROM Official
WHERE Salary = 'A09'

          UNION ALL

SELECT OwnerID, Name 
    FROM Owner
WHERE Birthday >= '1977-05-21';

PNR	NAME
P01	Mr A
P03	Ms C
P06	Mr F
H08	Mr X
H09	Ms Y
H10	Mr Z

UNION ALL Can Lead to Duplicates

If the results of multiple partial queries overlap, the UNION ALL operator includes duplicates.

Code Snippet

123456789
SELECT PlateNumber, Brand, Color
    FROM Car
WHERE Brand = 'Mercedes'

          UNION ALL

SELECT PlateNumber, Brand, Color 
    FROM Car
WHERE Color = 'white';

PLATENUMBER	BRAND	COLOR
HD-AL 1002	Mercedes	white
HD-MM 3206	Mercedes	black
HD-MM 1977	Mercedes	white
HD-AL 1002	Mercedes	white
HD-MM 1977	Mercedes	white

The duplicates are marked in bold.

Avoiding Duplicates Using UNION

You use UNION instead of UNION [ALL] to eliminate duplicates.

Code Snippet

123456789
SELECT PlateNumber, Brand, Color
    FROM Car
WHERE Brand = 'Mercedes'

          UNION 

SELECT PlateNumber, Brand, Color 
    FROM Car
WHERE Color = 'white';

PLATENUMBER	BRAND	COLOR
HD-AL 1002	Mercedes	white
HD-MM 3206	Mercedes	black
HD-MM 1977	Mercedes	white

UNION [ALL] Can Handle More Than Two SELECTs

You can use UNION [ALL] to combine result tables of multiple queries.

Code Snippet

123456789101112131415
SELECT PlateNumber, Brand, Color
    FROM Car
WHERE Brand = 'BMW'

          UNION 

SELECT PlateNumber, Brand, Color 
    FROM Car
WHERE Color = 'yellow'

          UNION

SELECT PlateNumber, Brand, Color 
    FROM Car
WHERE Color = 'orange';

PLATENUMBER	BRAND	COLOR
HD-JA 1972	BMW	blue
HD-MT 507	BMW	black
HD-MM 208	BMW	green
HD-VW 1999	Audi	yellow
HD-Y 333	Audi	orange

INTERSECT and EXCEPT

INTERSECT [DISTINCT]

INTERSECT returns records that exist in all query results.

Code Snippet

123456789
SELECT PlateNumber, Brand, Color
    FROM Car
WHERE Brand = 'Mercedes’

          INTERSECT

SELECT PlateNumber, Brand, Color 
    FROM Car
WHERE Color = 'white';

PLATENUMBER	BRAND	COLOR
HD-AL 1002	Mercedes	white
HD-MM 1977	Mercedes	white

EXCEPT [DISTINCT]

EXCEPT returns results from the first query that are NOT available in subsequent queries.

Code Snippet

12345678910
SELECT PlateNumber, Brand, Color
    FROM Car
WHERE Brand = 'Mercedes’

          EXCEPT 

SELECT PlateNumber, Brand, Color 
    FROM Car
WHERE Color = 'white';

PLATENUMBER	BRAND	COLOR
HD-MM 3206	Mercedes	black

Relationships Between the UNION [ALL], INTERSECT, and EXCEPT Operators

Next lesson