Lecture 06a: SQL Joins & Set Operations

INNER JOIN, Multi-Table Joins, Self Joins, Outer Joins, Anti-Join Patterns, and Set Operations

Lecture Source Slide Deck

Original Slides: Lecture 06a (58 slides) · Amit Kumar Dhar (IIT Bhilai)
Download lecture06a.pdf

1. The Join Mental Model

A join combines rows from two or more tables based on a related column between them. Logically, think of an INNER JOIN as:

Generating candidate pairs (Cartesian Product).
Applying the ON condition to discard invalid matches.

-- Explicit INNER JOIN with ON
SELECT student.name, department.building
FROM student
INNER JOIN department ON student.dept_name = department.dept_name;

-- USING shorthand (works when column names match identically)
SELECT student.name, building
FROM student
INNER JOIN department USING (dept_name);

2. Multi-Table Joins

In relational designs, answers frequently require traversing foreign key chains across multiple tables:

-- Question: Find each student's name, the title of the course they took, and their grade
-- Path: student -> takes -> section -> course
SELECT 
    s.name AS student_name,
    c.title AS course_title,
    t.grade
FROM student s
JOIN takes t ON s.id = t.id
JOIN section sec ON t.course_id = sec.course_id 
                AND t.sec_id = sec.sec_id 
                AND t.semester = sec.semester 
                AND t.year = sec.year
JOIN course c ON sec.course_id = c.course_id;

3. Self-Joins

A table can be joined to itself by giving it two distinct aliases.

Common Patterns

Finding Peers: Instructors in the same department.

To avoid self-matches (a.id = b.id) and mirror duplicates ((A, B) and (B, A)), use strict inequality a.id < b.id:

SELECT a.name AS instructor1, b.name AS instructor2, a.dept_name
FROM instructor a
JOIN instructor b ON a.dept_name = b.dept_name 
                 AND a.id < b.id;

Hierarchical Prerequisites:

SELECT c.title AS course_name, p_course.title AS prerequisite_name
FROM course c
JOIN prereq p ON c.course_id = p.course_id
JOIN course p_course ON p.prereq_id = p_course.course_id;

4. Outer Joins & The Anti-Join Pattern

Outer joins ensure that rows from the preserved table appear in the output even if no matching row exists in the joined table (missing attributes are populated with NULL).

The Anti-Join Pattern

To find rows that have no counterpart in another table:

-- Find sections that currently have NO assigned instructor
SELECT sec.course_id, sec.sec_id, sec.semester, sec.year
FROM section sec
LEFT JOIN teaches t ON sec.course_id = t.course_id
                   AND sec.sec_id = t.sec_id
                   AND sec.semester = t.semester
                   AND sec.year = t.year
WHERE t.id IS NULL; -- Anti-join condition

The Outer Join Trap

If you place a filter condition on the right-hand table inside the WHERE clause rather than the ON clause, you will silently convert the LEFT JOIN back into an INNER JOIN!

-- WRONG: Eliminates rows where t.id is NULL, turning it into an INNER JOIN!
SELECT s.course_id, t.id
FROM section s
LEFT JOIN teaches t ON s.course_id = t.course_id
WHERE t.semester = 'Fall';

-- CORRECT: Keeps sections with no instructor, and checks semester in the join match
SELECT s.course_id, t.id
FROM section s
LEFT JOIN teaches t ON s.course_id = t.course_id 
                   AND t.semester = 'Fall';

5. Set Operations

Set operations combine the result sets of two independent SELECT queries:

-- UNION (Removes duplicates, requires sorting/hash pass)
SELECT course_id FROM section WHERE semester = 'Fall' AND year = 2025
UNION
SELECT course_id FROM section WHERE semester = 'Spring' AND year = 2026;

-- UNION ALL (Fast, preserves duplicates without sorting)
SELECT course_id FROM section WHERE semester = 'Fall'
UNION ALL
SELECT course_id FROM section WHERE semester = 'Spring';

-- INTERSECT (Returns rows appearing in BOTH queries)
SELECT course_id FROM section WHERE year = 2025
INTERSECT
SELECT course_id FROM section WHERE year = 2026;

-- EXCEPT (Returns rows in query 1 that do NOT appear in query 2)
SELECT course_id FROM course
EXCEPT
SELECT course_id FROM section;

Lecture 06a: SQL Joins & Set Operations

1. The Join Mental Model

2. Multi-Table Joins

3. Self-Joins

Common Patterns

4. Outer Joins & The Anti-Join Pattern

The Anti-Join Pattern

5. Set Operations

6. Topic Practice Questions

On this page

Lecture 06a: SQL Joins & Set Operations

Q1: List all courses and the name of the instructor teaching them, including courses that have no instructor assigned.

Q2: Find instructors who teach nothing using a LEFT JOIN anti-join.

Q3: Explain the row count difference between INNER JOIN and FULL OUTER JOIN.

On this page