Combining Tables with JOINs

Our University schema stores related information across five tables. Students reference departments, courses reference both departments and instructors, and enrollments link students to courses. To answer most real-world questions you must combine these tables. That is exactly what SQL joins do.

Learning Goals

• Understand INNER, LEFT, and (conceptual) RIGHT/FULL joins using University data
• Write join queries that combine students, courses, departments, and enrollments
• Use table aliases and fully qualified column names to avoid ambiguity
• Recognize common mistakes when joining multiple tables

INNER JOIN

An INNER JOIN returns only rows where the join condition matches. Use it when you need data that exists on both sides—for example, listing students who have declared a department.

Students with declared departments

SELECT s.student_id,
       s.first_name || ' ' || s.last_name AS student_name,
       d.dept_name
FROM student AS s
INNER JOIN department AS d ON d.dept_id = s.dept_id
ORDER BY student_name;

Students whose dept_id is NULL are excluded because there is no matching department row.

LEFT JOIN

A LEFT JOIN returns every row from the left table and matches from the right table when they exist. This is perfect for finding undeclared students.

All students, flagging undeclared majors

SELECT s.student_id,
       s.first_name || ' ' || s.last_name AS student_name,
       d.dept_name
FROM student AS s
LEFT JOIN department AS d ON d.dept_id = s.dept_id
ORDER BY student_name;

Students without a department show NULL in dept_name, making them easy to identify.

RIGHT and FULL JOIN (Conceptual)

SQLite (and many lightweight engines) do not implement RIGHT JOIN or FULL JOIN, but it is still important to understand them:

• RIGHT JOIN: returns every row from the right table plus matches from the left. You can usually rewrite this as a LEFT JOIN by swapping table order.
• FULL JOIN: returns every row from both tables, matching where possible and showing NULL when no match exists. You can emulate this by combining a LEFT JOIN and a RIGHT JOIN with a UNION.

Joining More Than Two Tables

Most questions touch several tables. For example, to see which students are taking which courses—and which department owns each course—you need three joins.

Enrollment details across three tables

SELECT e.enroll_id,
       s.first_name || ' ' || s.last_name AS student_name,
       c.course_code,
       c.course_title,
       d.dept_name,
       e.semester,
       e.grade
FROM enrollment AS e
JOIN student     AS s ON s.student_id = e.student_id
JOIN course      AS c ON c.course_id  = e.course_id
JOIN department  AS d ON d.dept_id    = c.dept_id
ORDER BY e.semester, c.course_code, student_name;

You can chain as many joins as needed. Just add one join clause per relationship.

Joining with Instructors

Because each course references an instructor, you can extend the previous example to include faculty information.

Courses with instructor detail

SELECT c.course_code,
       c.course_title,
       d.dept_name,
       i.first_name || ' ' || i.last_name AS instructor_name
FROM course AS c
JOIN department AS d ON d.dept_id = c.dept_id
LEFT JOIN instructor AS i ON i.instructor_id = c.instructor_id
ORDER BY d.dept_name, c.course_code;

The LEFT JOIN ensures courses still appear even if an instructor has not been assigned yet.

Using Table Aliases

Aliases keep join queries readable and help you disambiguate similarly named columns.

Aliases clarify complex joins

SELECT s.student_id,
       s.first_name,
       s.last_name,
       c.course_code,
       e.semester
FROM student AS s
JOIN enrollment AS e ON e.student_id = s.student_id
JOIN course AS c ON c.course_id = e.course_id
WHERE e.semester = 'Spring 2025';

The alias (AS s, AS e, AS c) lets you refer to columns quickly without repeating full table names.

Handling Ambiguous Column Names

When two tables share a column name (for example, both student and instructor have first_name), qualify the column with its alias to avoid errors.

Fully qualify duplicate column names

SELECT i.instructor_id,
       i.first_name,
       i.last_name,
       d.dept_name
FROM instructor AS i
JOIN department AS d ON d.dept_id = i.dept_id
ORDER BY d.dept_name, i.last_name;

Common Pitfalls

• Missing join condition: Forgetting the ON clause produces a Cartesian product (every row matched with every other row).
• Wrong join type: Using INNER JOIN when you really need LEFT JOIN drops unmatched rows.
• Ambiguous columns: Always prefix duplicated column names with their table alias.
• Join order confusion: Sketch the relationships (or refer to the ER diagram) before writing complicated join chains.
• Null handling: Be aware of optional foreign keys such as student.dept_id and course.instructor_id.

Summary

• Use INNER JOIN when you only care about matching rows.
• Use LEFT JOIN to keep unmatched left-side records (great for spotting null foreign keys).
• Chain joins to pull together students, courses, departments, instructors, and enrollments.
• Rely on aliases and qualified column names for clarity and correctness.

Quiz

Show quiz

1. Which join ensures every student appears, even if they have not declared a department?
   A) INNER JOIN
   B) LEFT JOIN
   C) RIGHT JOIN
   D) FULL JOIN

2. What columns do you join on when connecting enrollment to student?
   A) enroll_id = student_id
   B) student_id in both tables
   C) course_id in both tables
   D) dept_id in both tables

3. Why is course joined to department in many examples?
   A) They share the same primary key
   B) Each course belongs to a department via dept_id
   C) Every department has exactly one course
   D) To filter out courses with NULL instructor assignments

4. True or False: You can rewrite any RIGHT JOIN as a LEFT JOIN by swapping table order.
   A) True
   B) False

---

Answers

1. B
2. B
3. B
4. A