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.
If we wanted to run a query to see only the accounts with both domestic and international offices we would write it with an
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:
|Genco Pura Olive Oil Company|
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:
An introduction to the