MySQL Joins

Last updated: October 19, 2026Oct 19, 2026 5 min read

Relational databases split data into several tables so each fact is stored once: students in one table, departments in another. A join puts them back together in a query, matching rows through a shared key such as dept_id. This page explains how joins work and introduces every join type, each of which has its own detailed tutorial.

1. Sample Tables

Table: students (5 rows)

Table: departments (3 rows)

Table: courses (4 rows)

Table: enrolments (5 rows)

2. How a Join Works

SELECT columns
FROM table1
[INNER | LEFT | RIGHT | CROSS] JOIN table2
  ON table1.key = table2.key;

SELECT s.name, d.dept_name
FROM students s
JOIN departments d ON d.dept_id = s.dept_id;

Result: 4 rows

3. The Join Types at a Glance

4. Same Tables, Different Joins

SELECT s.name, d.dept_name FROM students s INNER JOIN departments d ON d.dept_id = s.dept_id;

INNER: 4 matching pairs

SELECT s.name, d.dept_name FROM students s LEFT JOIN departments d ON d.dept_id = s.dept_id;

LEFT: adds Diya with NULL

SELECT s.name, d.dept_name FROM students s RIGHT JOIN departments d ON d.dept_id = s.dept_id;

RIGHT: adds History with NULL

5. FULL OUTER JOIN in MySQL

MySQL has no FULL OUTER JOIN. Combine a LEFT and a RIGHT join with UNION:

SELECT s.name, d.dept_name FROM students s LEFT JOIN departments d ON d.dept_id = s.dept_id
UNION
SELECT s.name, d.dept_name FROM students s RIGHT JOIN departments d ON d.dept_id = s.dept_id;

Result: 6 rows

6. Joining Three or More Tables

Add one JOIN per table. Which student takes which course?

SELECT s.name, c.title, e.grade
FROM enrolments e
JOIN students s ON s.student_id = e.student_id
JOIN courses  c ON c.course_id  = e.course_id
ORDER BY s.name, c.title;

Result: 5 rows

7. ON vs USING

When the key column has the same name in both tables, USING (col) is a shorthand and shows the column once:

SELECT name, dept_name FROM students JOIN departments USING (dept_id) ORDER BY name;

Result: 4 rows

ON is needed when names differ or the condition is more complex. Always use table aliases (s, d) to keep column references clear.

8. Conditions in ON vs WHERE

For INNER joins it doesn't matter. For LEFT joins it does: a condition in WHERE removes unmatched rows; in ON it only limits what is matched:

SELECT s.name, d.dept_name FROM students s LEFT JOIN departments d ON d.dept_id = s.dept_id AND d.dept_name = 'Physics';

Filter in ON: all students kept

SELECT s.name, d.dept_name FROM students s LEFT JOIN departments d ON d.dept_id = s.dept_id WHERE d.dept_name = 'Physics';

Filter in WHERE: behaves like INNER JOIN

9. Join Performance Tips

• Index the join columns, especially foreign keys (InnoDB indexes foreign key columns automatically).
• Join on columns of the same type and character set, otherwise indexes may not be used.
• Select only the columns you need.
• Check the plan with EXPLAIN; type = ALL on a big table means a missing index.
• MySQL 8 uses hash joins when no index is available, which is much faster than old nested loops.

10. Summary

• A join combines rows of two tables whose key values match.
• INNER keeps matches; LEFT/RIGHT also keep unmatched rows from one side; CROSS makes all combinations; a self join pairs a table with itself.
• Emulate FULL OUTER JOIN with LEFT JOIN UNION RIGHT JOIN.
• Use ON (or USING), aliases, and indexes on join columns.