SQL JOIN Types Explained: INNER, LEFT, RIGHT, FULL with Examples
Understanding SQL JOINs is one of the most important skills for any developer or data analyst. Whether you're preparing for a technical interview or writing production queries, knowing when and how to use each JOIN type will save you hours of debugging.
In this guide, we'll break down every major JOIN type with real examples you can run yourself.
What Is a SQL JOIN?
A JOIN combines rows from two or more tables based on a related column. Think of it as a way to answer questions that span multiple tables β like "show me all orders along with customer names."
Key insight: JOINs don't modify your data. They create a temporary result set you can query, filter, and sort.
Let's say we have two tables throughout this guide:
customers
| id | name | city |
|---|---|---|
| 1 | Alice | London |
| 2 | Bob | Paris |
| 3 | Carol | Berlin |
orders
| id | customer_id | product | amount |
|---|---|---|---|
| 1 | 1 | Laptop | 1200 |
| 2 | 1 | Mouse | 25 |
| 3 | 2 | Keyboard | 80 |
| 4 | 5 | Monitor | 400 |
Notice: Carol (id=3) has no orders. Order id=4 has customer_id=5 which does not exist in customers.
INNER JOIN
INNER JOIN returns only rows where there is a match in both tables.
sqlSELECT customers.name, orders.product, orders.amount FROM customers INNER JOIN orders ON customers.id = orders.customer_id;
Result:
| name | product | amount |
|---|---|---|
| Alice | Laptop | 1200 |
| Alice | Mouse | 25 |
| Bob | Keyboard | 80 |
Carol is excluded (no orders). Order id=4 is excluded (no matching customer). Only matched rows appear.
When to use INNER JOIN
- You only care about records that exist in both tables
- Filtering out unmatched rows is the desired behavior
- Most common JOIN type in day-to-day queries
LEFT JOIN (LEFT OUTER JOIN)
LEFT JOIN returns all rows from the left table and matched rows from the right table. If there's no match on the right side, NULL is returned for right-side columns.
sqlSELECT customers.name, orders.product, orders.amount FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;
Result:
| name | product | amount |
|---|---|---|
| Alice | Laptop | 1200 |
| Alice | Mouse | 25 |
| Bob | Keyboard | 80 |
| Carol | NULL | NULL |
Carol now appears with NULL values β she exists in customers but has no orders.
When to use LEFT JOIN
- You want all records from the main (left) table regardless of matches
- Finding customers who have never placed an order
- Keeping a full list while optionally enriching with related data
Finding unmatched rows with LEFT JOIN
A classic pattern: find all customers with no orders at all.
sqlSELECT customers.name FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE orders.id IS NULL;
Result: Carol
RIGHT JOIN (RIGHT OUTER JOIN)
RIGHT JOIN is the mirror of LEFT JOIN β it returns all rows from the right table and matched rows from the left.
sqlSELECT customers.name, orders.product, orders.amount FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;
Result:
| name | product | amount |
|---|---|---|
| Alice | Laptop | 1200 |
| Alice | Mouse | 25 |
| Bob | Keyboard | 80 |
| NULL | Monitor | 400 |
Order id=4 (Monitor) now appears with NULL for name β it has no matching customer.
When to use RIGHT JOIN
In practice, most developers rewrite RIGHT JOINs as LEFT JOINs by swapping the table order β it is easier to reason about. But RIGHT JOIN is useful when you are adding a table to an existing query and do not want to restructure it.
FULL OUTER JOIN
FULL OUTER JOIN returns all rows from both tables. Where there is no match, NULL fills in the gaps.
sqlSELECT customers.name, orders.product, orders.amount FROM customers FULL OUTER JOIN orders ON customers.id = orders.customer_id;
Result:
| name | product | amount |
|---|---|---|
| Alice | Laptop | 1200 |
| Alice | Mouse | 25 |
| Bob | Keyboard | 80 |
| Carol | NULL | NULL |
| NULL | Monitor | 400 |
Both Carol (no orders) and the orphan order (no customer) appear.
When to use FULL OUTER JOIN
- Data reconciliation between two sources
- Finding records that exist in one table but not the other
- Auditing mismatches between datasets
CROSS JOIN
CROSS JOIN returns the Cartesian product β every row from the left table paired with every row from the right table. No ON condition needed.
sqlSELECT customers.name, orders.product FROM customers CROSS JOIN orders;
With 3 customers and 4 orders, this returns 12 rows. Use with caution on large tables.
When to use CROSS JOIN
- Generating combinations (e.g., all color and size variants of a product)
- Calendar or date range generation
- Test data generation
SELF JOIN
A self join joins a table to itself. Useful for hierarchical data like org charts or finding related records in the same table.
sqlSELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
Quick Reference Table
| JOIN Type | Left table | Right table | Notes |
|---|---|---|---|
| INNER JOIN | Matched only | Matched only | Most common |
| LEFT JOIN | All rows | Matched + NULL | Keep all left |
| RIGHT JOIN | Matched + NULL | All rows | Keep all right |
| FULL OUTER JOIN | All rows | All rows | Keep everything |
| CROSS JOIN | All rows | All rows | No condition, Cartesian |
Common Interview Questions About JOINs
Q: What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table, with NULLs where there is no match on the right.
Q: Can you JOIN more than two tables?
Yes. Chain multiple JOINs:
sqlSELECT o.id, c.name, p.title FROM orders o INNER JOIN customers c ON o.customer_id = c.id INNER JOIN products p ON o.product_id = p.id;
Q: What happens if the JOIN condition matches multiple rows?
You get multiple result rows β one for each match. This can cause unexpected row multiplication if your data has duplicates.
Q: What is a JOIN without ON called?
A CROSS JOIN (or implicit cross join if you use comma syntax: FROM a, b).
Practice SQL on Froquiz
The best way to solidify JOIN concepts is to practice with real questions. Test your SQL knowledge on Froquiz β we have beginner to advanced level questions covering JOINs, subqueries, window functions and more.
Summary
- INNER JOIN β only matching rows from both tables
- LEFT JOIN β all left rows, NULLs for unmatched right rows
- RIGHT JOIN β all right rows, NULLs for unmatched left rows
- FULL OUTER JOIN β all rows from both, NULLs where no match
- CROSS JOIN β every combination, no condition
- SELF JOIN β a table joined to itself
Master these and you will handle 95% of real-world SQL queries with confidence.