Nexus Learn

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.idstudents.name
1Ali
2Sara
3Hina
enrollments.student_idenrollments.course
1Databases
2Networks
4Python

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?

JoinKeeps
INNER JOINOnly rows that match in both tables
LEFT JOINAll left rows, plus matches from the right
RIGHT JOINAll right rows, plus matches from the left
FULL OUTER JOINAll 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.

Keep learning