Soumitra SahaMay 15, 2025

SQL Joins Explained with Practical Examples

I explain each PostgreSQL join type with working examples, show exactly what rows each one returns, and cover the NULL pitfalls most developers hit first.

You write a LEFT JOIN, run the query, and get back rows with NULL everywhere the data should be. You know the data exists — you can see it in the table. So you add WHERE column IS NOT NULL to clean it up, and half your results vanish. Now you have fewer rows than you started with, which was never the goal.

This confusion is extremely common, and it traces back to one thing: not knowing exactly what each join type does before your WHERE clause runs. Once that click happens, joins stop being mysterious and become straightforward tools you can pick between.

I am going to walk through all six join types PostgreSQL supports — INNER, LEFT, RIGHT, FULL OUTER, CROSS, and self-join — show what each one does to your result set, and cover the specific mistakes that cause data to disappear or multiply when you do not expect it.

The tables I’ll use throughout the examples

To keep the examples concrete, I am using two small tables throughout this post: users and orders. This setup mirrors what you will find in almost any backend API that queries a database.

CREATE TABLE users (
  id   INT  PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id      INT     PRIMARY KEY,
  user_id INT     REFERENCES users(id),
  product TEXT    NOT NULL,
  amount  NUMERIC NOT NULL
);

INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');

INSERT INTO orders VALUES
  (101, 1, 'Keyboard', 89.00),
  (102, 1, 'Mouse',    34.00),
  (103, 2, 'Monitor',  299.00);

Three users, three orders. Carol has placed no orders. Alice has placed two. Every join result below comes from these exact rows.

INNER JOIN: only the rows that match on both sides

An INNER JOIN — or just JOIN with no qualifier — returns a row only when the join condition is satisfied on both sides. If a row in the left table has no matching row in the right table, it disappears. Same for the other direction.

SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

name	product	amount
Alice	Keyboard	89.00
Alice	Mouse	34.00
Bob	Monitor	299.00

Carol is gone. She has no matching row in orders, so the join produces nothing for her. Alice appears twice because she has two matching orders — one row per match is how the join works.

The ON clause is the general form. There is also USING when both tables share the same column name for the join key:

-- Equivalent when users.id and orders.user_id have the same name
-- After renaming orders.user_id to orders.id for illustration:
SELECT u.name, o.product
FROM users u
INNER JOIN orders o USING (id);

USING produces only one output column instead of two for the shared key, which keeps the result tidier. There is also NATURAL JOIN, which infers the join columns from all column names that match between the two tables — but it is risky in production because any schema change that introduces a new shared column name will silently change the join condition.

Try this

Insert a user with no orders: INSERT INTO users VALUES (4, 'David');

Run the INNER JOIN query.

Confirm David does not appear in the results.

Expected result: The result set stays at 3 rows. Only users with at least one matching order appear.

LEFT and RIGHT JOIN: keeping every row from one side

A LEFT JOIN starts with an inner join, then adds any rows from the left table that had no match — filling the right-side columns with NULL.

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

name	product	amount
Alice	Keyboard	89.00
Alice	Mouse	34.00
Bob	Monitor	299.00
Carol	NULL	NULL

Carol is back. Her product and amount columns are NULL because she has no matching order. Use LEFT JOIN whenever you want every row from the left table regardless of whether it has a match.

RIGHT JOIN is the exact mirror: every row from the right table appears, and the left-side columns fill with NULL for unmatched rows. In practice most people rewrite a RIGHT JOIN as a LEFT JOIN with the tables swapped, since reading joins left-to-right feels natural. The PostgreSQL documentation treats them as fully equivalent in terms of what they produce.

The ON vs WHERE trap

This is the specific mistake that turns a LEFT JOIN back into an INNER JOIN without you realising it.

-- Version A: filter inside the ON clause
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100;

-- Version B: filter in the WHERE clause
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

Version A keeps all three users. The condition o.amount > 100 is evaluated during the join, so it controls which orders qualify as a match. Alice gets paired with her Monitor… wait, Alice has no Monitor. Let me be precise: Bob’s Monitor (299.00) qualifies. Alice’s Keyboard (89.00) does not, so Alice gets NULL for the order columns. Carol still gets NULL because she has no orders at all.

Version B filters after the join. The outer join produced Carol’s NULL row, but NULL > 100 evaluates to NULL (which is not true), so Carol’s row is removed by WHERE. Same for any row where o.amount is NULL. You have just converted your outer join into an inner join.

