SQL JOINs: INNER, LEFT, RIGHT, and FULL OUTER Explained

INNER JOIN — matching rows only

INNER JOIN returns rows where the join condition matches in BOTH tables. Non-matching rows are excluded.

-- Returns only employees who have a department
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- Equivalent syntax:
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

LEFT JOIN — all rows from the left table

LEFT JOIN returns all rows from the left table, with NULL for columns from the right table where there's no match.

-- Returns ALL employees, even those with no department
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- Employees without departments will show:
-- name: "Alice", department_name: NULL

-- Find 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;

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN returns all rows from both tables.

-- RIGHT JOIN: all departments, even empty ones
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- FULL OUTER JOIN: everything
SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
-- Shows unmatched employees AND unmatched departments

-- Note: MySQL doesn't support FULL OUTER JOIN directly
-- Workaround: LEFT JOIN UNION RIGHT JOIN

Joining multiple tables

You can chain JOIN clauses to join more than two tables.

SELECT
    e.name        AS employee,
    d.name        AS department,
    m.name        AS manager,
    p.title       AS project
FROM employees e
JOIN departments d ON e.department_id = d.id
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN project_assignments pa ON e.id = pa.employee_id
LEFT JOIN projects p ON pa.project_id = p.id
ORDER BY e.name;

Self join

A self join joins a table to itself. Common for hierarchical data like employee-manager relationships.

-- Employee and their manager (both in same table)
SELECT
    e.name       AS employee,
    m.name       AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Employees who earn more than their manager
SELECT e.name, e.salary, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Exam tip

The most common JOIN exam question: "What's the difference between INNER JOIN and LEFT JOIN?" — INNER only returns matching rows from both sides; LEFT returns all rows from the left table with NULLs where there's no match.