How to Use MySQL JOINs with Examples

A JOIN combines rows from two or more tables, using a column they share.

Join types

1. INNER JOIN returns rows with a match in both tables.
2. LEFT JOIN returns all rows of the left table and the matches from the right (NULL if none).
3. RIGHT JOIN returns all rows of the right table and the matches from the left.
4. FULL JOIN returns all rows when there is a match in either table. MySQL has no FULL JOIN, so combine a LEFT and a RIGHT join with UNION.
5. CROSS JOIN returns every combination of rows (the Cartesian product).
6. SELF JOIN joins a table with itself.

Create the example tables

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

INSERT INTO departments VALUES (1, 'Sales'), (2, 'IT'), (3, 'HR');
INSERT INTO employees VALUES
    (1, 'Anna', 'Lee', 1),
    (2, 'Ben', 'Khan', 2),
    (3, 'Carla', 'Diaz', NULL);

INNER JOIN

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Only Anna and Ben appear. Carla has no department, and HR has no employee.

LEFT JOIN

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

All employees show up. Carla's department is NULL.

RIGHT JOIN

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

All departments show up, including HR with no employee.

FULL JOIN (with UNION)

SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

CROSS JOIN

SELECT employees.first_name, departments.department_name
FROM employees
CROSS JOIN departments;

3 employees times 3 departments gives 9 rows.

SELF JOIN

A self join compares rows of the same table. For example, if employees has a manager_id column:

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Tips

• Index the columns you join on.
• Use table aliases (e, d) to keep queries short.
• Select only the columns you need, not *.

Quick recap

• INNER keeps matches only, LEFT keeps the whole left table, RIGHT the whole right table.
• Use UNION to build a FULL JOIN in MySQL.