Graduating from “just use LEFT JOIN when in doubt”

SQL’s JOIN is simple to write but easy to get wrong — pick the wrong type and rows silently duplicate or disappear. Plenty of people get by on “when in doubt, LEFT JOIN,” but understanding the actual differences saves you from chasing unexpected row counts later.

This is a reference centered on a cheat sheet and row-count walkthrough for each JOIN type, plus how to write multi-table joins and how to review existing SQL by JOIN type.

JOIN type cheat sheet

Using users (3 rows: Alice, Bob, Carol) and orders (Alice has 2, Bob has 1, Carol has 0) as an example, here’s what each JOIN returns.

JOIN typeReturnsAlice & Bob’s rowsCarol (no orders)Order-only rows
INNER JOINOnly rows matching on both sidesAlice×2, Bob×1excludedexcluded
LEFT JOINAll of the left table, plus matches on the rightAlice×2, Bob×11 row (right side NULL)excluded
RIGHT JOINAll of the right table, plus matches on the leftAlice×2, Bob×1excludedincluded if present (left side NULL)
FULL JOINAll rows from both tablesAlice×2, Bob×11 rowincluded if present
CROSS JOINEvery combination (Cartesian product)3 users × 3 orders = 9 rows——

INNER JOIN is the only one that “filters.” Every other type works in the direction of “guarantee all rows from one or both sides” — and that difference is exactly what causes surprising row counts.

Why row counts “increase”

This is the single most common source of confusion. “I joined the tables and got way more rows than expected” is, nine times out of ten, a straightforward one-to-many relationship doing exactly what it’s supposed to.

SELECT users.name, orders.id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Even with 3 users and 3 orders, the result has as many rows as the orders table contributes (if Alice has 2 orders, Alice appears twice). Miss this during aggregation and a naive COUNT(*) counts orders, not users.

-- Wrong: intended to count users, actually counts "user × order" rows
SELECT COUNT(*) FROM users INNER JOIN orders ON users.id = orders.user_id;

-- Right: deduplicated user count
SELECT COUNT(DISTINCT users.id) FROM users INNER JOIN orders ON users.id = orders.user_id;

The practical rule: always know how many rows you had before the join, and if the post-join count isn’t a clean multiple of that, suspect the join condition.

The most common LEFT JOIN trap: WHERE quietly undoes it

You switch to LEFT JOIN specifically to include users with no orders — then the moment you add a WHERE condition, you’re right back to INNER JOIN behavior. This is the second most common incident.

-- Trap: looks like LEFT JOIN, but filtering on orders in WHERE drops the NULL rows
SELECT users.name, orders.status
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.status = 'completed';   -- Carol (NULL) gets filtered out here

Carol has no orders, so orders.status is NULL for her row. WHERE orders.status = 'completed' evaluates NULL = 'completed', and in SQL, any comparison against NULL evaluates to NULL (neither true nor false) — so that row is excluded from the result. The net effect is functionally identical to an INNER JOIN.

The fix is to move the condition into the ON clause:

-- Correct: putting the condition in ON preserves LEFT JOIN semantics
SELECT users.name, orders.status
FROM users
LEFT JOIN orders ON users.id = orders.user_id AND orders.status = 'completed';
-- Carol's row survives, with NULLs on the orders side

The rule of thumb: if you want to guarantee every row from the left table, filter the right side in ON; filter the left side in WHERE.

Joining more than two tables: anchor on one table

When you’re not sure how to structure three-plus-table joins, the trick is deciding which table is the subject first.

SELECT
  users.name,
  orders.id       AS order_id,
  order_items.quantity,
  products.name   AS product_name
FROM users
LEFT JOIN orders      ON users.id = orders.user_id
LEFT JOIN order_items ON orders.id = order_items.order_id
LEFT JOIN products    ON order_items.product_id = products.id
WHERE users.deleted_at IS NULL;

Starting from FROM users, this chains LEFT JOIN outward to orders, then order items, then products — every join keeps users as the anchor. Because of that, you can surface “users with no orders” and “orders with malformed line items” in the same query (mixing in an INNER JOIN partway through would silently start filtering, so watch for that).

SELF JOIN: making one table look like two

A SELF JOIN joins a table to itself, using aliases to treat it as though it were two different tables. The classic example is an “employee and their manager” self-referencing structure.

SELECT
  emp.name  AS employee_name,
  mgr.name  AS manager_name
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

Giving employees the aliases emp and mgr lets SQL treat it as a join between “two tables.” Since you’ll usually want to include employees with no manager (manager_id is NULL), LEFT JOIN is the natural choice here too.

Building and formatting JOINs

If writing a multi-table join from scratch is tedious, the Visual SQL Builder lets you assemble tables and JOIN conditions from a form. Switching between JOIN types (JOIN/LEFT JOIN/RIGHT JOIN/FULL JOIN) and watching the generated SQL change is a good way to make this cheat sheet concrete.

When you need to read an existing, complex JOIN query, the SQL Formatter makes it much easier to see which JOIN applies to which table. Both tools run entirely in your browser — table names and schema details are never sent anywhere.

FAQ

Are INNER JOIN and JOIN different?

No — they’re the same. In most RDBMSes, JOIN is shorthand for INNER JOIN. Many teams still write INNER JOIN explicitly for clarity during code review.

I’ve heard RIGHT JOIN is rarely used — is that true?

A RIGHT JOIN B produces the same result as B LEFT JOIN A with the table order swapped, so many teams adopt a convention of always using LEFT JOIN and never RIGHT JOIN. That said, you’ll still encounter it reading other people’s queries, so it’s worth understanding.

I heard some databases don’t support FULL JOIN?

MySQL has long lacked direct FULL JOIN support (it’s commonly emulated with a UNION of LEFT JOIN and RIGHT JOIN). PostgreSQL and SQL Server support it natively. Check your database’s support before relying on it.

If I paste in a join condition to check, is my data sent anywhere?

No. Both the Visual SQL Builder and the SQL Formatter run entirely in your browser — table names and queries you enter are never transmitted to a server.

Summary

  • INNER JOIN is the only type that “filters.” LEFT/RIGHT/FULL guarantee rows from one or both sides
  • One-to-many joins increase row count — consider COUNT(DISTINCT ...) before aggregating
  • To filter the right side of a LEFT JOIN, use ON; to filter the left side, use WHERE. Putting a right-side condition in WHERE turns a LEFT JOIN into a de facto INNER JOIN
  • For multi-table joins, anchor on one table and chain LEFT JOINs from it
  • SELF JOIN uses aliases to make one table look like two