UNION, INTERSECT, and MINUS

UNION

Previously when we’ve combined tables we have pulled some columns from one and some columns from another and combined the results into the same rows. A UNION stacks all the resulting rows from one table on top of all the resulting rows from another table. The columns used must have the same names and data types in order for UNION to work.

UNION is uncommon because it relies on returning data that could have been stored in the same table. For example, If you wanted to see a list of all of your clients but the international client data was stored in one table and the domestic client data was stored in another, but the structure of the tables was similar, you could write a UNION query to combine the relevant data into one result:

SELECT intl_accounts.account,
       intl_accounts.office_location AS location
  FROM intl_accounts
    UNION
SELECT accounts.account,
       "USA" AS location
  FROM accounts
OFFSET 10

Because there is no column called location in the domestic data we had to create one to match with the data from the international table and we populated it with the default value “USA”. The OFFSET in the query was used to remove the first several rows of the results so we could see the section of the results table where the data from the first table ends and the data from the second table begins:

Though there aren’t enough rows shown to see them, the results table contains all the rows from both tables including duplicates.

INTERSECT

INTERSECT compares the data in two tables and returns only the rows of data using the specified columns that exist in both tables.

If we wanted to run a query to see only the accounts with both domestic and international offices we would write it with an INTERSECT:

SELECT intl_accounts.account
  FROM intl_accounts
    INTERSECT
SELECT accounts.account
  FROM accounts

We removed the OFFSET clause as there’s no need to for it in this query. We also had to remove the location column as had we left it, the query would have returned no results because every row returned would have been unique:

MINUS

MINUS compares the data in two tables and returns only the rows of data using the specified columns that exist in the first table but not the second. It would be possible to get all of the results from the second table that don’t exist in the first by switching the table order in the query.

In the last example we returned rows that had values in both tables. In order to write a query for all the rows that exist in the first table but not the second, we would use the query:

SELECT intl_accounts.account
  FROM intl_accounts
    MINUS
SELECT accounts.account
  FROM accounts

The results look like this:

If we flipped the table order and wrote the query like this:

SELECT accounts.account
  FROM accounts
    MINUS
SELECT intl_accounts.account
  FROM intl_accounts

The first several rows of the returned results would look like: