JOINs — Inner, Left, Right, Full, Cross

TL;DR

A JOIN glues rows from two tables together using a predicate — usually “where the foreign key in table A matches the primary key in table B”. Five flavors, all variations on the same theme:

• INNER JOIN — keep only rows that match on both sides. The default.
• LEFT JOIN — keep all rows from the left, fill NULL where the right has no match.
• RIGHT JOIN — same, mirrored. Almost nobody writes these; flip the order and use LEFT JOIN.
• FULL JOIN — keep all rows from both sides, NULL where unmatched.
• CROSS JOIN — every row in A paired with every row in B. The Cartesian product. Useful for date-spines and small lookup expansions; catastrophic by accident.

Pick the wrong join type and you silently drop rows or silently duplicate them. ML training sets that mysteriously have 3× the expected row count are almost always a bad join.

The picture in your head

Two tables, side by side. Draw a line from each row on the left to every matching row on the right. That set of (left, right) pairs is your result. The flavor is the rule for what to do with rows that have no line going to or from them:

• INNER — drop them.
• LEFT — keep the left, pretend a NULL row exists on the right.
• FULL — keep both sides, NULL-fill unmatched.
• CROSS — ignore matching entirely, draw a line from every left row to every right row.

A worked example

CREATE TABLE users (
    id      int PRIMARY KEY,
    name    text,
    country text
);
CREATE TABLE orders (
    id      int PRIMARY KEY,
    user_id int REFERENCES users(id),
    total   numeric,
    placed  date
);

INSERT INTO users VALUES
  (1, 'Alice',  'US'),
  (2, 'Bob',    'US'),
  (3, 'Carol',  'UK'),
  (4, 'Dinesh', 'IN');   -- Dinesh has no orders

INSERT INTO orders VALUES
  (100, 1, 19.99, '2026-04-01'),
  (101, 1, 42.00, '2026-04-15'),
  (102, 2,  7.50, '2026-04-20'),
  (103, 3, 99.00, '2026-04-22'),
  (104, NULL, 5.00, '2026-04-25'); -- orphan order, no user

So we have 4 users, one of whom (Dinesh) never ordered, and 5 orders, one of which is an orphan with no user.

INNER JOIN — the default

“Match users to their orders, drop the unmatched on both sides.”

SELECT u.name, o.id AS order_id, o.total
FROM   users u
INNER  JOIN orders o ON o.user_id = u.id
ORDER  BY o.id;

+-------+----------+-------+
| name  | order_id | total |
+-------+----------+-------+
| Alice |     100  | 19.99 |
| Alice |     101  | 42.00 |
| Bob   |     102  |  7.50 |
| Carol |     103  | 99.00 |
+-------+----------+-------+

Dinesh is missing (no matching order). The orphan order 104 is missing (no matching user). 4 rows out, not 5 or 9. INNER is the default — JOIN without a flavor means INNER JOIN.

LEFT JOIN — keep everyone on the left

“Every user, with their orders if any, NULL otherwise.”

SELECT u.name, o.id AS order_id, o.total
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
ORDER  BY u.id, o.id;

+--------+----------+-------+
| name   | order_id | total |
+--------+----------+-------+
| Alice  |     100  | 19.99 |
| Alice  |     101  | 42.00 |
| Bob    |     102  |  7.50 |
| Carol  |     103  | 99.00 |
| Dinesh |    NULL  |  NULL |
+--------+----------+-------+

Dinesh shows up with NULL columns from orders. The orphan order is still gone — LEFT only preserves the left side.

This is the workhorse for ML feature engineering. “For every user, attach their last order, their order count in the last 30 days, their average basket size” — every one of those features is a LEFT JOIN (or a LEFT JOIN to a subquery) so users without orders still appear in the output with NULL/zero features instead of being silently dropped.

The LEFT JOIN + WHERE trap

-- WRONG: this becomes an INNER JOIN
SELECT u.name, o.id
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
WHERE  o.placed >= '2026-04-15';

