JOINs
Combine rows from two or more tables using INNER, LEFT, RIGHT, and FULL joins.
What you'll learn
- Why joins exist and how they combine data from multiple tables
- INNER JOIN — only matching rows from both tables
- LEFT JOIN — all rows from the left table, matched or not
- RIGHT JOIN and FULL OUTER JOIN
- Joining on the correct keys and avoiding Cartesian products
Concept
Why Joins Exist
Data in a relational database is split across multiple tables to avoid redundancy. Joins reassemble that data for querying. A join combines rows from two tables based on a related column — usually a foreign key. The general syntax is SELECT ... FROM table_a JOIN table_b ON table_a.col = table_b.col.
INNER JOIN
INNER JOIN returns only rows that have matching values in both tables. If a user has no orders, that user does not appear in the result. This is the most common join type — if you write just JOIN, it defaults to INNER.
LEFT JOIN
LEFT JOIN (or LEFT OUTER JOIN) returns every row from the left table, and the matching rows from the right table. If there is no match, the right table's columns are filled with NULL. This is perfect for "list all users and their orders, if any" — you see users with zero orders too.
RIGHT and FULL OUTER JOIN
RIGHT JOIN is the mirror of LEFT JOIN — all rows from the right table. FULL OUTER JOIN returns all rows from both tables; unmatched columns are NULL on either side. These are less common because you can usually rewrite a RIGHT JOIN as a LEFT JOIN by swapping table order.
Avoiding Cartesian Products
If you omit the ON clause (or write a condition that is always true), you get a Cartesian product — every row in table A joined with every row in table B. For two 1000-row tables that is 1,000,000 rows. Always specify an ON clause with the correct join key.
Example
-- INNER JOIN: only users who have placed orders
SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY u.name;
-- LEFT JOIN: all users, with NULL for those without orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name
ORDER BY order_count DESC;
-- Join three tables: users -> orders -> order_items
SELECT u.name, o.id, oi.product_name, oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id;
INNER JOIN for matching rows, LEFT JOIN to include users with no orders, and a three-table join chaining users → orders → order_items.
Try it
- SQL Formatter
Format multi-table JOIN queries for readability.
- JSON Diff
Compare query results from different join types.
Common mistakes
The mistake
Forgetting the ON clause
The fix
Without ON, you get a Cartesian product (every row × every row). Always specify the join condition, e.g. ON a.id = b.a_id.
The mistake
Using INNER JOIN when you need LEFT JOIN
The fix
If you need all rows from one table even when there is no match in the other, use LEFT JOIN. INNER JOIN silently drops unmatched rows.
Practice JOINs
13 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.