Common SQL join mistakes, reusable templates, and correctness rules for production queries.
Mistakes that quietly produce wrong answers.
A bad join predicate can explode row counts.
-- Bad: missing relationship detail can multiply rows
SELECT *
FROM orders o
JOIN payments p ON p.customer_id = o.customer_id;Always verify that your join key represents the real relationship. In this example, `payment_id` or `order_id` might be required instead.
Filtering after the join can remove rows you meant to preserve.
-- 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';Move the right-side filter into `ON` if you still need unmatched left rows.
Totals can be wrong when a base row joins to many detail rows.
Joining customers → orders → line_items can multiply rows fast.
Aggregate carefully, often at the lowest needed grain first.This is one of the most common analytics bugs in SQL.
Joining `INT` to `TEXT` or differently formatted keys can block index use and create hidden bugs.
Ensure both sides use compatible types and semantics for the join key.Normalize data types in schema design whenever possible.
Compact reference snippets for everyday work.
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;Alias tables clearly, keep join predicates explicit, and select only the needed columns.
A safe template for “find missing related records.”
SELECT a.*
FROM table_a a
WHERE NOT EXISTS (
SELECT 1
FROM table_b b
WHERE b.a_id = a.id
);This template is highly reusable across auditing, cleanup, and onboarding workflows.