Joins

You normalized in Module 4 and it worked beautifully. Student names live in Student. Course codes live in Enrollment. Nothing repeats.

Now your boss wants one report: student name next to course code.

That data is in two different tables. Now what?

Why & what

This is the bill for normalization, and it comes due here. You split tables to stop repetition; joins are how you put the pieces back together for one query.

A join combines rows from two tables by matching a value in one against a value in the other — almost always a foreign key matching a primary key.

How it works

Keep it tiny so you can see every row. Two students, two enrollments — deliberately mismatched.

Student: 101 Aisha, 104 Dev Enrollment: 101 DBMS101, 999 OS101

So Dev enrolled in nothing, and roll 999 doesn't exist as a student.

1. INNER JOIN — only matches survive. Match Student.roll_no with Enrollment.roll_no. Only 101 exists on both sides. One row out. Dev disappears. Roll 999 disappears.

SELECT s.name, e.course
FROM Student s
INNER JOIN Enrollment e ON s.roll_no = e.roll_no;

2. LEFT JOIN — keep everything on the left. Every student stays, matched or not. Aisha gets her course. Dev has no match, so his course comes back NULL. Two rows out.
3. RIGHT JOIN — keep everything on the right. Every enrollment stays. The 999 row survives with name = NULL, because no student matched it.
4. FULL JOIN — keep everything on both sides. Aisha's matched row, Dev with a NULL course, and 999 with a NULL name. Three rows out.
5. Read the NULLs as a message. A NULL in join output never means "empty data." It means "nothing on the other side matched." That's why LEFT JOIN plus WHERE course IS NULL is the standard way to find students who enrolled in nothing.

Pause here. Which join would you use to list students who have no enrollments at all?

LEFT JOIN from Student, then WHERE e.course IS NULL. Keep every student, then keep only the ones whose match came back empty.

Notice: NULL marks the side that had nothing to match with.

Common confusion

LEFT vs RIGHT confuses people far more than it should. "Left" just means the table written before the JOIN keyword. That's it. And every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order — which is why you'll rarely see RIGHT JOIN in real code.

Second: forgetting the ON condition. Write a join with no ON and you get a cross join — every row paired with every other row. Two tables of 1,000 rows give you a million rows. It's not an error message; it's just a very wrong, very slow answer.

Third, and this one is subtle: filtering in WHERE vs in ON. With a LEFT JOIN, putting a condition on the right table in WHERE quietly turns it back into an INNER JOIN — because the unmatched rows have NULLs, and NULL fails the WHERE test, so they get dropped. If you want to keep unmatched rows while filtering, the condition belongs in ON.

Interview angle

1. "Explain INNER, LEFT, RIGHT and FULL JOIN." — Walk two small tables through all four and say how many rows each returns. Concrete row counts beat definitions here.
2. "How do you find rows in table A with no match in table B?" — LEFT JOIN from A, then WHERE b.some_column IS NULL. This is asked constantly.
3. "What's a self join and when would you use it?" — Joining a table to itself, using two aliases. Classic case: an Employee table where manager_id points at another row in the same table.
4. "What happens if you forget the ON clause?" — A cross join: every row against every row. Fine on purpose, disastrous by accident.

Recap

A join matches rows across tables — INNER keeps only matches, LEFT and RIGHT keep one whole side, FULL keeps both, and any NULL in the output means that side had nothing to match.