SQL JOIN Explained

Almost no real database fits in one table — a users table and an orders table need to be combined to answer almost any interesting question, and JOIN is how SQL does that: matching rows from one table against rows in another based on a relationship between them, and producing a single combined result set. There are five kinds worth knowing, and they differ in exactly one place — what happens to a row on either side that doesn't find a match.

One honest caveat before the types: the popular Venn-diagram picture of joins (two overlapping circles, shade the part you want) is a helpful first mental model but not a precise one — a join isn't a set operation on two piles of rows, it's a row-by-row matching process, and that distinction matters most once WHERE gets involved (more on that in LEFT JOIN vs INNER JOIN). Think in terms of "for each row on one side, which rows on the other side match" rather than "overlapping circles," and the trickier cases stop being surprising.

Every diagram below uses the same two small tables — three users, three orders — so you can watch exactly which rows survive each join type on identical source data.

INNER JOIN — only the matches

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

A row from users only appears in the result if it has at least one matching row in orders — and if it has three matching orders, it appears three times, once per match. A user with zero orders doesn't appear at all. INNER is the default: writing plain JOIN with nothing in front of it means INNER JOIN in every major database.

LEFT (OUTER) JOIN — every row on the left, matched or not

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

Every row from users appears at least once, regardless of whether it has any orders. For a user with no matching orders, the columns that would have come from orders — o.total here — come back as NULL instead of the row being dropped. This is the join for "give me everything on the left, plus whatever matches on the right if anything does" — every user and their order count, including users with zero orders. OUTER is optional noise here; LEFT JOIN and LEFT OUTER JOIN mean exactly the same thing in every major database.

RIGHT (OUTER) JOIN — the mirror image

Same idea, flipped: every row from the right-hand table survives, NULL-padded where the left side doesn't match. In practice it's the least-used of the four, because A RIGHT JOIN B and B LEFT JOIN A produce the identical result — most style guides standardize on always writing LEFT JOIN and swapping which table comes first, purely so every join in a codebase reads the same direction instead of some queries reading left-to-right and others right-to-left. The diagram below adds one more order — total = 15.0, belonging to a user_id that doesn't exist in users — since that's the case that actually shows what RIGHT JOIN does differently from INNER JOIN: an orphaned order with no real user still survives.

FULL (OUTER) JOIN — everything, matched or not, from both sides

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

Every row from both tables appears at least once — unmatched rows from users get NULL on the orders side, and unmatched rows from orders (an order with a user_id that doesn't exist — a real data-integrity problem worth finding) get NULL on the users side. Useful specifically for finding exactly that kind of mismatch on either side at once. One portability note worth knowing before you reach for it: MySQL has no native FULL JOIN — it has to be emulated with a LEFT JOIN UNION RIGHT JOIN. PostgreSQL and SQL Server both support it natively.

CROSS JOIN — every combination, intentional or not

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

No matching condition at all — every row on the left pairs with every row on the right. 3 users and 3 orders produces 9 result rows (verified: exactly 9, running that query against a real table with 3 of each), regardless of any relationship between them. Genuinely useful occasionally — generating every combination of a small set of dimensions for a report, for instance — but far more often it shows up by accident: the old comma-join syntax, FROM users, orders, is a cross join unless a WHERE clause happens to add the matching condition back in. Forget that condition and the query still runs, just against every combination instead of the matched ones — silently multiplying your result set instead of erroring, which is exactly why SQL Formatting Best Practices recommends explicit JOIN ... ON syntax over comma joins: a join with no ON is a hard syntax error, not a silent accident.

Self-joins: a table joined to itself

Nothing about JOIN requires two different tables — joining a table to itself under two different aliases answers questions about relationships within a single table, most commonly a hierarchy:

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

e and m are both the same underlying employees table, aliased differently so the query can refer to "the employee row" and "that employee's manager's row" as if they were separate tables. The LEFT JOIN here matters specifically — an INNER JOIN would silently drop every employee who has no manager (the CEO, typically) instead of showing them with a NULL manager.

ON vs. USING

When the join columns share the exact same name on both sides, USING is a shorthand for the common case:

-- equivalent, when both tables use the column name user_id
JOIN orders ON orders.user_id = users.id  -- only if users' PK column is also named user_id
JOIN orders USING (user_id)

USING also does one thing ON doesn't: the shared column appears only once in the result instead of twice (once per table), which avoids an ambiguous-column error if you later SELECT *. It only applies when the column names genuinely match, which is why ON — more verbose, but explicit about exactly which columns are being compared — is the more common choice in practice, especially once primary and foreign key columns are named differently (id vs. user_id, as in every example above).

Chaining more than two tables

Each additional JOIN just adds another table to the same result, and each one gets its own ON condition:

SELECT u.name, o.total, p.name AS product
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

Logically, the order the joins are written in doesn't determine the order they actually run in — the query planner decides that based on table sizes and available indexes, covered in How Query Optimization Works. Writing order matters for readability, not correctness: chaining each join onto the table the previous one just introduced, the way the example above does, reads as a straight line instead of a tangle.

Try it yourself

For the single most commonly confused pair — LEFT JOIN vs. INNER JOIN, and the specific way a WHERE clause can silently undo a LEFT JOIN — see LEFT JOIN vs INNER JOIN. SQL Formatter makes a multi-join query easier to read at a glance, and SQL Index Advisor reads a query's JOIN ON conditions and suggests which columns are worth indexing for it. Both run entirely in your browser.