SQL JOINS
Combine rows from two or more tables based on a related column.
Why JOINs exist
Data is split across multiple tables to avoid repetition (normalization). A customer's name is in the customers table; their orders are in the orders table. JOINs reunite them for queries.
INNER JOIN — only matching rows
SQL
SELECT c.name, o.product, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
Returns only rows where the join condition matches in both tables. Customers with no orders are excluded. Orders with no matching customer are excluded.
LEFT JOIN — all rows from left, matching from right
SQL — show all customers, even those with no orders
SELECT c.name, o.product, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
All customers appear. For customers with no orders, o.product and o.amount are NULL.
RIGHT JOIN — all rows from right, matching from left
SQL
SELECT c.name, o.product, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;
All orders appear. Orders with no matching customer (orphaned FKs) still appear with NULL customer name.
FULL OUTER JOIN — all rows from both sides
SQL
SELECT c.name, o.product
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;
All rows from both tables. Unmatched rows on either side get NULLs for the other table's columns.
SELF JOIN — a table joined to itself
SQL — find employees and their managers
SELECT e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
JOIN types at a glance
| JOIN Type | Returns |
|---|---|
INNER JOIN | Only rows matching on both sides |
LEFT JOIN | All left rows + matching right (NULLs if no match) |
RIGHT JOIN | All right rows + matching left (NULLs if no match) |
FULL OUTER JOIN | All rows from both sides (NULLs where no match) |
CROSS JOIN | Every combination — M × N rows (no ON clause) |
SELF JOIN | Table joined to itself (requires aliases) |
Find rows with no match (anti-join)
SELECT c.* FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL;
This returns customers who have never placed an order — a very common analytics query.