The rule the PostgreSQL documentation states directly: an ON clause restriction is processed before the join, while a WHERE clause is processed after the join. Conditions that control which rows participate go in ON; conditions that filter the final output go in WHERE.

Check this before moving on

You can explain why Carol appears in a LEFT JOIN result even with no matching orders
You know why WHERE o.amount > 100 removes Carol’s row even though the issue is not her amount
You can state in one sentence the difference between filtering in ON versus filtering in WHERE

FULL OUTER JOIN: every row from both tables

A FULL OUTER JOIN combines both outer joins: every row from the left table appears, every row from the right table appears, and NULL fills whichever side has no match.

SELECT u.name, o.product, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

If the orders table contained an order with user_id = 99 — a user that does not exist — that row would appear with NULL for u.name. Combined with Carol’s row (no orders), both gaps surface. The result is: every user, every order, and NULL wherever the relationship is missing.

FULL OUTER JOIN is especially useful when reconciling two data sets: finding records that exist in one table but not the other after a migration, comparing an import against a reference table, or auditing which items from a source have been processed and which have not. It is less common in regular feature queries, but when you need it, nothing else does the same job.

CROSS JOIN and self-join: two special cases

A CROSS JOIN produces the Cartesian product: every row from the left table paired with every row from the right table, with no join condition at all.

SELECT u.name, o.product
FROM users u
CROSS JOIN orders o;

Three users, three orders — this gives 9 rows. Every user is paired with every order. On large tables, a CROSS JOIN can return billions of rows and kill your database. It has specific uses: generating test data, building a combination matrix, or joining a small reference table to every row in a larger one. In those narrow cases it is the right tool. Everywhere else, it is almost certainly a mistake (often from accidentally omitting the ON clause).

A self-join joins a table to itself using aliases. The classic use case is a hierarchical structure, like an employee table where each row holds the ID of the employee’s manager:

CREATE TABLE employees (
  id         INT  PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id INT  REFERENCES employees(id)
);

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

The two aliases — e for the employee and m for the manager — let PostgreSQL treat the same table as two separate sides of the join. Using LEFT JOIN here ensures the top-level employee (whose manager_id is NULL) still appears in the result. An INNER JOIN would drop them.

Three pitfalls that corrupt your results quietly

Duplicate rows from one-to-many relationships. Alice appears twice in the INNER JOIN result because she has two orders. If you then join a third table — say user_profiles — each of Alice’s order rows gets paired with her profile row, and she appears twice again. Unexpected row multiplication almost always traces back to a one-to-many relationship somewhere in the join chain. Run a count before and after adding a new join to catch this early.

Comparing NULL with equality. After an outer join, some columns will be NULL. Writing WHERE o.amount = NULL never returns anything — NULL does not equal NULL in SQL. Use WHERE o.amount IS NULL or WHERE o.amount IS NOT NULL. This catches people when they try to find unmatched rows after a LEFT JOIN.

Joining without an index on the join column. PostgreSQL can run any join without indexes, but on large tables it will default to a sequential scan of one or both tables. A join on orders.user_id is substantially faster when that column has an index. If you build a lot of queries that join across user_id columns — as is common in any multi-table backend — run EXPLAIN ANALYZE on the query to see whether PostgreSQL is doing an index scan or a sequential scan. The presence of Seq Scan where you expected Index Scan is the tell.

If you are writing those queries in TypeScript, you will also want to type the result of a join query explicitly. PostgreSQL returns a merged row shape that your ORM or query builder may not infer correctly, especially when both tables have columns with the same name. Naming columns explicitly in SELECT and typing the return shape in your application code prevents silent column shadowing.

The underlying logic behind every join type is the same: the join runs first and builds a combined table, then your WHERE clause filters that table. What you put in ON controls which rows participate in the combination; what you put in WHERE controls which combined rows you keep. Get that sequence straight and the NULL surprises stop.

Your goal	Use this join
Only rows that match on both sides	`INNER JOIN`
All rows from the left table, matched or not	`LEFT JOIN`
All rows from the right table, matched or not	`RIGHT JOIN`
All rows from both tables, matched or not	`FULL OUTER JOIN`
Every combination of two tables	`CROSS JOIN`
Compare rows within the same table	Self-join with aliases