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

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

Almost every student project that grows past one table hits the same moment: the names live in one table, the marks or orders live in another, and a single query has to combine them. A JOIN does that combining. It matches rows from two tables on a shared value, usually a key such as a student id, and returns one combined row for every match it finds.

The problem JOINs solve

Splitting data into tables avoids repeating the same facts everywhere. A student's name is stored once in a students table, and each enrolment only stores the student id. That is good design, but it means no single table answers a question like "which students are enrolled in which courses, and who has not enrolled in anything yet?" Without a JOIN, you would fetch two lists and match them by hand in code. With a JOIN, the database does the matching, and it does it consistently for every row.

Three example tables used throughout

Keep these small tables in mind. They are example data, invented only to make the joins visible, not results from any real system.

students:

id	name
1	Anaya
2	Rohan
3	Meera

courses:

id	title
10	Databases
20	Networks

enrolments:

student_id	course_id
1	10
1	20
2	10

Notice the gaps, because the gaps are the whole lesson. Meera (id 3) has no enrolment row. Networks (id 20) has one enrolment, Databases has two, and no course is left without students in this example. A different join type answers a different question about those gaps.

How a JOIN works, step by step

1. Pick a left table and a right table. The table named first in the query is the left table; the other is the right table.
2. Write the match condition in the ON clause, for example enrolments.student_id = students.id. This is the rule the database uses to pair rows.
3. The database tests combinations against that rule and keeps the pairs that match.
4. For pairs that do not match, the join type decides whether the unmatched row still appears, with NULLs filling the missing side.
5. Columns from both tables are available in the result, so you can select, filter and sort on either side.

The ON clause is not decoration. Change the match condition and you change the meaning of the result, even if the tables are the same.

INNER JOIN: only the matches

An INNER JOIN returns only rows that match on both sides. Students with no enrolment disappear, and courses with no enrolment disappear too.

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

Result:

name	title
Anaya	Databases
Anaya	Networks
Rohan	Databases

Meera is missing because she has no enrolment row. That is correct for the question "list every enrolment with its student and course names", and wrong for the question "list every student, including those not yet enrolled". The join type must match the question, not just the tables.

LEFT JOIN: keep every row on the left

A LEFT JOIN keeps every row from the left table, even when nothing matches on the right. Unmatched columns come back as NULL.

SELECT students.name, courses.title
FROM students
LEFT JOIN enrolments
  ON enrolments.student_id = students.id
LEFT JOIN courses
  ON courses.id = enrolments.course_id;

Result:

name	title
Anaya	Databases
Anaya	Networks
Rohan	Databases
Meera	NULL

Meera now appears, with a NULL course. That NULL is information: it means "no enrolment found", not "unknown course". Use LEFT JOIN whenever the report must include the base list in full, such as all students, all products, or all tickets, with optional related data attached.

RIGHT JOIN: keep every row on the right

A RIGHT JOIN is the mirror image: it keeps every row from the right table, even when nothing matches on the left.

SELECT students.name, courses.title
FROM courses
LEFT JOIN enrolments
  ON enrolments.course_id = courses.id
LEFT JOIN students
  ON students.id = enrolments.student_id;

In practice, most developers rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order, as shown above, because it reads in the same direction as the rest of the query. Knowing RIGHT JOIN still matters: you will meet it in inherited code, and recognising it as "keep the right side" saves a long debugging session. If you write one yourself, add a comment saying which side must survive, so the next reader does not have to reverse-engineer it.

FULL JOIN: keep both sides

A FULL JOIN, also called FULL OUTER JOIN, keeps unmatched rows from both sides. Every student appears, every course appears, and matched pairs appear once.

SELECT students.name, courses.title
FROM students
FULL JOIN enrolments
  ON enrolments.student_id = students.id
FULL JOIN courses
  ON courses.id = enrolments.course_id;

Use it for reconciliation questions such as "which students have no enrolment, and which courses have no students?" Note a practical caveat: some databases used in student projects, notably MySQL, do not support FULL JOIN directly. The usual workaround is a LEFT JOIN combined with a RIGHT JOIN using UNION, keeping the same column list in both parts.

Which join answers which question

Question	Join
List every enrolment with names	INNER JOIN
List every student, even without enrolments	LEFT JOIN
List every course, even without students	LEFT JOIN with courses on the left
Find both kinds of gap at once	FULL JOIN
Count enrolments per course, including zero	LEFT JOIN, then GROUP BY with COUNT on the right key

