SQL Joins

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

Why Joins?

The students table only stores a course_id number. To show a student's name next to the course title, you need data from both tables. The link is:

students.course_id  =  courses.id

Storing the course title once in courses, instead of in every student row, avoids spelling mistakes and makes changes easy. Joins put the data back together when you read it.

INNER JOIN

INNER JOIN returns only the rows that have a match in both tables.

SELECT students.name, courses.title
FROM students
INNER JOIN courses ON students.course_id = courses.id;

Hamza is missing because he has no course. Digital Marketing is missing because it has no students. Writing just JOIN means the same as INNER JOIN.

Table Aliases

Writing full table names gets long. Give each table a short alias:

SELECT s.name, c.title, c.duration_months
FROM students AS s
JOIN courses AS c ON s.course_id = c.id
WHERE c.duration_months > 4;

LEFT JOIN

LEFT JOIN returns all rows from the left (first) table, plus the matching rows from the right table. Where there is no match, the right side is NULL.

SELECT s.name, c.title
FROM students AS s
LEFT JOIN courses AS c ON s.course_id = c.id;

Now Hamza appears, with NULL as his course.

Find Rows Without a Match

Put courses on the left to see every course, then keep only the ones with no student:

SELECT c.title
FROM courses AS c
LEFT JOIN students AS s ON s.course_id = c.id
WHERE s.id IS NULL;

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN: it keeps all rows of the right table.

SELECT s.name, c.title
FROM students AS s
RIGHT JOIN courses AS c ON s.course_id = c.id;

Every course appears, and Digital Marketing gets NULL as its student. (Without ORDER BY, your database may list the rows in a different order.) Most developers simply swap the tables and use LEFT JOIN, which reads more naturally.

FULL JOIN

FULL JOIN (or FULL OUTER JOIN) keeps all rows from both tables. Here it would show Hamza with a NULL course and Digital Marketing with a NULL student. PostgreSQL and SQL Server support it; MySQL does not, but you can get the same result by combining a LEFT and a RIGHT join with UNION.

Join Types at a Glance

Exercise