JOIN is one of the key SQL operations that lets you combine data from multiple tables. Understanding JOINs is essential for working with relational databases.
What is a JOIN?
A JOIN connects rows from two or more tables based on a related column. Imagine two tables: employees and departments. Each employee has a department_id pointing to their department.
INNER JOIN — Intersection
Returns only rows that have matches in both tables.
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;If an employee has no department (NULL) or the department doesn't exist — the row won't appear in results.
LEFT JOIN — All from Left Table
Returns all rows from the left table and matching ones from the right. No match? The right side fills with NULL.
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;Use LEFT JOIN when you need all records from your main table, even without related data.
RIGHT JOIN — All from Right Table
Same as LEFT JOIN but prioritizes the right table. Rarely used in practice — it's usually simpler to swap tables and use LEFT JOIN.
FULL OUTER JOIN — Everything Combined
Returns all rows from both tables. Where there's no match — fills with NULL.
SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;Note: MySQL doesn't support FULL OUTER JOIN directly — use UNION of LEFT and RIGHT JOIN.
CROSS JOIN — Cartesian Product
Every row from the first table pairs with every row from the second. Result: N × M rows.
SELECT e.name, p.project_name
FROM employees e
CROSS JOIN projects p;SELF JOIN — Table with Itself
Joining a table with itself. Classic example — employee-manager hierarchy:
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Common Mistakes
- Missing ON clause — without it, JOIN becomes CROSS JOIN
- Duplicate rows — one-to-many relationships duplicate rows. Use DISTINCT or GROUP BY
- NULL in conditions — NULL ≠ NULL, so NULL rows won't join through regular =
Which JOIN to Choose?
- INNER JOIN — need only complete data from both tables
- LEFT JOIN — need all main records + optional related data
- FULL JOIN — need complete overview of both tables
- CROSS JOIN — generating all combinations