$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Combining results with UNION #

Every join we've looked at so far combines tables side by side, adding columns from a second table onto each row. UNION does something different: it stacks the results of two SELECT statements on top of each other, as long as both statements return the same number of columns.

SELECT
    column_1
FROM
    table_1
WHERE
    condition_1
UNION
SELECT
    column_1
FROM
    table_2
WHERE
    condition_2;
  • Both SELECT statements must return the same number of columns.
  • UNION combines the rows from both statements into a single result, and removes any duplicate rows.
  • If you want to keep duplicates instead of removing them, use UNION ALL.

Example #

SELECT
    name
FROM
    products
WHERE
    category = 'Home'
UNION
SELECT
    name
FROM
    products
WHERE
    category = 'Office';

Result:

+-------------+
|     name    |
+-------------+
|  Coffee Mug |
|  Desk Lamp  |
|   Notebook  |
|   Backpack  |
+-------------+

This gets us every Home product stacked together with every Office product, as a single column of names. Note that this particular example could also be written with WHERE category IN ('Home', 'Office'), since both SELECT statements pull from the same table and column, UNION is more useful once the two statements pull from different tables or different sets of columns, but the mechanics are easiest to see when the columns line up like this.

Exercise #

Write a query that returns the name of every product in the 'Electronics' category, UNION-ed with the name of every product in the 'Fitness' category.

  • The result has a name column.
  • The result contains exactly the products from the Electronics and Fitness categories, with no duplicates.
Solution 
SELECT
    name
FROM
    products
WHERE
    category = 'Electronics'
UNION
SELECT
    name
FROM
    products
WHERE
    category = 'Fitness';
Output will be displayed here