SQSQL · Lesson 5 of 8

JOINs — Combining Tables

Real databases split data across many tables: students in one, courses in another, enrollments linking them. JOIN stitches them back together. This is the concept that makes databases 'relational'.

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

SQL

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;

SQL

-- 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;

◆ Note
The single-letter names (students s) are table aliases — pure convenience for shorter queries. Also note NULL checks use IS NULL / IS NOT NULL, never = NULL. NULL isn't equal to anything, not even itself.