SQL Joins: A Complete Guide to Types, Differences, and When to Use Each
SQL joins are one of the most powerful — and most misunderstood — features of relational databases. This guide covers every join type in depth, with real examples, comparison tables, and clear guidance on when to reach for each one.
Joins are the mechanism by which relational databases earn their name. The entire point of separating data into distinct, normalised tables is that you can combine them on demand — pulling exactly the slice of related data you need, in the shape you need it. Understanding joins deeply is not optional for anyone working seriously with SQL. It is the skill that separates queries that work from queries that work correctly.
This guide covers every standard join type — INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF — with worked examples, result tables, and clear guidance on when each is appropriate. By the end you should be able to look at a data problem and immediately know which join, or combination of joins, will give you the right answer.
The sample schema
All examples in this guide use the same two tables, which keeps comparisons clean and directly comparable.
customers
| customer_id | name | city |
|---|---|---|
| 1 | Alice Nguyen | Sydney |
| 2 | Bob Martins | Melbourne |
| 3 | Carol White | Brisbane |
| 4 | David Kim | Sydney |
orders
| order_id | customer_id | product | amount |
|---|---|---|---|
| 101 | 1 | Laptop | 1200.00 |
| 102 | 1 | Mouse | 45.00 |
| 103 | 2 | Keyboard | 89.00 |
| 104 | 5 | Monitor | 399.00 |
A few things to note deliberately: customer 3 (Carol) and customer 4 (David) have no orders. Order 104 references customer_id 5, which does not exist in the customers table. These edge cases are what make the differences between join types meaningful.
INNER JOIN
An INNER JOIN returns only the rows where there is a matching value in both tables. If a row in the left table has no match in the right table, it is excluded. If a row in the right table has no match in the left table, it is also excluded.
SELECT
c.customer_id,
c.name,
o.order_id,
o.product,
o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
Result:
| customer_id | name | order_id | product | amount |
|---|---|---|---|---|
| 1 | Alice Nguyen | 101 | Laptop | 1200.00 |
| 1 | Alice Nguyen | 102 | Mouse | 45.00 |
| 2 | Bob Martins | 103 | Keyboard | 89.00 |
Notice what is missing: Carol and David (customers 3 and 4) do not appear because they have no matching orders. Order 104 does not appear because customer_id 5 does not exist in the customers table. The INNER JOIN is the intersection — only rows with a match on both sides survive.
When to use it: INNER JOIN is the right choice when you only care about records that have a relationship in both tables. Reporting on customers who have placed orders, products that have been sold, employees who have been assigned to projects — any scenario where unmatched rows are irrelevant to the question.
The trap: If you use an INNER JOIN when you actually need all records from one side, you will silently lose data. The query runs without errors, but the results are incomplete. This is one of the most common sources of incorrect reporting in SQL.
LEFT JOIN (LEFT OUTER JOIN)
A LEFT JOIN returns all rows from the left table, plus any matching rows from the right table. Where there is no match in the right table, the right-side columns are filled with NULL.
SELECT
c.customer_id,
c.name,
o.order_id,
o.product,
o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
Result:
| customer_id | name | order_id | product | amount |
|---|---|---|---|---|
| 1 | Alice Nguyen | 101 | Laptop | 1200.00 |
| 1 | Alice Nguyen | 102 | Mouse | 45.00 |
| 2 | Bob Martins | 103 | Keyboard | 89.00 |
| 3 | Carol White | NULL | NULL | NULL |
| 4 | David Kim | NULL | NULL | NULL |
Now Carol and David appear, with NULLs for the order columns. Order 104 still does not appear — it has no matching customer, and the left table here is customers, not orders.
When to use it: LEFT JOIN is the most commonly used join after INNER JOIN. Use it any time you want all records from the primary (left) table, regardless of whether they have a match in the secondary (right) table. Customer lists with their most recent order (or NULL if they've never ordered), products with their current inventory level (or NULL if not yet stocked), users with their profile data (or NULL if the profile hasn't been created yet).
Finding unmatched rows: LEFT JOIN combined with a WHERE clause filtering for NULL is the standard pattern for finding records with no match:
-- Find customers who have never placed an order
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
Result:
| customer_id | name |
|---|---|
| 3 | Carol White |
| 4 | David Kim |
This is a cleaner and often more performant approach than using NOT IN or NOT EXISTS subqueries for the same task.
RIGHT JOIN (RIGHT OUTER JOIN)
A RIGHT JOIN is the mirror image of a LEFT JOIN. It returns all rows from the right table, plus any matching rows from the left table. Where there is no match in the left table, the left-side columns are NULL.
SELECT
c.customer_id,
c.name,
o.order_id,
o.product,
o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
Result:
| customer_id | name | order_id | product | amount |
|---|---|---|---|---|
| 1 | Alice Nguyen | 101 | Laptop | 1200.00 |
| 1 | Alice Nguyen | 102 | Mouse | 45.00 |
| 2 | Bob Martins | 103 | Keyboard | 89.00 |
| NULL | NULL | 104 | Monitor | 399.00 |
Now order 104 appears, with NULLs for the customer columns because customer_id 5 doesn't exist. Carol and David are gone — they have no orders, and the right table (orders) drives the result.
When to use it: RIGHT JOIN is less common in practice, for a simple reason: any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Most developers find LEFT JOIN more readable because it matches the natural reading direction — "give me everything from this table, plus whatever matches from that one."
-- These two queries return identical results:
-- RIGHT JOIN
SELECT c.name, o.product
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
-- Equivalent LEFT JOIN (preferred style)
SELECT c.name, o.product
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;
Use RIGHT JOIN when the query is more readable that way, or when working in a codebase that uses it consistently. Never use it just because you learned it exists.
FULL OUTER JOIN
A FULL OUTER JOIN returns all rows from both tables. Where there is a match, the columns from both sides are populated. Where there is no match on either side, the columns from the non-matching side are NULL.
SELECT
c.customer_id,
c.name,
o.order_id,
o.product,
o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
Result:
| customer_id | name | order_id | product | amount |
|---|---|---|---|---|
| 1 | Alice Nguyen | 101 | Laptop | 1200.00 |
| 1 | Alice Nguyen | 102 | Mouse | 45.00 |
| 2 | Bob Martins | 103 | Keyboard | 89.00 |
| 3 | Carol White | NULL | NULL | NULL |
| 4 | David Kim | NULL | NULL | NULL |
| NULL | NULL | 104 | Monitor | 399.00 |
Every row from both tables appears. Matched rows are joined together. Unmatched rows from either side appear with NULLs on the other side.
When to use it: FULL OUTER JOIN is the right choice when you need to see everything — matched and unmatched — from both sides simultaneously. Data reconciliation between two systems, finding discrepancies between a source and a destination, auditing what exists in one table but not the other. It is less common than INNER or LEFT JOIN but irreplaceable when you need it.
MySQL note: MySQL does not natively support FULL OUTER JOIN syntax. The standard workaround is to UNION a LEFT JOIN and a RIGHT JOIN:
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_id, c.name, o.order_id, o.product
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
PostgreSQL, SQL Server, and most other databases support FULL OUTER JOIN directly.
CROSS JOIN
A CROSS JOIN returns the Cartesian product of the two tables — every row from the left table combined with every row from the right table. There is no ON condition, because every combination is returned regardless of any relationship.
SELECT
c.name,
o.product
FROM customers c
CROSS JOIN orders o;
With 4 customers and 4 orders, this returns 4 × 4 = 16 rows:
| name | product |
|---|---|
| Alice Nguyen | Laptop |
| Alice Nguyen | Mouse |
| Alice Nguyen | Keyboard |
| Alice Nguyen | Monitor |
| Bob Martins | Laptop |
| Bob Martins | Mouse |
| ... | ... |
The full result is 16 rows. At 100 rows × 100 rows, that's 10,000. At 1,000 × 1,000, it's 1,000,000. CROSS JOINs grow exponentially, so they must be used with care.
When to use it: CROSS JOIN has genuine use cases. Generating all combinations of sizes and colours for a product catalogue. Creating a date dimension table by crossing a list of years with a list of days. Building test data that requires every permutation. Pairing every sales representative with every region for a coverage matrix.
The accidental CROSS JOIN: One of the most dangerous SQL mistakes is an accidental CROSS JOIN caused by a missing or incorrect JOIN condition. If you forget the ON clause or accidentally join on a condition that always evaluates to true, you can return millions of rows from two relatively small tables. Always verify your row counts when working with multiple joins.
SELF JOIN
A SELF JOIN is not a distinct join type in SQL syntax — it is any join where a table is joined to itself. You use a regular INNER, LEFT, or other join, but both sides of the join reference the same table with different aliases.
The classic use case is hierarchical or recursive data: an employees table where each row has a manager_id that references another employee_id in the same table.
Consider this employees table:
employees
| employee_id | name | manager_id |
|---|---|---|
| 1 | Sarah Chen | NULL |
| 2 | James Okafor | 1 |
| 3 | Maria Lopez | 1 |
| 4 | Tom Brennan | 2 |
| 5 | Yuki Tanaka | 2 |
Sarah is the CEO with no manager. James and Maria report to Sarah. Tom and Yuki report to James.
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
Result:
| employee | manager |
|---|---|
| Sarah Chen | NULL |
| James Okafor | Sarah Chen |
| Maria Lopez | Sarah Chen |
| Tom Brennan | James Okafor |
| Yuki Tanaka | James Okafor |
We use LEFT JOIN so that Sarah (who has no manager) still appears in the results with a NULL manager, rather than being excluded by an INNER JOIN.
Other self join use cases: Finding duplicate records in a table, identifying rows that share a value with another row, comparing a row to adjacent rows in a time series, finding all pairs of items that share a category.
Comparing all join types
| Join type | Left unmatched rows | Right unmatched rows | Use when |
|---|---|---|---|
| INNER JOIN | Excluded | Excluded | You only want rows with a match on both sides |
| LEFT JOIN | Included (NULLs) | Excluded | You want all left rows, with or without a match |
| RIGHT JOIN | Excluded | Included (NULLs) | You want all right rows, with or without a match |
| FULL OUTER JOIN | Included (NULLs) | Included (NULLs) | You want all rows from both tables |
| CROSS JOIN | N/A (no condition) | N/A (no condition) | You need every combination of rows from both tables |
| SELF JOIN | Depends on join type used | Depends on join type used | You need to relate rows within the same table |
Joining more than two tables
Real queries frequently join three, four, or more tables. Each additional join extends the result set from the previous join. The order matters — each JOIN operates on the result of everything to its left.
SELECT
c.name,
o.order_id,
o.product,
p.category,
p.supplier
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id;
A few principles that keep multi-table joins manageable:
Start with the most selective join. If one join dramatically reduces the row count, putting it first means subsequent joins operate on fewer rows. This can have significant performance implications on large tables.
Be deliberate about join types. Each join in a chain is an independent decision. You might INNER JOIN customers to orders (you only want customers who have ordered) but LEFT JOIN orders to shipments (you want all orders, including those not yet shipped).
Use aliases consistently. With three or more tables, column names can collide. Always alias your tables and prefix every column reference. c.name is unambiguous; name in a four-table join is a maintenance hazard.
Verify row counts as you go. When building a complex join query, add each table incrementally and check the row count at each step. An unexpected explosion in rows usually means a join condition is wrong or missing.
JOIN performance considerations
Joins are where SQL performance either holds or breaks down. A few principles are worth understanding regardless of which database engine you use.
Indexes on join columns are essential. When you join on customer_id, the database needs to look up matching rows. Without an index, it scans the entire table for each lookup. With an index, it goes straight to the matching rows. For any column you join on regularly, ensure it is indexed — this is the single highest-leverage performance optimisation for join-heavy queries.
Filtering early reduces join cost. Moving WHERE conditions as early as possible — or using subqueries and CTEs to pre-filter — means the join operates on fewer rows. A join between 10,000 rows and 500 rows is far cheaper than a join between 10,000 rows and 50,000 rows, even if the final filtered result is the same size.
EXPLAIN is your friend. Every major database engine supports EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to show the query execution plan. If a join query is slow, run EXPLAIN first. It will tell you whether indexes are being used, where sequential scans are happening, and what the estimated row counts are at each step. Learning to read EXPLAIN output is a significant skill multiplier for SQL performance work.
Watch for fan-out. If the right side of a join has multiple rows matching each left-side row, the result set will have more rows than either input table. This is correct behaviour, but it can produce inflated aggregates if you're not careful. COUNT(*) on a joined result set counts the joined rows, not the original left-side rows. Use COUNT(DISTINCT customer_id) or aggregate before joining when this matters.
Common mistakes and how to avoid them
Using INNER JOIN when LEFT JOIN is needed. The query produces results, they look plausible, but rows are silently missing. Always ask: should rows with no match still appear in my results? If yes, use LEFT JOIN.
Filtering on a NULL column after a LEFT JOIN. If you LEFT JOIN and then add WHERE o.status = 'shipped', you've effectively converted the LEFT JOIN into an INNER JOIN — rows where o.status is NULL (i.e., unmatched left rows) will be excluded. Filter on right-side columns in the ON clause, not the WHERE clause, if you want to preserve unmatched left rows.
-- WRONG: converts LEFT JOIN to INNER JOIN behaviour
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'shipped'; -- excludes NULL rows
-- CORRECT: preserves unmatched customers
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
AND o.status = 'shipped';
Joining on a non-unique column without understanding the consequence. If you join on a column that has duplicate values on the right side, every left row will match multiple right rows and the result set will multiply. This is sometimes what you want; often it is not. Know your data cardinality before assuming a join is 1:1.
Forgetting to handle NULLs in aggregates. SUM(o.amount) on a LEFT JOIN result will correctly ignore NULLs, but AVG(o.amount) will also ignore them — meaning the average is over customers who have orders, not all customers. COALESCE(o.amount, 0) is often the right approach when NULLs should be treated as zero.
Quick reference: choosing the right join
When you sit down to write a join query, ask yourself these questions in order:
- Do I need rows from the left table even if they have no match on the right? If yes → LEFT JOIN.
- Do I need rows from the right table even if they have no match on the left? If yes → RIGHT JOIN (or swap tables and use LEFT JOIN).
- Do I need unmatched rows from both sides? If yes → FULL OUTER JOIN.
- Do I only care about rows that match on both sides? If yes → INNER JOIN.
- Do I need every combination of rows regardless of relationship? If yes → CROSS JOIN (with caution).
- Am I relating rows within the same table? If yes → SELF JOIN using any of the above types.
Joins reward deliberate thinking. The type you choose is a statement about what you want to happen to unmatched rows. Get that decision right and the rest of the query follows naturally.