Joins

What you will be able to do

• Write INNER JOIN and LEFT JOIN on retail keys
• Read join results without unexpected row multiplication
• Use table aliases consistently

Why this matters for data work

Facts live in orders; attributes live in stores and products. Joins attach dimensions. A bad join key fans out rows and double-counts revenue.

Concepts

• a INNER JOIN b ON a.key = b.key — only matches.
• a LEFT JOIN b ON … — keep all a rows; b columns NULL if no match.
• Aliases: FROM orders o JOIN stores s ON o.store_id = s.store_id.
• Fanout — joining a one-to-many twice without care multiplies rows.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
SELECT o.order_id, s.store_name, s.region, o.line_total
FROM orders o
INNER JOIN stores s ON o.store_id = s.store_id
WHERE o.status = 'completed'
ORDER BY o.line_total DESC
LIMIT 5;

SELECT s.region, count(*) AS completed_orders, round(sum(o.line_total), 2) AS revenue
FROM orders o
JOIN stores s ON o.store_id = s.store_id
WHERE o.status = 'completed'
GROUP BY s.region
ORDER BY revenue DESC;

SELECT p.category, count(*) AS n
FROM orders o
JOIN products p ON o.sku = p.sku
GROUP BY p.category
ORDER BY n DESC;
SQL

What you should see

• Store names next to high-value orders.
• Revenue by region.
• Category counts (orders.sku matches products).

Common mistakes

• Join on the wrong key — silent wrong metrics.
• INNER JOIN when you needed LEFT — dropped unmatched facts.
• No alias — ambiguous column errors.

Next

Join patterns for facts