Multi-column join conditions

Sometimes one column isn't enough to match rows. Sales and targets only match on the same store AND the same product, so the join needs both.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Joining on two columns at once

SELECT s.store_id, s.product_id, s.units_sold, t.target_units
FROM store_sales s
JOIN targets t ON s.store_id = t.store_id AND s.product_id = t.product_id

Result:

store_id	product_id	units_sold	target_units
S1	P1	40	50
S1	P2	25	20
S2	P1	30	30
S2	P2	10	15

Step by step

Store and product from the sales, plus the target.

SELECT s.store_id, s.product_id, s.units_sold, t.target_units

Short aliases (s and t) keep the query readable.

SELECT s.store_id, s.product_id, s.units_sold, t.target_units
FROM store_sales s

Same store AND same product.

SELECT s.store_id, s.product_id, s.units_sold, t.target_units
FROM store_sales s
JOIN targets t ON s.store_id = t.store_id AND s.product_id = t.product_id;

Practice

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

1. Join store_sales to targets on store_id and product_id. Show store_id, product_id, units_sold and target_units.

SELECT s.store_id, s.product_id, s.units_sold, t.target_units FROM store_sales s JOIN targets t ON s.store_id = t.store_id AND s.product_id = t.product_id

2. Join store_sales to prices on store_id and product_id. Show store_id, product_id and price.

SELECT s.store_id, s.product_id, p.price FROM store_sales s JOIN prices p ON s.store_id = p.store_id AND s.product_id = p.product_id

3. Show store_id, product_id and units_sold minus target_units as gap, matching on store and product.

SELECT s.store_id, s.product_id, s.units_sold - t.target_units AS gap FROM store_sales s JOIN targets t ON s.store_id = t.store_id AND s.product_id = t.product_id

4. Show store_id, product_id and revenue (units_sold × price) as revenue, matching sales to prices on store and product.

SELECT s.store_id, s.product_id, s.units_sold * p.price AS revenue FROM store_sales s JOIN prices p ON s.store_id = p.store_id AND s.product_id = p.product_id

5. Show the store_id and product_id that missed their target (units_sold below target_units).

SELECT s.store_id, s.product_id FROM store_sales s JOIN targets t ON s.store_id = t.store_id AND s.product_id = t.product_id WHERE s.units_sold < t.target_units

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