What Are JOINs?
JOINs combine rows from two or more tables based on a related column between them. They are essential for querying normalized databases.
💡 In a normalized database, data is split across multiple tables. JOINs bring it back together for querying.
Sample Tables
customers orders
+----+-----------+ +----+-------------+-----------+
| id | name | | id | customer_id | amount |
+----+-----------+ +----+-------------+-----------+
| 1 | Alice | | 1 | 1 | 150.00 |
| 2 | Bob | | 2 | 1 | 75.00 |
| 3 | Charlie | | 3 | 3 | 200.00 |
+----+-----------+ +----+-------------+-----------+
INNER JOIN
Returns only rows that have matching values in both tables.
SELECT
c.name,
o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
Result:
| name | amount |
|---|---|
| Alice | 150.00 |
| Alice | 75.00 |
| Charlie | 200.00 |
💡 Bob is missing from the result because he has no orders. INNER JOIN only returns matching rows.
LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table, and matched rows from the right table. NULL for non-matching rows.
SELECT
c.name,
o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
Result:
| name | amount |
|---|---|
| Alice | 150.00 |
| Alice | 75.00 |
| Bob | NULL |
| Charlie | 200.00 |
RIGHT JOIN (RIGHT OUTER JOIN)
Returns all rows from the right table, and matched rows from the left table.
SELECT
c.name,
o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;
Result:
| name | amount |
|---|---|
| Alice | 150.00 |
| Alice | 75.00 |
| Charlie | 200.00 |
FULL OUTER JOIN
Returns all rows from both tables. NULL for non-matching rows on either side.
SELECT
c.name,
o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;
Result:
| name | amount |
|---|---|
| Alice | 150.00 |
| Alice | 75.00 |
| Bob | NULL |
| Charlie | 200.00 |
JOIN Types Comparison
| Join Type | Left Table | Right Table | NULLs |
|---|---|---|---|
| INNER | Matching only | Matching only | No |
| LEFT | All rows | Matching only | Right side |
| RIGHT | Matching only | All rows | Left side |
| FULL | All rows | All rows | Both sides |
Multi-Table JOINs
SELECT
c.name AS customer,
o.id AS order_id,
p.name AS product,
oi.quantity
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;
JOIN with WHERE
SELECT
c.name,
o.amount,
o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 100
AND o.order_date >= '2024-01-01'
ORDER BY o.amount DESC;
Common JOIN Mistakes
| Mistake | Problem | Solution |
|---|---|---|
| Missing JOIN condition | Cross join (Cartesian product) | Always specify ON clause |
| Wrong column in ON | Incorrect matches | Verify primary/foreign key relationships |
| Using WHERE instead of ON | Filters after join | Use ON for join conditions |
⚠️ A JOIN without ON or WHERE creates a Cartesian product — every row from Table A paired with every row from Table B. This is almost always unintentional and extremely slow.
✏️ Exercise: Write a query to list all customers with their orders. Include customers even if they have no orders. Show customer name, order ID, and order amount.
See Solution
SELECT
c.name,
o.id AS order_id,
o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
ORDER BY c.name, o.order_date;
✅ Key Takeaways
- INNER JOIN returns only matching rows from both tables
- LEFT JOIN returns all left table rows with NULLs for non-matching right rows
- RIGHT JOIN returns all right table rows with NULLs for non-matching left rows
- FULL OUTER JOIN returns all rows from both tables
- Always specify JOIN conditions to avoid Cartesian products