Reference Tables Used in This Section
Every join note below uses these same two tables, so you can see exactly how each join type treats the same data differently.
employees
| id | name | department_id |
|---|---|---|
| 1 | Aman | 1 |
| 2 | Riya | 2 |
| 3 | Karan | 1 |
| 4 | Neha | NULL |
departments
| id | department_name |
|---|---|
| 1 | Engineering |
| 2 | Marketing |
| 3 | Sales |
Notice: Neha has no department (department_id is NULL), and Sales has no employees at all. These two "unmatched" rows are what make the differences between join types visible.
What a JOIN Does
A JOIN combines rows from two or more tables based on a related column between them — usually a foreign key pointing to a primary key. Without joins, related data spread across normalized tables (see Normalization) would be unusable on its own.
Basic Syntax
SELECT columns
FROM table1
JOIN_TYPE table2 ON table1.column = table2.column;The Six Join Types at a Glance
| Join Type | Returns |
|---|---|
| INNER JOIN | Only rows with a match in both tables |
| LEFT JOIN | All rows from the left table, matched or not |
| RIGHT JOIN | All rows from the right table, matched or not |
| FULL OUTER JOIN | All rows from both tables, matched or not |
| CROSS JOIN | Every row from table1 paired with every row from table2 |
| SELF JOIN | A table joined to itself, for hierarchical/relational data |
Quick Example — INNER JOIN
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;Output:
| name | department_name |
|---|---|
| Aman | Engineering |
| Riya | Marketing |
| Karan | Engineering |
Neha (no department) and Sales (no employees) both disappear — that's the defining behavior of INNER JOIN, explored in depth on its own page.
Why Joins Matter
Real databases are normalized — customer data lives in customers, order data in orders, product data in products — precisely to avoid repeating data. Joins are how you put that data back together for reporting, dashboards, and application queries. It's arguably the single most important SQL skill for any data or backend role.
Common Mistakes
- Forgetting the
ONcondition — this produces a Cartesian product (every row paired with every row), usually not what you meant. See CROSS JOIN for when that IS what you want. - Joining on the wrong columns (e.g. matching by name instead of ID) — fragile and can silently produce wrong matches
- Not knowing which join type actually answers the business question — this is the #1 real-world join mistake, more common than syntax errors
Interview Relevance
Joins are the single most-tested SQL topic in interviews across analyst, data science, and backend roles. Expect at least one join question in almost every SQL round — often several.
Practice Question
Using the reference tables above, predict — without running it — which rows an INNER JOIN, a LEFT JOIN, and a FULL OUTER JOIN would each return. Then check your answer on the individual join pages.
