Set Operations

Set operations merge data in to one set based on column definitions and the data contained within each column.

The four set operations are:

• UNION

• UNION ALL

• INTERSECT

• EXCEPT

The number of columns need to match. If number of columns don't match it'll result in an error.

<query 1> <set operation> <query 2>
SELECT <column> FROM <table 1> <set operation> SELECT <column> FROM <table 2>;
SELECT <column>, <column> FROM <table 1> <set operation> SELECT <column>, <column> FROM <table 2>;

UNION Examples

Unions return all distinct values from both data sets with no duplicates.

Get a list of unique restaurants from both north and south malls.

SELECT store FROM mall_south WHERE type = "restaurant"
    UNION
SELECT store FROM mall_north WHERE type = "restaurant";

Get a list of unique classes taught in two schools. Order them by their class name.

SELECT evening_class FROM school_1 UNION SELECT evening_class FROM school_2
    ORDER BY evening_class ASC;

UNION ALL

Union all returns all values from both data sets – with duplicates.

Get a list of all names for boys and girls and order them by name.

SELECT boy_name AS name FROM boy_baby_names
    UNION ALL
SELECT girl_name AS name FROM girl_baby_names
    ORDER by name;

INTERSECT

Returns only values that are in both data sets.

Get list of classes offered in both schools.

SELECT evening_class FROM school_1 INTERSECT SELECT evening_class FROM school_2
    ORDER BY evening_class ASC;

Get list of restaurants at both mall locations.

SELECT store FROM mall_south WHERE type = "restaurant"
    INTERSECT
SELECT store FROM mall_north WHERE type = "restaurant";

EXCEPT

Returns data from the first data set that's not in the second.

Get a list of local stores in a mall.

SELECT store FROM mall
    EXCEPT
SELECT store FROM all_stores WHERE type = "national"