Joins in SQL: INNER, NATURAL, LEFT/RIGHT/FULL OUTER

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What is the difference between COUNT(*) and COUNT(col)? from Lesson 3.3.5
2. What does R ⟕ S add to R ⋈ S? from Lesson 3.1.4
3. Which attribute makes STUDENT ⋈ COURSE (natural) match students to their department's courses? from Lesson 3.1.3

1 · The Hook

"Number of courses each student takes, including students who take none." With FROM–WHERE joins, Esha (no enrolments) vanishes. SQL's explicit JOIN syntax brings back the outer joins of relational algebra. It also hides one trap: counting rows of an outer join gives Esha a count of 1 instead of 0.

2 · Predict First

SELECT S.name, COUNT(*) FROM STUDENT S LEFT JOIN ENROLL E ON S.roll = E.roll GROUP BY S.name; What count does Esha get? What if COUNT(E.cid) is used instead?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

COUNT() gives Esha 1: the left join produces one row for her (with NULLs), and COUNT() counts rows. COUNT(E.cid) gives 0, because her E.cid is NULL. Use COUNT of a column from the padded side.

Join syntax

FROM STUDENT S JOIN ENROLL E ON S.roll = E.roll            -- inner join (theta/equijoin)
FROM STUDENT JOIN ENROLL USING (roll)                      -- equijoin on roll, one roll column
FROM STUDENT NATURAL JOIN ENROLL                           -- all same-named columns
FROM STUDENT S LEFT  [OUTER] JOIN ENROLL E ON …            -- keep all students
FROM STUDENT S RIGHT [OUTER] JOIN ENROLL E ON …            -- keep all enrolments
FROM STUDENT S FULL  [OUTER] JOIN ENROLL E ON …            -- keep both
FROM STUDENT CROSS JOIN COURSE                             -- Cartesian product

• JOIN alone means INNER JOIN.
• NATURAL JOIN uses every common column name (STUDENT NATURAL JOIN COURSE matches on dept: 8 rows with Esha).
• USING (col) names the join columns explicitly and outputs one copy.

Outer joins on the data

SELECT S.name, E.cid FROM STUDENT S LEFT JOIN ENROLL E ON S.roll = E.roll; → the 7 enrolment rows plus (Esha, NULL): 8 rows.

ON vs WHERE in outer joins

• A condition in ON decides which rows match; unmatched left rows are still kept with NULLs.
• A condition in WHERE filters the joined result; padded rows usually fail it (UNKNOWN) and disappear, turning the outer join into an inner join.

SELECT S.name, E.cid FROM STUDENT S LEFT JOIN ENROLL E
  ON S.roll = E.roll AND E.grade = 'A';      -- 6 rows: Asha×2, Chirag, and Bhavin, Diya, Esha with NULL
SELECT S.name, E.cid FROM STUDENT S LEFT JOIN ENROLL E
  ON S.roll = E.roll WHERE E.grade = 'A';    -- 3 rows: Asha×2, Chirag

Counting with outer joins

SELECT S.name, COUNT(E.cid) FROM STUDENT S LEFT JOIN ENROLL E ON S.roll = E.roll GROUP BY S.name;

→ Asha 3, Bhavin 1, Chirag 2, Diya 1, Esha 0.

Finding "no match" rows

… LEFT JOIN ENROLL E ON S.roll = E.roll WHERE E.roll IS NULL → Esha: students with no enrolment (Lesson 3.1.4's σis null(⟕) pattern).

4 · Visual

STUDENT S  LEFT JOIN  ENROLL E  ON S.roll = E.roll

name    E.cid                       COUNT(*) per name   COUNT(E.cid) per name
Asha    C1  C2  C3                   3                   3
Bhavin  C3                           1                   1
Chirag  C1  C2                       2                   2
Diya    C4                           1                   1
Esha    NULL   <- padded row         1   (wrong!)        0   (right)

5 · Worked Examples

Example 1 Easy

How many rows does SELECT * FROM STUDENT JOIN ENROLL USING (roll); return? How many columns?

Solution

7 rows (Esha has no match). Columns: roll once + name, dept, cgpa + cid, grade = 6.

Example 2 Medium

How many rows does SELECT * FROM COURSE C LEFT JOIN ENROLL E ON C.cid = E.cid AND E.grade = 'A'; return?

Solution

Matches with grade A: C1 (roll 1), C2 (roll 3), C3 (roll 1): 3 rows. C4 has no A-grade match and is padded: 4 rows.

Example 3 GATE

SELECT COUNT(*) FROM STUDENT NATURAL JOIN COURSE; with the 5-row STUDENT table?

Solution

NATURAL JOIN matches on dept: CS 3 students × 2 courses = 6, EE 1 × 1, ME 1 × 1 → 8.

8 · Connect the Dots

Lesson 3.3.5 aggregated; this lesson joined explicitly and kept unmatched rows. Next: combining whole query results with UNION, INTERSECT, EXCEPT, and their bag versions with ALL.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · NAT · 1 mark(s) — Left join

How many rows: SELECT * FROM STUDENT S LEFT JOIN ENROLL E ON S.roll = E.roll;

Numerical answer: ________

Q2 · NAT · 2 mark(s) — ON vs WHERE

How many rows: SELECT S.name FROM STUDENT S LEFT JOIN ENROLL E ON S.roll = E.roll AND E.cid = 'C1';

Numerical answer: ________

Q3 · NAT · 2 mark(s) — ON vs WHERE

How many rows: SELECT S.name FROM STUDENT S LEFT JOIN ENROLL E ON S.roll = E.roll WHERE E.cid = 'C1';

Numerical answer: ________

Q4 · MCQ · 2 mark(s) — Counting

SELECT S.name, COUNT(*) FROM STUDENT S LEFT JOIN ENROLL E ON S.roll = E.roll GROUP BY S.name; gives Esha:

1. (A) 0
2. (B) NULL
3. (C) 1
4. (D) no row for Esha

Q5 · MCQ · 1 mark(s) — NATURAL JOIN

STUDENT NATURAL JOIN COURSE joins on:

1. (A) cid
2. (B) nothing (Cartesian product)
3. (C) roll
4. (D) dept

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

INNER JOIN … ONJOIN … USINGNATURAL JOIN (SQL)LEFT / RIGHT / FULL OUTER JOINCROSS JOINON vs WHERE in outer joins