Reading 2: Joining Relations

When One Relation Is Not Enough

In Reading 1, every query we wrote looked at a single relation — either STUDENT, COURSE, or ENROLLMENT. Those queries answered questions that lived entirely within one table. But many of the most useful questions in a real database span multiple relations.

Consider Query D from the Reading 1 Now You Try:

SELECT  StudentID
FROM    Enrollment
WHERE   CourseID = 'ENG401'
        AND Semester = 'Fall'

This returns a list of StudentIDs — numbers like 10042 and 10078 — but not the actual student names. The names live in the STUDENT relation, not in ENROLLMENT. To answer the genuinely useful question — What are the names of students enrolled in American Literature this fall? — we need to combine information from two relations simultaneously.

This is exactly what the JOIN operation does. And it is precisely why, back in Topic 5b, we designed the database with shared identifiers (StudentID, CourseID) linking the relations together. Those identifiers were always intended to be the bridge between tables. JOIN is how we cross that bridge.

How JOIN Works

A JOIN combines two relations into one temporary combined relation by matching tuples that share a common value in a specified attribute. The most common case — and the one we will focus on — is joining on a shared identifier.

Here is the SQL pattern for a JOIN:

SELECT  <attributes you want to see>
FROM    <first relation>, <second relation>
WHERE   <first relation>.<shared attribute> = <second relation>.<shared attribute>

The key additions compared to a single-relation query are:

• The FROM clause now lists two relations, separated by a comma.
• The WHERE clause includes a condition that specifies how the two relations are connected — which attribute they share.
• When the same attribute name appears in both relations (like StudentID), we use dot notation — Student.StudentID and Enrollment.StudentID — to make clear which relation we mean.

Let's look at this in action.

Example 1: Names Instead of IDs

Here is the query that solves the problem we identified above — getting student names instead of just IDs from an enrollment query:

SELECT  Student.LastName, Student.FirstName
FROM    Student, Enrollment
WHERE   Student.StudentID = Enrollment.StudentID
        AND Enrollment.CourseID = 'ENG401'
        AND Enrollment.Semester = 'Fall'

Let's read this using our FROM → WHERE → SELECT strategy:

• FROM Student, Enrollment — we are looking in both the STUDENT and ENROLLMENT relations.
• WHERE has three conditions:
  • Student.StudentID = Enrollment.StudentID — this is the JOIN condition, linking the two relations by their shared identifier.
  • Enrollment.CourseID = 'ENG401' — we only want Fall enrollment records for American Literature.
  • Enrollment.Semester = 'Fall' — further filtering to the fall semester.
• SELECT Student.LastName, Student.FirstName — we want the actual names from the STUDENT relation.

Plain-English question: What are the names of students enrolled in American Literature (ENG401) in the Fall semester?

From our sample data, this returns two rows: Adeyemi, Priya and Hernandez, Marcus.

Example 2: Course Details for a Student

JOIN works in any direction. Here we use it to look up course details for a specific student, joining ENROLLMENT with COURSE instead:

SELECT  Course.CourseName, Course.Department
FROM    Enrollment, Course
WHERE   Enrollment.CourseID = Course.CourseID
        AND Enrollment.StudentID = 10091

Reading it:

• FROM Enrollment, Course — looking in ENROLLMENT and COURSE.
• WHERE: the JOIN condition links the two relations via CourseID; the second condition filters to one specific student (10091, Sofia Lindqvist).
• SELECT: we want the course name and department from the COURSE relation.

Plain-English question: What courses (names and departments) is student 10091 enrolled in?

From our sample data: Earth Science, Science.

Example 3: A Question That Needs All Three Relations

Some questions require information from all three relations. This query finds the names of eleventh-grade students and the courses they are enrolled in:

SELECT  Student.LastName, Student.FirstName, Course.CourseName
FROM    Student, Enrollment, Course
WHERE   Student.StudentID = Enrollment.StudentID
        AND Enrollment.CourseID = Course.CourseID
        AND Student.Grade = 11

Reading it:

• FROM: all three relations.
• WHERE: two JOIN conditions (Student linked to Enrollment via StudentID; Enrollment linked to Course via CourseID) plus one filter (Grade = 11).
• SELECT: student names from STUDENT, course name from COURSE.

Plain-English question: What are the names of all eleventh-grade students and the courses they are enrolled in?

From our sample data: Lindqvist Sofia — Earth Science; Washington DeShawn — Studio Art.

Notice how the two JOIN conditions in the WHERE clause form a chain: Student → Enrollment → Course. Each link in the chain connects two relations via their shared identifier. This chain pattern is how multi-relation queries are typically structured.

Reading a JOIN Query: What to Look For

When you encounter a SQL query with multiple relations in the FROM clause, here is a reliable approach for figuring out what it does:

1. Identify the relations in FROM. These tell you which tables are being combined.
2. Find the JOIN conditions in WHERE. These are the conditions that use dot notation to link attributes across relations (e.g., Student.StudentID = Enrollment.StudentID). They tell you how the relations are connected.
3. Find the filter conditions in WHERE. These are the conditions that restrict which rows qualify (e.g., Grade = 11). They tell you the criteria for the search.
4. Read the SELECT clause. This tells you what the answer looks like — which attributes from which relations appear in the result.
5. Assemble the plain-English question from what you found in steps 1-4.

With practice, this process becomes quick and intuitive. The Now You Try below gives you that practice.

Now You Try

For each SQL query below, write a plain-English question that the query answers. Use the STUDENT, COURSE, and ENROLLMENT relations from Reading 1. Suggested answers are in the answer key.

Query A:

SELECT  Student.LastName, Student.FirstName
FROM    Student, Enrollment
WHERE   Student.StudentID = Enrollment.StudentID
        AND Enrollment.CourseID = 'MTH302'

Query B:

SELECT  Course.CourseName, Enrollment.Grade
FROM    Course, Enrollment
WHERE   Course.CourseID = Enrollment.CourseID
        AND Enrollment.StudentID = 10042

Query C:

SELECT  Student.LastName, Student.FirstName
FROM    Student, Enrollment, Course
WHERE   Student.StudentID = Enrollment.StudentID
        AND Enrollment.CourseID = Course.CourseID
        AND Course.Department = 'Fine Arts'
        AND Student.Grade = 11