← Computer Science blog
Concept guide8 October 2026 · 3 min read

SQL Joins Explained: INNER, LEFT, RIGHT and FULL With Two Tiny Tables

CSEITAI&DSBCABSc CSBSc ITCOIT

Joins are the part of SQL where students stop trusting themselves. The fix is to use two tables small enough to hold in your head, and to run every join on the same pair.

The two tables

students

idname
1Aisha
2Rohan
3Meera

enrollments

student_idcourse
1DBMS
1OS
2DBMS
4CN

Notice what we built in on purpose:

  • Meera (id 3) has no enrollment.
  • Enrollment for student_id = 4 has no matching student.
  • Aisha has two enrollments.

Those three oddities are what make the join types behave differently.

INNER JOIN: only the matches

SELECT s.name, e.course
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id;
namecourse
AishaDBMS
AishaOS
RohanDBMS

Meera is gone (no enrollment) and the student_id = 4 row is gone (no student). INNER JOIN keeps a row only when both sides match. Aisha appears twice because she matches twice.

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;
namecourse
AishaDBMS
AishaOS
RohanDBMS
MeeraNULL

All three students appear. Meera has no match, so her course is NULL. The enrollment for student 4 is still dropped, because it belongs to the right table and has no partner on the left.

Reading rule: "LEFT JOIN: keep every row of the left table, fill the right side with NULL when there is no match."

RIGHT JOIN: everything from the right table

SELECT s.name, e.course
FROM students s
RIGHT JOIN enrollments e ON s.id = e.student_id;
namecourse
AishaDBMS
AishaOS
RohanDBMS
NULLCN

Now every enrollment appears. The one for student 4 shows up with NULL for the name, and Meera, who is on the left, is the one that disappears.

A RIGHT JOIN is just a LEFT JOIN with the tables swapped. Most people write LEFT JOINs only and reorder the tables.

FULL OUTER JOIN: everything from both

SELECT s.name, e.course
FROM students s
FULL OUTER JOIN enrollments e ON s.id = e.student_id;
namecourse
AishaDBMS
AishaOS
RohanDBMS
MeeraNULL
NULLCN

Both oddities survive: Meera with no course, and the course with no student. (MySQL does not support FULL OUTER JOIN directly. The usual workaround is a LEFT JOIN combined with a RIGHT JOIN using UNION.)

The pattern that comes up in exams: "find the missing ones"

"List students who have not enrolled in any course."

SELECT s.name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.student_id IS NULL;

Result: Meera. LEFT JOIN keeps everyone, and the WHERE ... IS NULL filter then keeps only those who found no match. This one pattern is worth memorizing.

One-glance summary

JoinKeepsUnmatched rows show
INNERMatches onlyNever
LEFTAll of leftNULL on the right
RIGHTAll of rightNULL on the left
FULL OUTERAll of bothNULL on whichever side is missing

Two mistakes to avoid

  1. Putting the filter in the wrong place. In a LEFT JOIN, a condition on the right table placed in WHERE can silently turn it into an INNER JOIN, because NULL rows fail the condition. Put it in the ON clause if you want to keep unmatched left rows.
  2. Forgetting the ON clause. A join without a condition produces a Cartesian product: every row paired with every row.

Sumantrak™ is an AI mentor built for Indian engineering, diploma, BCA and BSc CS/IT students — DBMS, SQL practicals, and the rest of your syllabus, explained like this. Free to start →