Joins

What is it? #

A join combines rows from two tables by matching a value in one against a value in the other — almost always a foreign key against a primary key.

Joins exist because good design splits data across tables. Customers live in one table, orders in another. To show "Asha's orders", you must bring the two back together, and a join is how.

The only real question is what to do about rows that have no match. Every join type is an answer to that one question:

INNER JOIN drops them. LEFT JOIN keeps everything from the left table. RIGHT JOIN keeps everything from the right. FULL JOIN keeps everything from both.

That is genuinely the whole idea. The rest is knowing which one you want.

Think of it like this #

You have a list of members and a list of gym visits.

Inner join — only members who have visited. Members who never came do not appear at all.

Left join — every member, with their visits next to them. Members who never came still appear, with blanks where the visit details would be. This is how you find who has never visited.

Full join — every member and every visit, including visits by someone whose membership record is missing. That last group usually means a data problem, which is exactly why you would look.

Simple example #

You have customers and orders. Three questions, three different joins:

"Show every order with its customer's name" is an inner join. "Show every customer and how much they have spent, including those who have never ordered" is a left join. "Find orders whose customer record has gone missing" is a left join run from the other direction.

Code #

-- ---------- Setup ----------
-- customers: 1 Asha, 2 Ravi, 3 Meera (Meera has never ordered)
-- orders:    1 -> customer 1,  2 -> customer 1,  3 -> customer 2

---------- The five joins, drawn ----------

     customers          orders
        (A)              (B)

