lesson 26 Joins & sets · advanced

Combine sets with UNION

Combine query results with or without duplicates.

the idea

UNION stacks compatible query outputs and removes duplicate rows. UNION ALL retains duplicates. Both sides need the same number of columns in matching positions.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT city
FROM customers
WHERE city='Chicago'
UNION
SELECT city
FROM customers
WHERE city='Austin';

Watch for: UNION combines rows vertically; JOIN combines columns horizontally.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Build two compatible city lists. Chicago and Austin rows each produce a city column. UNION stacks compatible selects and drops duplicates.

   step-1.sql Run

   sql›

   SELECT city
   FROM customers
   WHERE city IN ('Chicago', 'Austin')
   ORDER BY city;

2. 2

   Stack sets with UNION. UNION removes duplicates across the stacked rows. UNION ALL would keep every duplicate.

   step-2.sql Run

   sql›

   SELECT city
   FROM customers
   WHERE city = 'Chicago'
   UNION
   SELECT city
   FROM customers
   WHERE city = 'Austin';