Join patterns for facts

What you will be able to do

• Join orders to stores and products in one query
• Use COALESCE for readable NULL labels
• Put outer-join filters in the ON clause when needed

Why this matters for data work

Real checks touch several dimensions. Filter placement on LEFT JOIN changes meaning: WHERE b.col = x turns a left join into an inner join.

Concepts

• Fact + multiple dims in one FROM.
• COALESCE(s.store_name, 'unknown').
• Filter dim on LEFT JOIN via ON … AND dim.active (pattern); for INNER, WHERE is fine.
• Select measures from the fact table only once.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
SELECT
  s.region,
  p.category,
  count(*) AS n,
  round(sum(o.line_total), 2) AS revenue
FROM orders o
JOIN stores s ON o.store_id = s.store_id
JOIN products p ON o.sku = p.sku
WHERE o.status = 'completed'
GROUP BY s.region, p.category
ORDER BY s.region, revenue DESC;

SELECT o.order_id, COALESCE(s.store_name, 'missing store') AS store_name
FROM orders o
LEFT JOIN stores s ON o.store_id = s.store_id
ORDER BY o.order_id
LIMIT 5;
SQL

What you should see

• A region × category revenue grid for completed orders.
• Store names filled (seed has full FK coverage).

Common mistakes

• Aggregating after a fanout join — validate count(*) vs fact grain.
• WHERE on left-joined table — accidentally drops NULLs you meant to keep.

Next

Subqueries