🧩INNER JOIN
Only rows that exist on both sides
An INNER JOIN (or just JOIN) keeps tuples where the ON predicate is true. If the parent row has no child, or the foreign key is NULL, or the dimension key is missing, the row disappears from the result. That is exactly what you want when missing partners indicate bad data: assay results must reference a real sample; GL postings must reference a valid account.
Duplicates on the join key multiply rows: two shipment rows for the same order id produce two output rows per order line. Model uniqueness with constraints or pre-aggregate before joining when you need one row per entity.
Prefer explicit JOIN
Comma-style FROM a, b WHERE a.id = b.id is INNER-equivalent on many engines, but mixing comma lists with LEFT JOIN is easy to misread and accidentally drop outer semantics in large queries.
Plans follow selectivity
Selective keys invite nested-loop joins with index probes; large hash builds need memory. Stale statistics make the optimizer think a join is tiny when it is not, always compare estimated rows to reality on critical paths.
Equi-join on foreign key
SELECT o.order_id, c.company_name
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-04-01';| order_id | company_name |
|---|---|
| 501 | HelixBio Labs |
| 502 | Crimson Therapeutics |
Composite join keys
SELECT i.lot_id, s.available_units
FROM inventory i
JOIN stock_levels s
ON s.warehouse_id = i.warehouse_id
AND s.sku = i.sku;| lot_id | available_units |
|---|---|
| L-900 | 120 |
| L-901 | 0 |
📐Non-equi and range joins
Predicates like BETWEEN or < are still joins, they just rarely use index equality probes.
Example pattern: assign each instrument reading to the calibration window that contains its timestamp. These joins are powerful and expensive; validate row counts carefully because overlapping windows can still multiply rows.
FROM instrument_readings r
JOIN calibration_windows w
ON w.instrument_id = r.instrument_id
AND r.read_at BETWEEN w.valid_from AND w.valid_to
Refactor LEFT → INNER to “simplify”
INNER drops parents without matches. Dashboard totals and cohort sizes shrink; alerts stop firing because “nothing is wrong”: the data is simply gone from the SELECT output.
Re-ask product: should orphans disappear?
If optional children should still surface parents, keep LEFT JOIN and move child-only filters into ON. For “parents with no child,” use LEFT JOIN … WHERE child.id IS NULL (anti-join).
DISTINCT after every join
SELECT DISTINCT hides duplicate keys from a bad join graph but keeps arbitrary surviving rows and burns CPU on large sets.
Fix grain or aggregate upstream
Collapse children with GROUP BY / window functions, or join to a subquery that is already one row per key.
⚡ Advanced / dialect note
WHERE EXISTS (SELECT 1 FROM child WHERE …)) answer “is there any match?” without projecting child columns, often clearer than INNER JOIN plus DISTINCT on the parent when you only need a boolean filter.Types and collation
Joining text to varchar across different collations, or bigint to double precision, can prevent index use through implicit casts. Align types in the schema or cast explicitly once, consistently.
TL;DR
INNER JOIN keeps only rows where the join predicate matches in both tables, default join when you mean “must exist on both sides.” If keys are not unique, row counts multiply (many-to-many explosion). Always specify join predicates explicitly; comma-joins are easy to misread and unsafe under change.