SQL JOINs Explained: INNER, LEFT, RIGHT, and FULL with Examples

What are SQL JOINs?

JOINs are one of the most powerful features in SQL. They let you combine rows from two or more tables based on a related column. Understanding JOINs is essential for working with relational databases.

Imagine you have a students table and an enrollments table. A JOIN lets you answer questions like 'which students are enrolled in which courses?' by combining data from both tables.

INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables.

SELECT s.name, c.title
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

This returns only students who are enrolled in at least one course. Students with no enrollments and courses with no students are excluded.

LEFT JOIN

A LEFT JOIN returns all rows from the left table, plus matched rows from the right table. If there's no match, the right-side columns are NULL.

SELECT s.name, COUNT(e.course_id) AS course_count
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name;

This returns ALL students, even those with zero enrollments (their course_count will be 0).

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN is the reverse of LEFT JOIN — it returns all rows from the right table.

FULL OUTER JOIN returns all rows from both tables, with NULLs where there's no match on either side.

SELECT s.name, c.title
FROM students s
FULL OUTER JOIN enrollments e ON s.id = e.student_id
FULL OUTER JOIN courses c ON e.course_id = c.id;

FULL OUTER JOINs are less common but useful when you need a complete picture of all data in both tables.

Practice JOINs Now

The best way to learn JOINs is to practice them. Ira SQL Pro has a built-in database with students, courses, and enrollments tables — perfect for practicing every type of JOIN.

Try our SQL tutorials on JOINs, or use the AI assistant to generate JOIN queries from plain English descriptions.