SQL Joins
A SQL join combines rows from two or more tables based on a related column, typically a foreign key pointing to a primary key. INNER JOIN returns only rows matched in both tables, LEFT and RIGHT JOIN keep every row from one side with NULLs where no match exists, and FULL JOIN keeps every row from both tables.
A join combines rows from two or more tables based on a related column between them, typically a foreign key pointing to a primary key. Joins are what make normalized database design practical: data lives in separate, non-redundant tables, and a join stitches it back together at query time whenever you actually need the combined view.
The main join types
- INNER JOIN: returns only rows that have a match in both tables.
- LEFT JOIN: returns every row from the left table, matched with the right table where a match exists, and NULLs for the right table's columns where it doesn't.
- RIGHT JOIN: the mirror of LEFT JOIN, every row from the right table, matched with the left where possible.
- FULL JOIN: returns every row from both tables, matched where possible, NULLs on whichever side has no match.
A worked example
Two tables: customers and orders, where orders.customer_id references customers.customer_id.
customers:
| customer_id | name |
|---|---|
| 1 | Asha |
| 2 | Rahul |
| 3 | Priya |
orders:
| order_id | customer_id | amount |
|---|---|---|
| 100 | 1 | 40 |
| 101 | 1 | 25 |
| 102 | 2 | 60 |
INNER JOIN (only customers who have placed at least one order):
SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
Returns Asha (40), Asha (25), Rahul (60). Priya doesn't appear at all, she has no matching row in orders.
LEFT JOIN (every customer, with order info where it exists):
SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
Returns Asha (40), Asha (25), Rahul (60), and Priya (NULL), Priya still appears, just with no order amount since she has none.
Where this matters in real systems
LEFT JOIN is the standard tool for "show me everything in table A, with related info from table B if it exists" queries, exactly the shape of "show me every customer, including ones who haven't ordered yet." Reporting and analytics queries lean on this constantly: showing every product even if it has zero sales this month, rather than silently hiding products with no matching sales rows the way an INNER JOIN would.
What interviewers actually ask about SQL joins
- "What's the difference between INNER JOIN and LEFT JOIN?" INNER JOIN only returns rows with a match in both tables; LEFT JOIN returns every row from the left table regardless, with NULLs where there's no match on the right.
- "How would you find customers who have never placed an order?" LEFT JOIN customers to orders, then filter WHERE orders.order_id IS NULL, catching exactly the customers with no matching order row.
- "What does a FULL JOIN return?" Every row from both tables, matched where possible, with NULLs on whichever side lacks a match.
- "Why use joins instead of storing all the data in one big table?" Joins let you keep data normalized (no duplication, one source of truth per fact) while still being able to query across related tables as needed.
Key takeaways
- A join combines rows from two or more tables based on a related column, usually a foreign key.
- INNER JOIN returns only matching rows; LEFT JOIN keeps every row from the left table regardless of a match.
- LEFT JOIN with a NULL check is the standard way to find rows in one table with no corresponding row in another.
- Joins are what make normalized, non-duplicated table design practical for real querying.
More in SQL
Browse all DBMS topics
Log in to save which topics you've read across every subject.
Frequently asked questions
What's the difference between INNER JOIN and LEFT JOIN?
INNER JOIN only returns rows with a match in both tables; LEFT JOIN returns every row from the left table regardless, with NULLs where there's no match on the right.
How would you find customers who have never placed an order?
LEFT JOIN customers to orders, then filter WHERE orders.order_id IS NULL, catching exactly the customers with no matching order row.
What does a FULL JOIN return?
Every row from both tables, matched where possible, with NULLs on whichever side lacks a match.
Why use joins instead of storing all the data in one big table?
Joins let you keep data normalized (no duplication, one source of truth per fact) while still being able to query across related tables as needed.