Joins: putting tables back together

Normalization splits data across tables. A join puts it back for one query. Which join you choose decides which rows survive.

SELECT s.name, c.title, e.grade
FROM students s
JOIN enrolment e ON e.roll_no = s.roll_no
JOIN courses   c ON c.code    = e.course_code;

The question that decides the join is: do I want the rows with nothing on the other side? "List every student and their courses" includes students who enrolled in nothing: that is a LEFT JOIN. An INNER JOIN silently drops them, and the query looks like it worked.

A subquery answers a question that needs its own query first:

SELECT name FROM students
WHERE cgpa > (SELECT AVG(cgpa) FROM students);

A correlated subquery runs once per outer row, because it mentions the outer row. That is often a join written the slow way.

Takeaway: pick the join by asking which unmatched rows must survive. Getting that wrong loses data with no error.