08 - SQL Joins

🎯 Learning Objectives

• Differentiate between natural join, inner join, and outer joins
• Write LEFT, RIGHT, and FULL OUTER JOIN queries
• Understand when each join type is appropriate
• Write join conditions using ON and USING clauses

📋 Prerequisites

• 05 - SQL Queries — SELECT-FROM-WHERE with multiple tables
• 03 - Relational Model — Foreign keys, referential integrity

📖 Core Content

8.1 Intuition: Combining Tables

Why This Matters: Joins are the most powerful and commonly used SQL operation. Understanding join types is essential for writing correct queries.

8.2 Join Types Overview

8.3 Inner Join

sql
-- Explicit inner join (using JOIN keyword)
SELECT I.name, T.course_id
FROM instructor I
JOIN teaches T ON I.ID = T.ID;
-- Equivalent: implicit join (comma + WHERE)
SELECT I.name, T.course_id
FROM instructor I, teaches T
WHERE I.ID = T.ID;

8.4 Natural Join

sql
SELECT name, course_id
FROM instructor NATURAL JOIN teaches;

sql
SELECT name, building
FROM instructor NATURAL JOIN department;

sql
SELECT name, building
FROM instructor JOIN department USING (dept_name);

8.5 Theta Join

sql
-- Join where instructor salary > department budget (unusual, but valid)
SELECT I.name, I.salary, D.budget
FROM instructor I
JOIN department D ON I.salary > D.budget;

8.6 Outer Joins

LEFT OUTER JOIN

sql
SELECT I.name, T.course_id
FROM instructor I
LEFT OUTER JOIN teaches T ON I.ID = T.ID;

RIGHT OUTER JOIN

sql
SELECT I.name, T.course_id
FROM instructor I
RIGHT OUTER JOIN teaches T ON I.ID = T.ID;

FULL OUTER JOIN

sql
SELECT I.name, T.course_id
FROM instructor I
FULL OUTER JOIN teaches T ON I.ID = T.ID;

8.7 Visualizing Joins with Venn Diagrams

8.8 Worked Examples

Example 1: Find all instructors and the courses they teach (including those who teach nothing)

sql
SELECT I.ID, I.name, T.course_id
FROM instructor I
LEFT OUTER JOIN teaches T ON I.ID = T.ID;

Example 2: Find all courses and the instructors who teach them (including courses with no instructor)

sql
SELECT C.course_id, C.title, T.ID
FROM course C
LEFT OUTER JOIN teaches T ON C.course_id = T.course_id;

Example 3: Full outer join — instructor and department

sql
SELECT *
FROM instructor I
FULL OUTER JOIN department D ON I.dept_name = D.dept_name;

8.9 Join Conditions: ON vs USING vs WHERE

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Natural Join Joins on ALL Common Columns

Pitfall 2: Confusing LEFT and RIGHT Join Direction

sql
SELECT * FROM A LEFT JOIN B ON ...  -- preserves A (left)
SELECT * FROM A RIGHT JOIN B ON ... -- preserves B (right)

Pitfall 3: Thinking Outer Joins Are Always Better

• More explicit about your intent
• Faster (the optimizer knows it can eliminate non-matching rows early)
• The correct semantic choice Fix: Use INNER JOIN when you want only matching rows; use OUTER JOIN when you need non-matching rows preserved.

📝 Practice Questions

Q1. Write a query using LEFT JOIN to list all departments and the number of instructors in each (including departments with zero instructors).

Answer

sql
SELECT D.dept_name, COUNT(I.ID) AS num_instructors
FROM department D
LEFT JOIN instructor I ON D.dept_name = I.dept_name
GROUP BY D.dept_name;

Without LEFT JOIN, departments with no instructors would be missing from the result. COUNT(I.ID) counts only non-NULL IDs, giving 0 for empty departments.

Q2. What is the difference between INNER JOIN and LEFT JOIN?

Answer

• INNER JOIN: Returns only rows where the join condition matches in both tables. Non-matching rows are excluded.
• LEFT JOIN: Returns ALL rows from the left table, with matching data from the right table. Non-matching rows have NULL for right-table columns.

Use INNER JOIN when you need only related data; use LEFT JOIN when you need ALL data from the primary table regardless of matches.

Q3. Write a NATURAL JOIN query and its equivalent using JOIN...ON.

Answer

sql
-- Natural join
SELECT name, course_id FROM instructor NATURAL JOIN teaches;

-- Equivalent with ON clause
SELECT I.name, T.course_id
FROM instructor I
JOIN teaches T ON I.ID = T.ID;

NATURAL JOIN automatically finds common column names and joins on them.

Q4. When would you use a FULL OUTER JOIN?

Answer

Use FULL OUTER JOIN when you need ALL rows from both tables, regardless of whether they match. Example:

• Show all employees and all departments: every employee appears (even those in no department), and every department appears (even those with no employees).

FULL OUTER JOIN = LEFT JOIN ∪ RIGHT JOIN (with duplicate removal).

Q5. Write a RIGHT JOIN that is equivalent to a given LEFT JOIN.

Answer

sql
-- LEFT JOIN: preserves instructors
SELECT I.name, T.course_id
FROM instructor I
LEFT JOIN teaches T ON I.ID = T.ID;

-- Equivalent RIGHT JOIN: move tables
SELECT I.name, T.course_id
FROM teaches T
RIGHT JOIN instructor I ON T.ID = I.ID;

RIGHT JOIN is the mirror image — just swap the table order.

Q6. What is a theta join? Give an example.

Answer

A theta join joins tables using any arbitrary condition (not just equality). The condition can use any comparison operator: =, <, >, <=, >=, <>.

Example: Find instructor-department pairs where the instructor's salary exceeds the department's budget:

sql
SELECT I.name, D.dept_name, I.salary, D.budget
FROM instructor I
JOIN department D ON I.salary > D.budget;

Q7. Why is NATURAL JOIN considered unsafe?

Answer

NATURAL JOIN automatically joins on ALL columns with the same name. If:

• You later add a column to one table with the same name as a column in the other table
• Two tables accidentally share a column name with different semantics

The join condition changes silently, potentially producing wrong results without errors.

Example: If instructor and student both have a name column, NATURAL JOIN would join on both dept_name AND name, which is almost certainly wrong.

Q8. Write a query to find instructors who do NOT teach any course, using an outer join.

Answer

sql
SELECT I.ID, I.name
FROM instructor I
LEFT JOIN teaches T ON I.ID = T.ID
WHERE T.ID IS NULL;

The LEFT JOIN keeps all instructors. Those who don't teach have NULL in T.ID. Filtering with WHERE T.ID IS NULL finds only those non-teaching instructors.

🔗 Cross-References

• Next Topic: 09 - SQL Functions, Procedures & Triggers
• Previous Topic: 07 - Advanced SQL
• Related: BSCS2003 (MAD 1) — JOIN queries in web app backends
• Related: 10 - Relational Algebra — Join in relational algebra (⋈)
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 4 (Intermediate SQL) Join Discord Previous07 - Advanced SQLNext09 - SQL Functions & Triggers