If you cannot state the question in one sentence, do not write the query yet. The sentence tells you which side must survive, and that tells you the join.

ON versus WHERE: the filter trap

This is the mistake that costs students the most time. In a LEFT JOIN, a condition in the ON clause filters the right table before matching, so unmatched left rows survive. The same condition in the WHERE clause filters the finished result, and a NULL from an unmatched row fails most comparisons, so the LEFT JOIN silently becomes an INNER JOIN.

Compare these two. The first keeps Meera; the second drops her.

-- Keeps students with no matching enrolment:
SELECT students.name
FROM students
LEFT JOIN enrolments
  ON enrolments.student_id = students.id
  AND enrolments.course_id = 20;

-- Drops them: NULL course_id fails the WHERE test:
SELECT students.name
FROM students
LEFT JOIN enrolments
  ON enrolments.student_id = students.id
WHERE enrolments.course_id = 20;

Rule of thumb: put match rules in ON, and put filters on the surviving side in WHERE. When you filter on the optional side of a LEFT JOIN, either keep the condition in ON or explicitly allow NULL in the WHERE clause.

Counting, duplicates and NULLs

Three habits prevent the next round of surprises.

First, COUNT() and COUNT(column) differ after a LEFT JOIN. COUNT() counts the NULL row for Meera; COUNT(enrolments.student_id) does not, because COUNT skips NULLs. For "enrolments per student", count the right-side key.

Second, a join can multiply rows. If a student has two enrolments, that student appears twice. That is correct, but it breaks a naive total: summing a per-student value after joining enrolments counts it once per enrolment. Aggregate before joining, or group carefully, when the joined table has many rows per base row.

Third, never test NULL with = NULL. NULL means "no value here", and comparing it with equals gives neither true nor false in the usual sense. Use IS NULL and IS NOT NULL, for example WHERE enrolments.student_id IS NULL to find students with no enrolment.

A 20-minute practice routine

1. Create the three tables above with a handful of rows, including one student with no enrolment and one course with no students.
2. Run the INNER JOIN and write down, by hand, which rows you expect before looking at the output.
3. Change it to a LEFT JOIN from students and check that the unenrolled student appears with NULL.
4. Move the course filter between ON and WHERE and watch the unenrolled student appear and disappear.
5. Write the query for "students with no enrolment" using LEFT JOIN plus IS NULL, and the query for "courses with no students" the same way.

Predicting the result before running it is the whole exercise. If the prediction is wrong, the gap in understanding is visible immediately, while the tables are still small enough to reason about.

Frequently asked questions

Do I need a foreign key to JOIN?

No. A JOIN only needs a match condition, and it will happily join on any comparable columns. A foreign key is still worth adding: it documents the relationship, stops orphan rows from being inserted, and helps the database plan the query. Joining on unrelated columns, or on columns with different meanings, is how nonsense results are made.

Why does my JOIN return far more rows than either table?

Either the match condition is too loose, or one side has several matching rows per key. Check for duplicate keys first, then check whether you joined on the right columns. A join on a non-unique column, such as a city name instead of a student id, multiplies rows in ways that look like a bug but are exactly what the query asked for.

Should I JOIN in SQL or combine lists in code?

Prefer SQL when the question is about matching, filtering, counting or sorting across tables. The database is built for it, and one query replaces a loop full of lookups. Combine in code only when the data comes from different systems, or when the matching logic genuinely cannot be expressed as a condition.

What about CROSS JOIN and SELF JOIN?

A CROSS JOIN pairs every row with every row and is rarely what you want; it is mainly useful for small combination tables. A SELF JOIN joins a table to itself, which is the standard way to model hierarchies such as employees and managers, where the manager id points back into the same table. Both are worth knowing, but neither replaces the four joins above for everyday reporting.

Limitations

This guide covers two-table logic and the common multi-table chain, not query performance. On large tables, missing indexes on join columns, joining on text columns with different collations, and accidental cross joins can all turn a fast query into a slow one; that tuning work belongs in a separate guide on indexing and query plans. The example SQL follows widely supported standard syntax, but FULL JOIN support and some NULL-handling details vary by database, so check your own system's documentation before relying on a feature in a graded project.