Explore how different SQL JOIN types combine rows from two tables based on a related column.
Overview
Quick Context
In a relational database, data is split into multiple tables to avoid redundancy (a practice called normalization). SQL JOINs are the mechanism to bring that related data back together for querying. They combine rows from two or more tables based on a related column between them.
Table A (Left)
EMPLOYEES
ID
Name
DeptID
Table B (Right)
DEPARTMENTS
ID
DeptName
SELECT * FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.ID;
Result Set
READY
Emp.ID
Name
Emp.DeptID
Dept.ID
DeptName
A Visual Guide to SQL JOINs
Unlock the power of relational data by mastering how to combine tables. This guide and the interactive tool will make it click.
The Core Idea: Finding Pairs
At its heart, a JOIN operation is like a matching game. The database engine takes a row from the first table (the "left" table) and compares its "join key" (e.g., DeptID) to the join key of every row in the second table (the "right" table). When it finds a match, it combines the two rows into a single, wider row in the result set.
The different types of JOINs (INNER, LEFT, RIGHT, FULL) simply change the rules for what happens when a row in one table doesn't have a matching partner in the other.
The four joins on one small example
Two tables, deliberately tiny, so every row can be accounted for.
Employees: Alice (DeptID 101), Bob (101), Carol (102), David (103). Departments: 101 Engineering, 102 Marketing, 104 Sales.
Note the two orphans: David's department 103 does not exist, and Sales has nobody in it.
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON e.dept_id = d.id;
Join type
Rows returned
Who is missing
INNER JOIN
Alice, Bob, Carol
David and Sales — no partner
LEFT JOIN
Alice, Bob, Carol, David (dept NULL)
Sales
RIGHT JOIN
Alice, Bob, Carol, Sales (name NULL)
David
FULL OUTER JOIN
All five rows, NULLs where unmatched
Nobody
Everything else about joins is elaboration on this table. INNER keeps only pairs. LEFT keeps every row of the first table regardless. RIGHT keeps every row of the second. FULL OUTER keeps everything and fills gaps with NULL.
The word "outer" simply means "keep the unmatched rows too" — LEFT JOIN and LEFT OUTER JOIN are the same thing, and the OUTER is optional noise.
Finding what is missing: the anti-join
The most useful pattern in this topic is not a join type at all. It is a LEFT JOIN followed by a filter for the rows that failed to match:
-- Employees whose department does not exist
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;
The LEFT JOIN keeps David with NULLs in the department columns; WHERE d.id IS NULL keeps only rows where the match failed. This is how you find customers with no orders, products never sold, users who never logged in, and orphaned foreign keys.
The condition must test a column that is NOT NULL in the right-hand table — usually its primary key. Testing a nullable column cannot distinguish "no match" from "matched a row whose value was NULL".
NOT EXISTS expresses the same idea and is often clearer, and on most modern engines the planner produces the same execution plan for both.
ON versus WHERE, and why it only matters for outer joins
This distinction quietly breaks more queries than any other join topic.
-- (A) filter inside the join
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id AND d.name = 'Engineering';
-- (B) filter after the join
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'Engineering';
Query A returns every employee, with department details filled in only for those in Engineering and NULLs for everyone else. Query B returns only the Engineering employees, because WHERE runs after the join and discards every row whose department name is NULL — which includes all the rows the LEFT JOIN was preserving.
Query B has silently become an inner join. If you ever write a LEFT JOIN and then filter on a column from the right-hand table in WHERE, you have almost certainly made this mistake.
For INNER JOIN the distinction does not matter: filtering during or after produces the same rows, and the planner is free to reorder them anyway.
Duplicates, and why row counts explode
A join does not preserve row counts, and the arithmetic surprises people.
If one customer has 3 orders, joining customers to orders returns that customer's name 3 times. That is correct behaviour — the result has one row per matched pair, not one row per customer.
Where it becomes a real bug is with two joins:
SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN payments p ON p.customer_id = c.id -- a second one-to-many
GROUP BY c.name;
A customer with 3 orders and 2 payments produces 3 × 2 = 6 rows, and the SUM(o.total) now counts every order twice. The total is inflated and nothing in the output looks wrong.
Two defences. Aggregate each side separately before joining — in a CTE or a subquery — so each returns one row per customer. Or add COUNT(*) to any aggregate query while developing, and check it against the row counts you expect.
Run the four joins against one database
The sections above describe what each join does. Here they are, on a schema small enough to hold in your head -- change the join type and watch the row count move, which is the fastest way to make the difference stick.
query.sqlSQLite
-- Four tables, five customers, six orders. Two customers have never
-- ordered anything, which is what makes the outer joins interesting.
--
-- Try changing LEFT JOIN to INNER, RIGHT and FULL in turn and re-running.
SELECT c.name AS customer,
o.id AS order_id,
o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;
Result
Experiments to try
The Venn diagram is your best friend here. It shows exactly which data will be included. Use it to predict the outcome before running the visualizer.
The Standard: INNER JOIN
Select INNER JOIN (the default). Notice the Venn diagram only highlights the intersection.
Click Run Visualizer. The animation will only create result rows when an Employees.DeptID finds a matching Departments.ID.
Observation: Employee 'David' (DeptID 103) and the 'Sales' department (ID 104) are excluded from the result because they don't have a match in the other table.
All from the Left: LEFT JOIN
Switch to LEFT JOIN. The Venn diagram now includes all of the left circle (Employees).
Run the visualizer. The animation proceeds as before, but when it gets to 'David' and 'Eve', it can't find a match.
Observation: Instead of discarding them, the LEFT JOIN keeps the employee rows and fills the department columns with NULL. All employees are present in the result.
All from the Right: RIGHT JOIN
Switch to RIGHT JOIN. Now the right circle (Departments) is fully highlighted.
Run the visualizer. This time, the logic focuses on the departments table.
Observation: The 'Sales' department (ID 104), which has no employees, is now included in the result, with NULL values for the employee columns.
Everything: FULL OUTER JOIN
Select FULL OUTER JOIN. The entire Venn diagram is highlighted.
Run the visualizer.
Observation: The result set now includes everyone and every department. Rows that didn't have a match in the other table are preserved, with NULLs filling in the gaps. It's the combination of a LEFT JOIN and a RIGHT JOIN.
Thinking in Pseudocode
Here's a simplified mental model for how a database might process a LEFT JOIN:
result_set = []
for each employee in Employees_Table:
found_a_match = false
for each department in Departments_Table:
if employee.DeptID == department.ID:
combined_row = combine(employee, department)
add combined_row to result_set
found_a_match = true
if not found_a_match:
combined_row = combine(employee, NULL_department) // Keep the left row
add combined_row to result_set
return result_set
Where that leaves you
INNER JOIN: Only matching rows. Use this when you only care about data that exists in both tables (e.g., "Show me employees who are in a valid department").
LEFT JOIN: All rows from the left table, plus matches from the right. Use this when you want to keep all records from the primary table, even if they don't have a corresponding entry in the other (e.g., "Show me ALL employees and their department name, if they have one").
RIGHT JOIN: All rows from the right table. Less common, but useful for finding records in the right table that don't have a match in the left (e.g., "Show me all departments, and which employees are in them").
FULL OUTER JOIN: All rows from both tables. Use when you need a complete picture of all data from both tables, regardless of whether it matches.
How the database actually executes a join
You write JOIN; the planner chooses one of three algorithms, and knowing which explains most performance questions.
Nested loop join. For each row of the outer table, look up matching rows in the inner one. Fast when the outer side is small and the inner side has an index on the join column. Without that index it degrades to scanning the inner table once per outer row, which is the classic slow join.
Hash join. Build a hash table of the smaller side in memory, then scan the larger side and probe it. Excellent for large, unindexed equality joins — and it needs enough memory to hold the build side, or it spills to disk and slows down sharply.
Merge join. Sort both sides by the join key and walk them in step. Efficient when both inputs are already sorted, typically because they arrive from index scans.
The single most valuable habit here is running EXPLAIN (or EXPLAIN ANALYZE) on a slow join. It tells you which algorithm was chosen, whether an index was used, and where the estimated row counts diverge from reality — and a bad estimate is usually the root cause when the planner picks the wrong strategy.
The practical checklist for a slow join: index both sides of the join condition, make sure the two columns have the same data type (a mismatch can silently disable index use), and filter as early as possible so fewer rows reach the join.
Self joins and other useful shapes
A table can be joined to itself, which is how hierarchies are queried:
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
The aliases e and m make one physical table act as two logical ones. The LEFT JOIN keeps the chief executive, who has no manager.
Two other shapes worth recognising:
CROSS JOIN pairs every row with every row — 100 rows against 100 rows gives 10,000. Deliberately useful for generating calendars or all combinations of two dimensions, and accidentally produced by forgetting a join condition.
Joining on a range rather than equality — ON t.event_time BETWEEN p.start AND p.end — is legal and common in time-based work, but it cannot use hash joins, so watch the plan.
Questions people ask
Is LEFT JOIN slower than INNER JOIN? Usually slightly, because the engine must preserve unmatched rows and has fewer options for reordering. The difference is rarely the reason a query is slow — missing indexes are.
Should I use RIGHT JOIN? You can, but almost nobody does. Swapping the table order and using LEFT JOIN reads more naturally, and consistency helps whoever maintains the query next.
How many tables can I join? Dozens, technically. Past about eight the planner's job gets much harder and query plans become unstable. If you regularly join ten tables, consider a view, a materialised summary, or a schema change.
Why did my join produce more rows than either table? Because at least one side matched multiple rows. Check for duplicate keys on the join column — SELECT key, COUNT(*) ... HAVING COUNT(*) > 1 finds them immediately.
Does the order of tables in a join matter? Not for the result of an inner join, and the planner reorders them freely. It does matter for outer joins, where "left" and "right" are defined by the written order.
What is USING? Shorthand for joining on identically named columns: JOIN departments USING (dept_id). It also merges the two columns into one in the output, which is occasionally what you want.
Recap in one screen
A join matches rows from two tables on a condition and returns the combined pairs.
INNER keeps matches only; LEFT/RIGHT keep all rows from one side; FULL OUTER keeps everything.
LEFT JOIN plus WHERE right.key IS NULL is how you find rows with no match.
Filtering a right-hand column in WHERE turns an outer join back into an inner one — put it in ON instead.
Joining two one-to-many relationships at once multiplies rows and inflates aggregates.
Index both join columns, keep their types identical, and read EXPLAIN when it is slow.
Predict, then reveal
About to run: The Standard: INNER JOIN. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Check yourself
0 of 3
Answer without scrolling back up.
A LEFT JOIN returns:
The left table is preserved whole. That is what makes LEFT JOIN the tool for 'show me all customers, including the ones with no orders'.
You LEFT JOIN, then filter the right table in the WHERE clause. What usually happens?
NULL fails almost every comparison. Putting that condition in the ON clause instead keeps the unmatched rows, and this is one of the most common SQL bugs there is.
Joining on a column with duplicate values on both sides produces:
Three matching rows on each side give nine output rows. Unexpected row multiplication after a join is nearly always a duplicate key on one side.
Cheat sheet
SQL Joins Visualizer
In a relational database, data is split into multiple tables to avoid redundancy (a practice called normalization). SQL JOINs are the mechanism to bring that related data back together for querying. They combine rows from two or more tables based on a related column between them.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.