INNER JOIN        keeps only rows that match in BOTH
    ( A ( ### ) B )        -> Asha x2, Ravi x1     (Meera dropped)

LEFT JOIN         keeps ALL of A, fills B with NULL where absent
    ( ### ( ### ) B )      -> Asha x2, Ravi x1, Meera with NULLs

RIGHT JOIN        keeps ALL of B, fills A with NULL where absent
    ( A ( ### ) ### )      -> every order, even orphaned ones

FULL JOIN         keeps ALL of both sides
    ( ### ( ### ) ### )    -> everything, NULLs on whichever side is missing

CROSS JOIN        every row of A paired with every row of B
    3 customers x 3 orders = 9 rows    (no matching condition at all)

-- ---------- INNER JOIN: only matching rows ----------

SELECT c.name, o.id AS order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id     -- "JOIN" alone means INNER JOIN
ORDER BY c.name;

-- Asha  | 1 | 450.00
-- Asha  | 2 | 120.00
-- Ravi  | 3 | 990.00
-- Meera does not appear: she has no orders.

-- ---------- LEFT JOIN: keep everything on the left ----------

SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;

-- Asha  | 1    | 450.00
-- Asha  | 2    | 120.00
-- Meera | NULL | NULL      <-- kept, with NULLs
-- Ravi  | 3    | 990.00

-- ---------- The classic use: find rows with NO match ----------

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;            -- only the rows where the join found nothing

-- Meera
-- This is the standard "customers who have never ordered" query.

-- ---------- The trap: WHERE turns a LEFT JOIN into an INNER JOIN ----------

-- WRONG: Meera disappears again
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;           -- NULL > 100 is not true, so Meera is filtered out

-- RIGHT: put the condition in the JOIN, not in WHERE
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
                  AND o.total > 100;     -- condition applied WHILE joining

-- Rule of thumb: conditions on the LEFT-JOINED table belong in ON.
-- Conditions on the left-hand table belong in WHERE.

-- ---------- LEFT JOIN with aggregation: include the zeroes ----------

SELECT c.name,
       count(o.id)             AS order_count,   -- count(o.id) skips NULLs -> 0 for Meera
       COALESCE(sum(o.total),0) AS spent         -- sum of nothing is NULL, so default it
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spent DESC;

-- Asha  | 2 | 570.00
-- Ravi  | 1 | 990.00
-- Meera | 0 |   0.00     <-- count(*) would wrongly show 1 here

-- ---------- RIGHT and FULL ----------

-- RIGHT JOIN is a LEFT JOIN with the tables swapped. Most teams just use LEFT
-- and reorder the tables, because reading a query is easier with one direction.
SELECT c.name, o.id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL;            -- orphaned orders: a data integrity problem

-- FULL JOIN keeps unmatched rows from both sides. Useful for reconciling
-- two sources that should agree:
SELECT COALESCE(a.ref, b.ref) AS ref, a.amount, b.amount
FROM bank_lines a
FULL JOIN ledger_lines b ON a.ref = b.ref
WHERE a.ref IS NULL OR b.ref IS NULL;   -- lines present in one system only

-- ---------- SELF JOIN: a table joined to itself ----------

CREATE TABLE employees (
    id         bigint PRIMARY KEY,
    name       text NOT NULL,
    manager_id bigint REFERENCES employees(id)    -- points at the same table
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;     -- LEFT: the CEO has no manager

-- Aliases (e and m) are REQUIRED here — without them PostgreSQL cannot tell
-- which "id" you mean.

-- ---------- CROSS JOIN: every combination ----------

-- Deliberate use: generate a row per (product, size) combination
SELECT p.name, s.label
FROM products p
CROSS JOIN sizes s;

-- Accidental use: forgetting the ON condition produces this by mistake.
-- 10,000 rows x 10,000 rows = 100,000,000 rows. This is how queries hang.

How it works #

Conceptually, a join pairs each row of one table with each row of the other and keeps the pairs where the ON condition is true. PostgreSQL does not literally do that — it picks a much smarter strategy — but the mental model predicts the results correctly.

The difference between join types is entirely about unmatched rows. INNER discards them. LEFT keeps every row from the left table and fills the right side with NULL. RIGHT is the mirror image. FULL keeps unmatched rows from both.

The WHERE trap is the most common real-world join bug and worth understanding properly. A LEFT JOIN produces NULLs for unmatched rows, and then WHERE runs afterwards on the result. Since NULL > 100 is not true, every unmatched row is filtered straight back out — and your LEFT JOIN has silently behaved like an INNER JOIN. The fix is to put conditions about the joined table into the ON clause, where they apply during the join rather than after it. Conditions about the left-hand table still belong in WHERE.

The same NULL behaviour is why WHERE o.id IS NULL finds non-matching rows. Only rows the join failed to match have a NULL id, so that test isolates exactly the rows that had no partner.

Counting after a LEFT JOIN has a related catch. count(*) counts rows, and an unmatched customer still produces one row — so Meera would wrongly show a count of 1. count(o.id) counts non-NULL values of that column, giving the correct 0. Similarly sum() over no rows returns NULL, not zero, which is why COALESCE(sum(...), 0) appears so often.

Self joins need table aliases, because both sides are the same table and PostgreSQL otherwise cannot tell which id you mean. The employee-manager hierarchy is the standard example, and a LEFT JOIN is right because the person at the top has no manager.

Cross joins have no ON condition and produce every combination. Occasionally that is what you want. Far more often it happens by accident when a join condition is forgotten, and the row count explodes multiplicatively.

Real-world use #

Duplicate rows after a join are almost never a bug in PostgreSQL — they are a fan-out. Joining orders to order items multiplies each order by its number of items. That is correct behaviour, but it wrecks any sum() you then run on the order total, because the same total is counted once per item. When a total looks inflated, count the rows before and after the join; that diagnoses it immediately.

Always index the columns you join on. Foreign keys are the usual join targets, and PostgreSQL does not index them automatically. A join on unindexed columns forces PostgreSQL to scan entire tables, and it is one of the most frequent causes of a query that was fine in testing and unusable in production.

Most teams avoid RIGHT JOIN entirely. Anything it expresses can be written as a LEFT JOIN with the tables in the other order, and keeping every join in one direction makes long queries far easier to read.

FULL JOIN is niche but valuable for reconciliation — comparing what the payment provider says against what your own ledger says, and listing what appears in only one of them.

When a multi-table query returns something you did not expect, build it back up one join at a time and check the row count at each step. The join that changes the count unexpectedly is the one to look at.

Common mistakes #

• Putting a condition about the right-hand table in WHERE after a LEFT JOIN, silently making it an INNER JOIN.
• Using count(*) after a LEFT JOIN and counting unmatched rows as 1 instead of 0.
• Forgetting that sum() over no rows returns NULL rather than 0.
• Omitting the ON condition and accidentally producing a cross join of two large tables.
• Summing a parent value after joining to a child table, so the fan-out multiplies the total.

Practice #

Using customers and orders, write five queries: every order with its customer name; every customer with their order count including zeros; customers who have never ordered; orders whose customer record is missing; and every customer paired with every product category (a deliberate cross join). Then take the second query, move the aggregate condition into a WHERE clause, and watch the customers with zero orders vanish — that is the trap, seen directly.