The four main joins all combine two tables on a shared key. The only real difference is what each one does with rows that have no match on the other side. Learn that one idea and every join type stops being confusing.
This is a focused, side by side comparison. If you want the long form treatment with more scenarios and edge cases, read the full SQL joins guide, and if you learn better from pictures see SQL joins explained visually. Here we keep one tiny dataset and run the same query four ways so you can see exactly which rows survive.
What this comparison covers
1. The shared two table example
Every query below runs against the same two tables: customers and orders. They are linked by orders.customer_id = customers.id. The data is deliberately messy so the differences show up clearly.
-- Two small tables to compare joins
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
total DECIMAL(8,2)
);Here is the sample data. Notice that Dana is a customer who has never placed an order, and order #5000 has a customer_id of 99 that does not exist in the customers table. Those two odd rows are what separate one join type from another.
| customers.id | name |
|---|---|
| 1 | Alice |
| 2 | Ben |
| 3 | Cara |
| 4 | Dana |
| orders.id | customer_id | total |
|---|---|---|
| 1001 | 1 | 50.00 |
| 1002 | 1 | 20.00 |
| 1003 | 2 | 90.00 |
| 1004 | 3 | 15.00 |
| 5000 | 99 | 75.00 |
Left and right: in FROM customers c JOIN orders o, the table named first (customers) is the left table and the second (orders) is the right table. LEFT and RIGHT always refer to that written order, not to anything on screen.
2. INNER JOIN: only the matches
An INNER JOIN keeps a row only when the key exists on both sides. Unmatched rows on either side are dropped.
SELECT c.name, o.id AS order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;| name | order_id | total |
|---|---|---|
| Alice | 1001 | 50.00 |
| Alice | 1002 | 20.00 |
| Ben | 1003 | 90.00 |
| Cara | 1004 | 15.00 |
What it keeps: only customers who have at least one order, and only orders that belong to a real customer. Dana and order #5000 both vanish.
3. LEFT JOIN: keep all of the left table
A LEFT JOIN keeps every row from the left table (customers), matched to orders where possible. When there is no matching order, the order columns come back as NULL.
SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;| name | order_id | total |
|---|---|---|
| Alice | 1001 | 50.00 |
| Alice | 1002 | 20.00 |
| Ben | 1003 | 90.00 |
| Cara | 1004 | 15.00 |
| Dana | NULL | NULL |
What it keeps: all four customers, including Dana with NULLs because she has no orders. Order #5000 is still dropped because it has no matching customer. This is the join you reach for most often: "all customers, plus their orders if any."
4. RIGHT JOIN: keep all of the right table
A RIGHT JOIN is the mirror image: it keeps every row from the right table (orders), and pads the left side with NULL where a customer is missing.
SELECT c.name, o.id AS order_id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;| name | order_id | total |
|---|---|---|
| Alice | 1001 | 50.00 |
| Alice | 1002 | 20.00 |
| Ben | 1003 | 90.00 |
| Cara | 1004 | 15.00 |
| NULL | 5000 | 75.00 |
What it keeps: all five orders, including the orphan #5000 with a NULL customer name. Dana disappears because she has no order. In practice most people avoid RIGHT JOIN and simply swap the table order to use a LEFT JOIN instead, because reading "keep all of the first table" is easier on the eye.
5. FULL JOIN: keep everything from both sides
A FULL OUTER JOIN keeps every row from both tables. Matched rows line up, and anything unmatched on either side is NULL padded.
SELECT c.name, o.id AS order_id, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;| name | order_id | total |
|---|---|---|
| Alice | 1001 | 50.00 |
| Alice | 1002 | 20.00 |
| Ben | 1003 | 90.00 |
| Cara | 1004 | 15.00 |
| Dana | NULL | NULL |
| NULL | 5000 | 75.00 |
What it keeps: all four customers and all five orders. Both odd rows survive: Dana (a customer with no order) and #5000 (an order with no customer). This is the reconciliation join: "show me everything on either side and flag whatever does not line up."
MySQL has no FULL JOIN. PostgreSQL, SQL Server, and Oracle support it directly. In MySQL you emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION. The official syntax is documented in the MySQL JOIN reference.
-- FULL JOIN emulation for MySQL
SELECT c.name, o.id, o.total
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.id, o.total
FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;6. Side by side comparison
Here is the whole picture in one table. Read the "keeps" column first, then "NULLs where," and the choice usually becomes obvious.
| Join | Keeps | NULLs where | Typical use |
|---|---|---|---|
| INNER JOIN | Rows matched in both tables | Never (no unmatched rows survive) | Only records that exist on both sides, such as orders with a valid customer |
| LEFT JOIN | All left rows, plus matches | Right columns, for unmatched left rows | The main table plus optional related data, such as all customers and their orders if any |
| RIGHT JOIN | All right rows, plus matches | Left columns, for unmatched right rows | Same as LEFT but from the other table; usually rewritten as a LEFT JOIN |
| FULL JOIN | All rows from both tables | Either side, wherever a match is missing | Reconciliation and auditing across two sources |
The row counts from our data drive the point home: INNER returns 4 rows, LEFT returns 5 (adds Dana), RIGHT returns 5 (adds #5000), and FULL returns 6 (adds both).
7. Which one do I pick?
You almost never need to guess. Answer one question: do I want to keep rows that have no partner, and if so, from which table?
Only rows that exist on both sides?
Use INNER JOIN. This is the default and the most common choice. If you are counting or summing matched activity, this is almost always what you want.
Every row of your primary table, matches optional?
Use LEFT JOIN, with the primary table written first. This is how you keep customers who have never ordered, products that have never sold, and so on.
Every row of the other table instead?
Technically RIGHT JOIN, but it is cleaner to swap the two tables and keep using LEFT JOIN so the "keep all" table always reads first.
Everything from both, matched or not?
Use FULL JOIN (or the UNION trick in MySQL). Reach for this when you are reconciling two lists and need to see gaps on either side.
For the deeper trade offs, including how joins compare with subqueries for the same result, see subqueries vs joins and the core SQL joins lesson.
Chaining more than two tables
The same rules scale to three or more tables. When you chain joins, mixing INNER and LEFT matters, because a later INNER JOIN can quietly discard the NULL padded rows an earlier LEFT JOIN produced. If you want customers, their orders, and each order's shipment, and you want to keep customers with no orders at all, every join in the chain from the customers table outward should be a LEFT JOIN.
SELECT c.name, o.id AS order_id, s.carrier
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN shipments s ON s.order_id = o.id;Swap that second LEFT JOIN for an INNER JOIN and any order without a shipment, along with the customer if it was their only order, drops out of the result. The rule of thumb: once you go LEFT, stay LEFT down the chain unless you deliberately want to require a match.
8. The classic LEFT JOIN plus WHERE trap
This is the single most common join bug, and it bites almost everyone once. You write a LEFT JOIN to keep all customers, then add a filter on the right table in the WHERE clause. Suddenly the unmatched rows disappear and your LEFT JOIN behaves exactly like an INNER JOIN.
Say you want every customer, along with any order over 40, keeping customers who have no such order:
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 40; -- Dana is silently droppedThe problem: for Dana, o.total is NULL, and NULL > 40 is not true, so the WHERE throws her row away. The filter runs after the join and undoes it. Move the condition into the ON clause so it applies while matching, not after:
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id AND o.total > 40
ORDER BY c.name;Watch out: a condition on the right table in WHERE quietly converts a LEFT JOIN into an INNER JOIN. Filter the right table inside ON. The one exception is WHERE right.col IS NULL, which is the deliberate "no match" pattern below.
9. Finding rows with no match
A LEFT JOIN plus IS NULL is the standard way to find rows in one table that have no partner in the other. It is an anti join in disguise: keep everything on the left, then keep only the rows where the right side failed to match.
-- Customers who have never placed an order
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;| id | name |
|---|---|
| 4 | Dana |
What it keeps: only the left rows that had no match, so exactly the customers with zero orders. Pick a right column that can never be NULL for a real match (a primary key like o.id is ideal) so the test is reliable. Flip the tables to answer the mirror question, such as "orders whose customer no longer exists," which returns order #5000. The PostgreSQL join tutorial works through more of these outer join shapes if you want another walkthrough.
10. How often each join is used
Not all joins show up equally in real code. Based on typical application and reporting queries, INNER and LEFT dominate day to day work, while RIGHT and FULL are comparatively rare. The rough split below is a practical guide, not a hard statistic, but it matches what you will see reviewing most codebases.
The takeaway: master INNER and LEFT first, because they cover the vast majority of queries you will ever write. Treat RIGHT as a LEFT with the tables swapped, and keep FULL in your pocket for reconciliation tasks. Keep a copy of the SQL cheat sheet nearby while the syntax settles, and when you are ready to build the muscle memory, the SQL beginner course drills joins with real datasets.
A quick word on speed
The join type you pick changes which rows come back, but performance mostly comes down to one thing: the columns in your ON clause should be indexed. In our example the database matches orders.customer_id against customers.id. The primary key on customers.id is already indexed, but orders.customer_id is a foreign key that often is not. Add an index on it and the engine can look up matches directly instead of scanning the whole orders table for every customer.
-- Index the join key on the many side
CREATE INDEX idx_orders_customer ON orders(customer_id);This holds for every join type equally. INNER, LEFT, RIGHT, and FULL all benefit from an indexed join key, because they all have to find matching rows before deciding what to keep. Getting the logic right comes first, but once the result is correct, an index on the join column is usually the single biggest win.
Once you can predict which rows a join keeps before you run it, the four join types stop feeling like separate commands and start feeling like one idea with four settings. That prediction habit is the whole skill.