SQL Joins (INNER, LEFT, RIGHT, FULL)

Why joins?

In a normalized database, related data lives in different tables. Student names are in Students, course registrations in Enrollments. To answer “which courses is each student taking?” we join the tables on a related column:

Students.id  =  Enrollments.student_id

The example tables

Students

id	name
1	Asha
2	Ravi
3	Meena
4	John

Enrollments

student_id	course
1	DBMS
1	OS
3	CN
5	AI

Ravi and John have no enrollments, and enrollment 5 has no matching student — those rows are what make the join types different.

The four main joins

INNER JOIN — only matches

SELECT s.name, e.course
FROM Students s
INNER JOIN Enrollments e ON s.id = e.student_id;

Result: Asha–DBMS, Asha–OS, Meena–CN (3 rows). Unmatched rows on either side are dropped.

LEFT JOIN — all left rows

Every student appears; students without an enrollment get NULL in the course columns. Result: 5 rows (the 3 matches + Ravi and John with NULL).

RIGHT JOIN — all right rows

Every enrollment appears; enrollment 5 gets NULL for the student columns. Result: 4 rows.

FULL OUTER JOIN — everything

All rows from both tables, matched where possible: 6 rows. (MySQL doesn’t support FULL JOIN directly — combine a LEFT and a RIGHT join with UNION.)

Venn-diagram summary

Join	Keeps
INNER	A ∩ B (matches only)
LEFT	All of A, plus matches from B
RIGHT	All of B, plus matches from A
FULL OUTER	A ∪ B
CROSS	Every combination (no ON condition)

Useful patterns

-- Students with no enrollment (an "anti-join")
SELECT s.name
FROM Students s
LEFT JOIN Enrollments e ON s.id = e.student_id
WHERE e.student_id IS NULL;

-- Number of courses per student (0 for nobody)
SELECT s.name, COUNT(e.course) AS courses
FROM Students s
LEFT JOIN Enrollments e ON s.id = e.student_id
GROUP BY s.name;

A self join joins a table with itself (e.g. employees and their managers in the same table).

How databases execute joins

• Nested loop: for each row of A, scan B — O(n·m), fine for small tables or when B has an index.
• Hash join: build a hash table on the smaller table, then probe it — O(n + m).
• Sort-merge join: sort both on the key and merge — great when data is already sorted.

The query optimiser chooses automatically; EXPLAIN shows which one it used.

Common mistakes

• Forgetting the ON condition → an accidental CROSS JOIN with huge results.
• Filtering the right table in WHERE after a LEFT JOIN (e.g. WHERE e.course = 'DBMS'), which silently turns it back into an INNER JOIN — put such conditions in the ON clause instead.
• Comparing with = NULL instead of IS NULL.