JOINs are the most important concept in relational databases. They allow you to combine data from multiple tables, which is the entire point of using a relational database in the first place. This guide explains every JOIN type with clear examples and visual diagrams.
The Two-Table Setup
Every example uses these two tables:
employees
| id | name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Carol | 30 |
| 4 | Dave | NULL |
departments
| id | name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 40 | Sales |
INNER JOIN — Only Matching Rows
Returns rows that have matches in BOTH tables.
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
Result: Alice (Engineering), Bob (Marketing), Carol (Sales) — Dave has NULL department, Department 40 (Sales) has no employees.
LEFT JOIN — All From Left, Matched From Right
Returns ALL rows from the left table, plus matches from the right table.
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
Dave appears even with NULL department. Sales (40) does NOT appear because it has no matching employees.
RIGHT JOIN — All From Right, Matched From Left
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
Sales (40) appears even with no employees. Dave does NOT appear because his NULL doesn't match any department.
FULL OUTER JOIN — Everything From Both
SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
Returns ALL rows from both tables, with NULLs where there's no match. MySQL doesn't support FULL OUTER JOIN directly — use a UNION of LEFT and RIGHT JOINs.
The Complete Decision Table
| JOIN Type | When to Use |
|---|---|
| INNER | Only records that exist in both tables |
| LEFT | All from left + matched from right |
| RIGHT | All from right + matched from left |
| FULL | Everything from both tables |
| CROSS | Every combination (Cartesian product) |
| SELF | Hierarchical data (org charts, managers) |
Common Patterns
Find rows with no match (Anti-Join)
-- Employees with no department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;
Self Join for Org Charts
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
Performance Tips
- Always specify JOIN conditions — CROSS JOIN without a condition returns millions of rows
- Use EXPLAIN — Check if indexes are being used
- Join on indexed columns — JOIN on primary/foreign keys
Practice SQL with our SQL Formatter and JSON Formatter tools.
Free Developer Tools
If you found this article helpful, check out DevToolkit — 40+ free browser-based developer tools with no signup required.
Popular tools: JSON Formatter · Regex Tester · JWT Decoder · Base64 Encoder
🛒 Get the DevToolkit Starter Kit on Gumroad — source code, deployment guide, and customization templates.
Top comments (0)