SQL JOIN Types Simulator: two tables, five joins.

A SQL JOIN combines rows from two tables on a matching key, and the join type decides what happens to rows with no match. Edit the rows on the left, flip the JOIN type below, and watch the result update live. NULLs appear exactly where the JOIN type says they should.

users

orders

result

users

id	name

orders

id	user_id	item

SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id

result · 3 rows

id	name	order_id	item
1	Alice	100	Book
1	Alice	101	Mug
2	Bob	102	Pen

Try this

Add a user "Eve" with no orders. Flip INNER → LEFT. Watch Eve appear with NULLs in the order columns.
Notice the orphan order with user_id = 5 (no matching user). Flip to RIGHT or FULL — it appears with NULLs in the user columns.
Try CROSS JOIN. Four users × four orders = 16 rows. Cartesian product is rarely what you want; this is why.

The 30-second mental model

INNER — only rows where the condition matches on both sides.

LEFT — all rows from the left table; NULLs where the right doesn't match.

RIGHT — all rows from the right; NULLs where the left doesn't match.

FULL — all rows from both; NULLs wherever a match is missing.

CROSS — every row from left paired with every row from right. No condition.

Adjacent

What you're looking at

Two editable source tables on the left — users (id, name) and orders (id, user_id, item) — feed the single result table below. The row of buttons picks the join type, and the line above them shows the exact SQL being run. Edit any cell, add a row, or delete one, and the result recomputes instantly. NULL shows up in grey italic wherever a row on one side has no match on the other. The counters up top track how many users, orders, and result rows you currently have.

Start on INNER: the result has four rows. Carol and Dave have no orders, so they drop out, and the orphan order with user_id = 5 ("Ghost") drops too, because no user owns it. Flip to LEFT and Carol and Dave reappear with NULL order columns; flip to RIGHT or FULL and Ghost shows up with NULL user columns instead. The one that should surprise you is CROSS: with no match condition at all, four users times four orders gives sixteen rows. That blow-up is exactly what happens when you forget the ON clause on a real query.

Why JOINs exist

Relational data lives in many tables; queries usually want one.

A well-designed relational database splits data into tables to avoid repetition — instead of storing the user's name on every order row, you store the name once in a users table and reference it from orders via user_id. That keeps updates clean (rename a user once, not 100 times) and storage compact, but it means almost every interesting query needs to recombine data across tables. That's what JOIN does.

The JOIN condition specifies WHICH rows go together. Usually it's a foreign-key match: users.id = orders.user_id. The JOIN type then specifies WHAT TO DO with rows that don't have a match. Drop them (INNER)? Keep the left side and fill nulls on the right (LEFT)? Keep both sides (FULL)? That's the entire choice. Master the five types and you've mastered the SQL JOIN.

What each JOIN actually does

One table on each side, one match condition, five outcomes.

JOIN type	Includes	NULLs where
INNER	Only matched rows.	Never.
LEFT (LEFT OUTER)	Every row from the left table.	Right side, when no match.
RIGHT (RIGHT OUTER)	Every row from the right table.	Left side, when no match.
FULL (FULL OUTER)	Every row from both tables.	Either side, wherever the other doesn't match.
CROSS	Every combination (Cartesian product).	Never (no condition).

The most-used by far is INNER, then LEFT. RIGHT is rarely written explicitly (most devs just swap the table order and use LEFT). FULL is occasionally useful for data-reconciliation work — "show me rows that exist in only one side". CROSS is mostly used to generate test data or with WHERE clauses that effectively turn it back into an INNER JOIN.

The four bugs every beginner writes once

And how to spot them.

Using INNER when you meant LEFT. Symptom: you ran a query like "show me every user with their order count" and the users without orders are missing. Cause: you JOINed users to orders with INNER. Fix: switch to LEFT and your zero-order users appear with NULL in the order columns.
Multiplying row counts unexpectedly. Symptom: you SELECT COUNT(*) FROM users JOIN orders and get 4 × however many orders per user. Cause: each user row is duplicated once per matching order. Fix: when you want one row per user, GROUP BY user.id and COUNT or SUM the order columns.
Forgetting the JOIN condition. Symptom: result has WAY more rows than you expected. Cause: you wrote SELECT * FROM users JOIN orders without an ON clause — most databases treat that as CROSS. Fix: always include ON users.id = orders.user_id.
NULL in the JOIN column. Symptom: rows with a NULL foreign key disappear silently. Cause: NULL = NULL is false in SQL, so the JOIN doesn't match them. Fix: if you need those rows, use LEFT JOIN, or explicitly handle NULL in the JOIN condition.

Found this useful?