Lesson 5 of 12 · 0 of 2 exercises solved

JOINs — Combining Tables

Real split data across several tables: students in one, courses in another, and enrollments linking them. combines related rows from those tables in one query.

Instead of one giant table repeating the course name on every student row, you store courses once and them by id. A JOIN matches rows from two tables using a condition — almost always 'foreign key equals '.

CREATE TABLE courses (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_id  INTEGER REFERENCES courses(id),
  score      REAL
);

-- Which student is enrolled in which course?
SELECT students.name, courses.name AS course, enrollments.score
FROM enrollments
JOIN students ON students.id = enrollments.student_id
JOIN courses  ON courses.id  = enrollments.course_id;

-- INNER JOIN (default): only rows with a match on both sides
SELECT s.name, e.score
FROM students s
JOIN enrollments e ON e.student_id = s.id;

-- LEFT JOIN: all rows from the left table,
-- NULLs where the right side has no match
SELECT s.name, e.score
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id;
-- Students with no enrollments still appear (score = NULL)

-- Find students NOT enrolled in anything:
SELECT s.name
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.student_id IS NULL;

Common question

Should I split data across tables at all?

A JOIN combines rows from two tables where a condition holds, almost always a foreign key in one matching a primary key in the other. It is what makes splitting data into tables practical: each thing lives in one place, and queries stitch the pieces back together as needed. INNER JOIN keeps matches; LEFT JOIN keeps everything on one side, and the NULLs it produces are how "no match" shows up.

Give tables short aliases, join on keys, and check the row count of a new join against what you expect. An unexpected jump in rows means the condition is wrong. When you need "rows with no match", think LEFT JOIN with IS , or NOT EXISTS.

Source: Codd's normal forms exist because repeated facts drift apart. The relational model stores each fact once and combines them when you ask.

Common mix-ups

Each of these sounds reasonable. Think about why it is wrong, then open the explanation.

One wide table with every fact is simpler than several joined tables.
A JOIN returns rows from both tables whether or not they match.
Joining on the wrong column gives an .

Practice

Produce one row per enrollment with the columns student, course and score, joining the three tables. Students who are not enrolled in anything must not appear.

Exercise

Three-table join

3 checks

SELECT ...
FROM enrollments e

Output

Press Run to try your code, then Check answer to grade it.

Find the students who are not enrolled in any course. Return just their names.

Exercise

The anti-join

3 checks

SELECT s.name
FROM students s

Output

Press Run to try your code, then Check answer to grade it.

Check yourself

Quick check

A LEFT JOIN between students and enrollments returns…

Finished reading?2 exercises still open. You can come back to them later.

Tip: use the ← and → keys to move between lessons.