You’d think this returns “every user, with their April-15-or-later orders”. It doesn’t. WHERE o.placed >= ... filters NULL out (because NULL >= 'date' is unknown), so users with no orders disappear. Fix: move the condition into the ON:

SELECT u.name, o.id
FROM   users u
LEFT   JOIN orders o
       ON o.user_id = u.id
      AND o.placed >= '2026-04-15';

Now Dinesh stays. Alice’s order 100 (April 1) is filtered out of the join, so she shows up with (101, 42.00) only — but Alice still appears.

This is the single most common LEFT JOIN bug. Burn it into memory: filter the right table inside ON; filter the left table in WHERE.

RIGHT JOIN — almost never

SELECT u.name, o.id
FROM   users u
RIGHT  JOIN orders o ON o.user_id = u.id;

This is exactly equivalent to:

SELECT u.name, o.id
FROM   orders o
LEFT   JOIN users u ON u.id = o.user_id;

Most engineers always write LEFT JOIN because reading “preserve the left side” is more intuitive than “preserve the right side”. The only reason RIGHT JOIN exists is that joining several tables in sequence (a JOIN b JOIN c) sometimes makes the right-hand keep semantically clearer. In practice: rewrite as LEFT JOIN.

FULL JOIN — keep everything

“Every user and every order, matched where possible, NULL elsewhere.”

SELECT u.name, o.id AS order_id, o.total
FROM   users u
FULL   JOIN orders o ON o.user_id = u.id
ORDER  BY u.id NULLS LAST, o.id;

+--------+----------+-------+
| name   | order_id | total |
+--------+----------+-------+
| Alice  |     100  | 19.99 |
| Alice  |     101  | 42.00 |
| Bob    |     102  |  7.50 |
| Carol  |     103  | 99.00 |
| Dinesh |    NULL  |  NULL |
| NULL   |     104  |  5.00 |
+--------+----------+-------+

Both Dinesh (no orders) and the orphan order 104 (no user) survive. FULL JOIN is the right tool for reconciliation queries: “find rows that exist on one side but not the other”.

-- Find mismatches in either direction
SELECT u.id AS user_only, o.id AS order_only
FROM   users u
FULL   JOIN orders o ON o.user_id = u.id
WHERE  u.id IS NULL OR o.user_id IS NULL;

CROSS JOIN — Cartesian product

“Every row on the left × every row on the right, no predicate.”

SELECT u.name, c.label
FROM   users u
CROSS  JOIN (VALUES ('basic'), ('premium')) AS c(label);

+--------+---------+
| name   | label   |
+--------+---------+
| Alice  | basic   |
| Alice  | premium |
| Bob    | basic   |
| Bob    | premium |
| Carol  | basic   |
| Carol  | premium |
| Dinesh | basic   |
| Dinesh | premium |
+--------+---------+

4 users × 2 plans = 8 rows. Useful when you genuinely want the product — “every user × every day in last week”, to fill in missing rows in a time-series before computing rolling counts.

The trap: a CROSS JOIN of two 100k-row tables is 10 billion rows. If you ever forget the ON clause:

-- DISASTER: the ON is missing, this is a CROSS JOIN in disguise
SELECT u.name, o.total
FROM   users u, orders o;     -- old comma syntax + no WHERE = cross product

This is why most modern style guides forbid the FROM a, b comma syntax and require explicit JOIN keywords — the missing ON becomes a syntax error instead of a 10-billion-row query.

Self-joins

Joining a table to itself, usually with two aliases. Useful for hierarchies or row-pair comparisons.

-- Find pairs of users in the same country
SELECT u1.name AS user_a, u2.name AS user_b, u1.country
FROM   users u1
JOIN   users u2
       ON u1.country = u2.country
      AND u1.id < u2.id;       -- avoid (Alice,Alice) and double counting

