Learn · SQL · Core

SQL Joins

Databases split data across tables to avoid repeating it. A JOIN is how you stitch those tables back together to answer a real question — like “which customer placed this order?”

First — tables and keys

A table is a grid: rows (records) and columns (fields). Instead of storing a customer’s name on every order, we keep customers in one table and orders in another, and link them by an id.

• Primary key: a column that uniquely identifies each row (e.g. customer_id). No two rows share it.
• Foreign key: a column in one table that points to another table’s primary key (orders.customer_id → customers.customer_id).

What a JOIN does

Two tables — customers and their orders:

Join them on customer_id and you get a combined row per match: “order 101 → Darshan”, “order 102 → Darshan”. That’s essentially a VLOOKUP, done properly.

SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

The 4 join types

Interview gold: LEFT JOIN is how you find “customers with no orders” — join, then filter where the right side is null.

A join is a row-matching machine

Here’s the whole thing in one sentence — memorise it and every join query stops being magic:

The ON clause is just a true/false test, same as a WHERE, but it decides matches instead of survival. Darshan’s customer row meets order 101: o.customer_id = c.customer_id → 1 = 1 → TRUE → out comes a combined row. Darshan’s row meets an order belonging to customer 2: 2 = 1 → FALSE → nothing. Run that loop in your head and you can predict any join’s output before you hit enter.

INNER JOIN — only matches survive

Plain JOIN means INNER JOIN. A row that finds no partner — on either side — simply doesn’t appear in the result.

SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;

If Amit (customer 2) has placed no orders, he matched nothing — and he is gone. Not null, not flagged, just absent. INNER JOIN doesn’t report the non-matches; it erases them. That’s perfect for “show me orders with their customer names.” It’s wrong the moment your question is about Amit.

LEFT JOIN — every left row survives

A LEFT JOIN makes one promise: every row of the left table shows up at least once. Matched rows behave exactly like INNER. A row with no match still appears — and every column from the right table is filled with NULL.

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

That NULL isn’t missing data or an error — it’s the join telling you “no match existed.” It’s manufactured information, and it’s the key to the most-asked join query in interviews and real work — “which customers have never ordered”:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Read it as the machine: LEFT JOIN guarantees everyone appears; matched customers carry a real order_id; unmatched ones carry NULL there; the WHERE keeps only the NULLs. Result: Amit. This LEFT JOIN … WHERE right.id IS NULL shape is a tool you’ll reuse forever — unused coupons, products never sold, users who never logged in.

RIGHT and FULL — the honest short version

• RIGHT JOIN is a LEFT JOIN written backwards — every right row survives. Nobody really needs it: A RIGHT JOIN B is exactly B LEFT JOIN A. Recognise it in old code, then reorder the tables and use LEFT for consistency.
• FULL JOIN keeps unmatched rows from both sides, NULL-padding whichever side is missing. It’s genuinely rare — mostly reconciliation jobs (“rows in the bank file but not ours, and vice versa, in one query”).

The ON-vs-WHERE bug — LEFT silently becomes INNER

This is the classic outer-join mistake, and it’s dangerous because it returns plausible wrong answers. Intent: “show every customer, with their large (₹1000+) orders if any.”

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount >= 1000;         -- the bug

Run the machine. LEFT JOIN dutifully makes a row for Amit with o.amount = NULL. Then WHERE evaluates NULL >= 1000 → NULL → row dropped. Any customer whose orders are all under ₹1000 vanishes too. Your “every customer” query now quietly hides exactly the customers you wanted. A WHERE condition on right-table columns undoes the LEFT JOIN, because the NULL-padded rows can never pass it.

The fix: a filter that decides which right rows count as matches belongs in ON, not WHERE:

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
                  AND o.amount >= 1000;

The rule to keep: on an outer join, ON decides matching, WHERE decides survival of the combined row. On an INNER join the two are interchangeable — which is exactly why people pick up the sloppy habit and only get burned when they graduate to LEFT. (The one legit exception is WHERE right.id IS NULL — it works because it targets the NULL-padding on purpose.)

Row multiplication — why COUNT after a join can lie

If Darshan placed 3 orders, his one customer row gets copied once per matching order. Join two customers (one with 3 orders, one with 2) and you don’t get 2 rows — you get 5. The result’s grain changed: it’s no longer “one row per customer,” it’s “one row per order.”

SELECT count(*)
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;   -- counts matches, NOT customers

That count is customer-order matches, not customers. Anyone who reads it as a customer count just reported a wrong number with full confidence. Every COUNT, SUM, and AVG after a join inherits the new meaning — the classic disaster is SUMming an amount after a join multiplied the rows, and finance “triples” overnight. And when someone “fixes” duplicate output with SELECT DISTINCT, they’re usually hiding multiplication they don’t understand. The honest fix is always: decide what one row of this result should mean, then make the join produce exactly that.

Junction tables — joining in two hops

When two things relate many-to-many — a customer can be in many groups, a group has many customers — SQL has no “list” column. The relationship gets its own table, where each row is one membership fact: just the two foreign keys.

CREATE TABLE group_member (
  group_id    BIGINT NOT NULL REFERENCES groups(id),
  customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
  PRIMARY KEY (group_id, customer_id)   -- the pair IS the primary key
);

Getting from a customer to their groups takes two hops — customer → membership, membership → group — which is just the same matching machine run twice:

SELECT c.name, g.name AS group_name
FROM customers c
JOIN group_member gm ON gm.customer_id = c.customer_id
JOIN groups g        ON g.id = gm.group_id;

The primary key is the pair of foreign keys because the same membership stated twice isn’t new information — the PK makes duplicates impossible.

Self-joins, briefly

A table can join to itself — you just alias it twice and treat the aliases as two tables. This is the “employees earning more than their manager” interview question, and it shows up any time a hierarchy is stored as a parent_id column.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

File the pattern; don’t drill it yet.

Two gotchas that bite everyone

• The cross-join explosion. The old comma style FROM customers c, orders o with the join condition forgotten pairs every customer with every order — 4 × 6 = 24 rows here, a trillion rows on two million-row tables. Modern JOIN … ON won precisely because forgetting ON is a syntax error, not a silent server-killer.
• Ambiguous columns. If both tables have an id, writing bare SELECT id throws “column reference id is ambiguous.” Postgres refuses to guess. From today, qualify every column in a join (c.name, o.amount) even the unambiguous ones — the error is the kind version; the cruel version is a query that silently picks the column you didn’t mean.

Interview Q&A — the ones they actually ask

Where you’ll use it — real life

Now YOU do the reps

Next: SQL Indexes →