SQL Joins Explained with Simple Examples
A join combines rows from two tables using a column they share. Most real queries need joins, so they are one of the most important things to learn in SQL.
The two tables we will use
Imagine a students table and an enrollments table. Ali and Sara have enrolled in courses. Hina has not enrolled in anything. One enrollment belongs to a student who is not in the students table.
| students.id | students.name |
|---|---|
| 1 | Ali |
| 2 | Sara |
| 3 | Hina |
| enrollments.student_id | enrollments.course |
|---|---|
| 1 | Databases |
| 2 | Networks |
| 4 | Python |
INNER JOIN: only matches
SELECT s.name, e.course
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id;
You get only rows that match in both tables: Ali with Databases, and Sara with Networks. Hina has no enrollment and the Python row has no student, so both are left out.
LEFT JOIN: everything from the left table
SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;
You get every student. Ali and Sara show their courses. Hina is included too, with NULL in the course column. This is the join to use for questions like "which students have no enrollment?" (add WHERE e.course IS NULL).
RIGHT JOIN: everything from the right table
RIGHT JOIN is the mirror image. You get every enrollment, and the Python row appears with NULL for the student name. Many people simply swap the tables and use LEFT JOIN instead.
FULL OUTER JOIN: everything from both
You get all rows from both tables: matched pairs, students with no enrollment, and enrollments with no student. MySQL does not support FULL OUTER JOIN directly, but you can combine a LEFT JOIN and a RIGHT JOIN with UNION.
Which join should you use?
| Join | Keeps |
|---|---|
| INNER JOIN | Only rows that match in both tables |
| LEFT JOIN | All left rows, plus matches from the right |
| RIGHT JOIN | All right rows, plus matches from the left |
| FULL OUTER JOIN | All rows from both tables |
Frequently asked questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN drops rows with no match. LEFT JOIN keeps every row from the left table and fills in NULL where there is no match.
What does ON do in a join?
ON says which columns to compare, for example s.id = e.student_id. Without it you get every possible pair of rows (a cross join).
Can I join more than two tables?
Yes. Write one JOIN after another, each with its own ON condition.