+--------+--------+---------+
| user_a | user_b | country |
+--------+--------+---------+
| Alice  | Bob    | US      |
+--------+--------+---------+

The u1.id < u2.id is the canonical trick to deduplicate self-join pairs.

Multi-way joins

You can chain joins. Each one builds on the result of the previous:

SELECT u.name, o.id, p.method
FROM   users u
LEFT   JOIN orders o   ON o.user_id = u.id
LEFT   JOIN payments p ON p.order_id = o.id;

The planner is allowed to reorder joins as long as the result is the same. It picks the order it thinks is cheapest based on estimated row counts. When it picks wrong (which happens), EXPLAIN ANALYZE shows you the chosen order; sometimes the fix is SET join_collapse_limit = 1 and writing the joins in the order you want.

Join algorithms — what the planner picks

JOIN is the syntax. The execution method is one of:

You don’t pick — the planner does. But you choose the enabling conditions: indexes, table statistics, and query shape. See EXPLAIN (SQL 302).

USING and NATURAL JOIN

Two shorter forms for the common case where the join columns share a name:

-- USING: explicitly name the shared column
SELECT u.name, o.id
FROM   users u
JOIN   orders o USING (user_id);   -- works only if both tables have user_id

-- NATURAL JOIN: join on every column with a matching name
SELECT * FROM users NATURAL JOIN orders;  -- joins on every same-named column

USING is fine and reduces typing. NATURAL JOIN is dangerous — it silently joins on every column that happens to share a name, including ones added later (created_at, updated_at). Avoid.

Common pitfalls

• Many-to-many duplication. Joining orders to order_items 1:N multiplies your row count. If you then SUM(order.total), you over-count. Pre-aggregate one side or use window functions.
• LEFT JOIN ruined by WHERE on the right table. See above. Always ask: “should this filter live in the ON or the WHERE?”
• Forgetting that NULL never equals NULL. Joining on a nullable column silently drops rows where the join key is NULL on either side. Use COALESCE(x, '__nil__') or IS NOT DISTINCT FROM if you need NULL=NULL.
• Implicit cross join via comma syntax. FROM a, b with a missing WHERE is a Cartesian product. Use explicit JOIN.
• Joining on the wrong key. o.user_id = u.id is right; o.id = u.id accidentally joins by primary keys instead and silently returns nonsense for any pair where the IDs happen to match.
• SELECT * after a join. Both sides’ columns spill out, ID columns appear twice, and your downstream code breaks when columns are added.

Production patterns for ML

1. The “user × time” feature spine. When building features over a window, you usually want one row per (user_id, date) even if the user had zero events that day:

WITH user_dates AS (
    SELECT u.id AS user_id, d::date AS day
    FROM   users u
    CROSS  JOIN generate_series('2026-04-01'::date,
                                '2026-04-30'::date,
                                '1 day'::interval) AS d
)
SELECT ud.user_id, ud.day,
       COALESCE(SUM(o.total), 0) AS daily_spend
FROM   user_dates ud
LEFT   JOIN orders o
       ON o.user_id = ud.user_id
      AND o.placed = ud.day
GROUP  BY ud.user_id, ud.day;

CROSS JOIN builds the spine; LEFT JOIN attaches actuals; COALESCE turns NULL into 0. This is the canonical “no gaps” feature pipeline.

2. The point-in-time feature join. When attaching a feature value to a training event, the feature must reflect what was known at the event time, not the latest value. The join is on feature.computed_at <= event.time, keeping the latest such row. This is what feature stores like Feast and Tecton automate; under the hood, it’s a window function plus a LEFT JOIN.

Practice problems

Resources

• PostgreSQL — joins — postgresql.org/docs
• Mode Analytics — JOINs — mode.com/sql-tutorial
• Use The Index, Luke — joins and indexes — use-the-index-luke.com/sql/join
• PostgreSQL documentation — postgresql.org/docs
• SQLBolt — lessons 6–9 — sqlbolt.com