SQL Join Patterns and Pitfalls

-- Bad: missing relationship detail can multiply rows
SELECT *
FROM orders o
JOIN payments p ON p.customer_id = o.customer_id;

# A bad join predicate can explode row counts.

-- Problematic if you want all customers
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid';

# Filtering after the join can remove rows you meant to preserve.

Joining customers → orders → line_items can multiply rows fast.
Aggregate carefully, often at the lowest needed grain first.

# Totals can be wrong when a base row joins to many detail rows.

Ensure both sides use compatible types and semantics for the join key.

# Joining `INT` to `TEXT` or differently formatted keys can block index use and create hidden bugs.

SELECT a.col1,
       b.col2
FROM table_a a
JOIN table_b b
  ON b.a_id = a.id
WHERE a.status = 'active'
ORDER BY a.created_at DESC;

# A clean, readable structure for production queries.

SELECT a.*
FROM table_a a
WHERE NOT EXISTS (
  SELECT 1
  FROM table_b b
  WHERE b.a_id = a.id
);

# A safe template for “find missing related records.”