JOINs combine rows from related tables. Use INNER JOIN when both sides must exist, and LEFT JOIN when you need to keep every row from the left table even if matches are missing.
- Join on stable keys such as customer_id or order_id, not descriptive names.
- Check row counts before and after joins to catch duplication early.
- Choose LEFT JOIN deliberately when missing matches should still appear.
Pick the right join type for the question
Northstar operations teams often ask for all customers and their latest order. If you use INNER JOIN, customers with no order disappear. LEFT JOIN keeps them visible, which is usually the right business outcome.
INNER JOIN is still useful for metrics that require valid matches on both sides, such as item revenue by existing product record.
Prevent accidental fan-out
Joining orders to order_items multiplies rows, one per item line. That is expected, but totals can inflate if you later sum order-level amounts without regrouping.
When validating a join, compare distinct keys and sample duplicates. A quick COUNT(*) and COUNT(DISTINCT order_id) check can reveal fan-out before results reach stakeholders.
- Qualify columns with table aliases.
- Join on fields with matching data types.
- Run a small key-level sanity query before adding aggregates.
Show every customer and any paid order
Tables are customers(customer_id, customer_name, segment) and orders(order_id, customer_id, status, total_amount).
- Start from customers as the left table.
- LEFT JOIN orders ON customers.customer_id = orders.customer_id.
- Filter order status in the JOIN condition to keep unmatched customers visible.
Common mistakes
- Placing a right-table filter in WHERE after a LEFT JOIN and turning it into an INNER JOIN.
- Joining text names that are not unique.
- Ignoring duplicated rows created by one-to-many joins.
- Selecting similarly named columns without aliases and misreading output.
Try one
You need all products even when no sales exist yet. Should you use INNER JOIN or LEFT JOIN from products to order_items?
Use LEFT JOIN from products so products without sales still appear in the result.
Sources
- PostgreSQL 18 Docs: Table Expressions and JOINsOfficial JOIN semantics, ON conditions, and join order behavior.