Joins I: INNER JOIN, Equi-Join, Natural Join (and the Comma-Join)

Why joins are the heart of relational SQL

A single normalized table rarely holds the whole answer — a student's name lives in students, the section they enrolled in lives in sections, and the link between them lives in registrations. A join is the relational operation that recombines them: it takes two or more tables that share a common domain (a primary-key/foreign-key pair) and produces one result table.

The single rule that prevents the most common disaster: to join $n$ tables you need exactly $n-1$ join conditions. Forget one and you get a Cartesian (cross) join — every row of one table paired with every row of the other. It runs without error but returns a meaningless, enormous result (15 rows joined to 10 rows with no condition = 150 rows). If a multi-table query returns far *more* rows than you expect, suspect a missing join condition first.

Two styles, same result

The exam mixes two notations and expects you to read both:

-- Legacy comma-join + WHERE (textbook/Oracle style):
SELECT st.name, c.code
FROM   registrations r, students st, sections s, courses c
WHERE  r.student_id = st.student_id
  AND  r.section_id = s.section_id
  AND  s.course_id  = c.course_id;

-- Modern explicit JOIN … ON (write this):
SELECT st.name, c.code
FROM   registrations r
JOIN   students st ON st.student_id = r.student_id
JOIN   sections s  ON s.section_id = r.section_id
JOIN   courses  c  ON c.course_id  = s.course_id;

Both produce identical results. The JOIN … ON form is safer: the join condition sits right next to the table it joins, so a forgotten condition is a visible mistake rather than a silently-missing line buried in a long WHERE. Learn to *read* the comma form (it is all over the answer keys); *write* the JOIN … ON form.

Equi-join vs. natural join

- An equi-join matches rows on equality of the common column. The common column then appears twice in the result — once from each table. That redundancy is harmless (the two copies are always equal) but usually you just don't select both. - A natural join is an equi-join that automatically drops the duplicate common column. In Postgres you can write NATURAL JOIN, but it is risky: it silently joins on *every* identically-named column, so an unrelated name or state column shared by two tables can wreck the match. Prefer an explicit JOIN … ON and just don't project the duplicate.

Worked example: count enrolments in one section

*Question (exam 6-28a):* how many students were enrolled in section 2714 during semester I-2018?

The enrolment rows live in registrations; the semester lives in sections. So we join those two on section_id (one join, two tables — $n-1=1$ condition), filter, and count:

SELECT COUNT(*) AS enrolled
FROM   registrations r
JOIN   sections s ON s.section_id = r.section_id
WHERE  s.section_id = 2714
  AND  s.semester  = 'I-2018';

The join links each registration to its section; the WHERE keeps only section 2714 in I-2018; COUNT(*) tallies the surviving rows. Result: 3 students. Walk the data: registrations for section 2714 are students 3, 7, and 12 — three rows.