UNION and UNION ALL

UNION stacks the rows from two queries into one result.

About 5–10 minutes · no sign-up needed

Example

Combining Retail Sales Rows with UNION and UNION ALL

SELECT order_id, customer_id, channel, revenue, order_date FROM online_orders
UNION ALL
SELECT order_id, customer_id, channel, revenue, order_date FROM store_sales
ORDER BY order_date

Result:

order_id	customer_id	channel	revenue	order_date
O2001	C101	Online	129.99	2024-03-01
O2002	C102	Online	54.5	2024-03-02
S3001	C102	In-Store	54.5	2024-03-02
O2003	C103	Online	89.0	2024-03-03
S3002	C106	In-Store	75.0	2024-03-03

Step by step

After SELECT, list the column names you want, separated by commas.
```
SELECT order_id, customer_id, channel, revenue, order_date
```

FROM tells SQL which table to pull the data from.

SELECT order_id, customer_id, channel, revenue, order_date
FROM online_orders

UNION ALL stacks two result sets, keeping every row including duplicates.

SELECT order_id, customer_id, channel, revenue, order_date
FROM online_orders
UNION ALL

Practice

Try each one in your head first, then open the answer.

1. List every customer_id that appears in orders or reviews, without duplicates.

SELECT customer_id FROM orders UNION SELECT customer_id FROM reviews

2. List every customer_id from cart and wishlist in one column, keeping duplicates.

SELECT customer_id FROM cart UNION ALL SELECT customer_id FROM wishlist

3. List every product_id that appears in cart or wishlist, without duplicates.

SELECT product_id FROM cart UNION SELECT product_id FROM wishlist

4. List all revenue amounts from orders and store_sales in one column, keeping duplicates.

SELECT revenue FROM orders UNION ALL SELECT revenue FROM store_sales

5. List every category that appears in products or promotions, without duplicates.

SELECT category FROM products UNION SELECT category FROM promotions

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed