SovranCode
SQL: Query, Model, and Analyze Data Self joins and many-to-many data
This device
Course contentsSelf joins and many-to-many data · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONINNER, LEFT, RIGHT, and FULL joins
NEXT LESSONSubqueries and correlated subqueries
Combining related data · Lesson 14 145 min

Self joins and many-to-many data

Some relationships point back to the same kind of record; others connect two independent lists. Self joins make a hierarchy readable, while a bridge table makes every many-to-many connection explicit, queryable, and safe.

What you will leave with

You will write an employee-to-manager self join, choose the preserved side of a hierarchy, model enrollment-style relationships with a bridge table, and count relationship rows without losing zero-activity records.

First, recognize the relationship

Do not start with SQL syntax. Start by asking what each row can be connected to. If a row points to another row in the same table, you have a self-reference. If a record on each side can connect to many records on the other side, you have a many-to-many relationship and need a bridge table.

SELF REFERENCE

Employee → manager

The manager_id stored on an employee points back to an employee_id in the same table.

MANY TO MANY

Learner ↔ course

A learner can take several courses, and each course can have several learners. Neither side can hold one simple foreign key for all connections.

A useful rule of thumb

If you need a sentence like “this person has several of those,” make the connection a row of its own. That row can later carry the details that describe the connection: when it started, who created it, its status, or a permission level.

A self join gives one table two roles

A self join is not a special SQL keyword. It is an ordinary join where the same table appears twice with different aliases. In an employee hierarchy, one alias means “the employee we are describing” and the other means “that employee's manager.” The two aliases let SQL—and the reader—keep those roles separate.

ROLE ONEemployeeemployee.manager_id
references
ROLE TWOmanagermanager.employee_id
START WITH

The row you want to list

Start from employees AS employee when every employee belongs in the report.

JOIN BACK

The related role

Join employees AS manager through the self-referencing manager ID.

Read the first query in three steps

FROM employees AS employee chooses the people you want to list. LEFT JOIN employees AS manager brings in a second role from that same table without removing Amina, who has no manager. Finally, manager.employee_id = employee.manager_id states the relationship: the manager's ID must equal the employee's stored manager ID.

SQL BROWSER RUNNER

List every employee and their manager

Use two aliases for the same employees table.

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  title TEXT NOT NULL,
  manager_id INTEGER
);
INSERT INTO employees VALUES
  (1, 'Amina', 'Director', NULL),
  (2, 'Bilal', 'Engineering manager', 1),
  (3, 'Celia', 'Developer', 2),
  (4, 'Dina', 'Developer', 2),
  (5, 'Elias', 'Designer', 1);

SELECT employee.employee_name AS employee, employee.title,
       manager.employee_name AS manager
FROM employees AS employee
LEFT JOIN employees AS manager ON manager.employee_id = employee.manager_id
ORDER BY employee.employee_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Find Amina. Why is her manager NULL, and why is LEFT JOIN the right choice?

Read aliases as role names

Choose aliases that describe the relationship, not just short letters. employee, manager, and report make a hierarchy query far easier to review than two unexplained copies of e.

Use the relationship in either direction

The same stored relationship can answer two questions. Starting from an employee and joining to a manager answers “who does each person report to?” Starting from a manager and joining to reports answers “who is on this manager's team?” The data did not change; the query's starting population did.

For example, Bilal's manager_id is 1, so the manager alias finds Amina's row, whose employee_id is also 1. Celia's manager_id is 2, so the same condition finds Bilal. The join does not “look up a name”; it compares stable IDs, then selects the human-readable name from the matched row.

SQL BROWSER RUNNER

Find Bilal's direct reports

Reverse the role you start from to ask a manager-focused question.

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  title TEXT NOT NULL,
  manager_id INTEGER
);
INSERT INTO employees VALUES
  (1, 'Amina', 'Director', NULL),
  (2, 'Bilal', 'Engineering manager', 1),
  (3, 'Celia', 'Developer', 2),
  (4, 'Dina', 'Developer', 2),
  (5, 'Elias', 'Designer', 1);

SELECT report.employee_name, report.title
FROM employees AS report
JOIN employees AS manager ON manager.employee_id = report.manager_id
WHERE manager.employee_name = 'Bilal'
ORDER BY report.employee_name;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the manager name to Amina. Predict the direct reports before running the query.

SQL BROWSER RUNNER

Read a two-level reporting chain

Join the employees table three times to show employee, manager, and director.

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  title TEXT NOT NULL,
  manager_id INTEGER
);
INSERT INTO employees VALUES
  (1, 'Amina', 'Director', NULL),
  (2, 'Bilal', 'Engineering manager', 1),
  (3, 'Celia', 'Developer', 2),
  (4, 'Dina', 'Developer', 2),
  (5, 'Elias', 'Designer', 1);

SELECT employee.employee_name AS employee, manager.employee_name AS manager,
       director.employee_name AS director
FROM employees AS employee
LEFT JOIN employees AS manager ON manager.employee_id = employee.manager_id
LEFT JOIN employees AS director ON director.employee_id = manager.manager_id
ORDER BY employee.employee_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Identify which rows have no director. What does each NULL mean at this level?

A bridge table turns many-to-many into two one-to-many relationships

A learner can enroll in many courses, and a course can have many learners. Do not store a comma-separated course list on the learner or duplicate learner data inside each course. Instead, create a third table where one row means one enrollment. That bridge row connects exactly one learner to exactly one course.

Why a list inside one column fails

A value like "10, 11, 12" looks convenient, but the database cannot protect each relationship with a foreign key, index it efficiently, or attach facts such as an enrollment date to one course. With a bridge table, Amina's SQL Foundations enrollment and Amina's PostgreSQL enrollment are two separate, inspectable facts.

learnerslearner_idOne learner can have many enrollment rows.
enrollmentslearner_id + course_idThe relationship itself can hold a date, progress, role, price, or status.
coursescourse_idOne course can have many enrollment rows.
SQL BROWSER RUNNER

Read learner-course relationships

Travel from the bridge table to both entities to produce one row per enrollment.

CREATE TABLE learners (learner_id INTEGER PRIMARY KEY, learner_name TEXT NOT NULL);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, course_title TEXT NOT NULL);
CREATE TABLE enrollments (
  enrollment_id INTEGER PRIMARY KEY,
  learner_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TEXT NOT NULL,
  progress_percent INTEGER NOT NULL
);
INSERT INTO learners VALUES (1, 'Amina'), (2, 'Bilal'), (3, 'Celia'), (4, 'Dina');
INSERT INTO courses VALUES (10, 'SQL Foundations'), (11, 'PostgreSQL'), (12, 'Data Modeling');
INSERT INTO enrollments VALUES
  (101, 1, 10, '2026-01-05', 100), (102, 1, 11, '2026-02-01', 40),
  (103, 2, 10, '2026-01-12', 80), (104, 3, 12, '2026-03-03', 20);

SELECT learner.learner_name, course.course_title, enrollment.enrolled_at, enrollment.progress_percent
FROM enrollments AS enrollment
JOIN learners AS learner ON learner.learner_id = enrollment.learner_id
JOIN courses AS course ON course.course_id = enrollment.course_id
ORDER BY learner.learner_id, course.course_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What does one output row represent? Identify the learner who appears twice and explain why.

SQL BROWSER RUNNER

Keep courses with no learners

Start at courses and use LEFT JOIN to preserve the course catalog.

CREATE TABLE learners (learner_id INTEGER PRIMARY KEY, learner_name TEXT NOT NULL);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, course_title TEXT NOT NULL);
CREATE TABLE enrollments (
  enrollment_id INTEGER PRIMARY KEY,
  learner_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TEXT NOT NULL,
  progress_percent INTEGER NOT NULL
);
INSERT INTO learners VALUES (1, 'Amina'), (2, 'Bilal'), (3, 'Celia'), (4, 'Dina');
INSERT INTO courses VALUES (10, 'SQL Foundations'), (11, 'PostgreSQL'), (12, 'Data Modeling');
INSERT INTO enrollments VALUES
  (101, 1, 10, '2026-01-05', 100), (102, 1, 11, '2026-02-01', 40),
  (103, 2, 10, '2026-01-12', 80), (104, 3, 12, '2026-03-03', 20);

SELECT course.course_title, learner.learner_name, enrollment.progress_percent
FROM courses AS course
LEFT JOIN enrollments AS enrollment ON enrollment.course_id = course.course_id
LEFT JOIN learners AS learner ON learner.learner_id = enrollment.learner_id
ORDER BY course.course_id, learner.learner_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add a new course INSERT with no enrollment, then verify it still appears with NULL learner fields.

The bridge row is a fact, not plumbing

The enrollment date and progress percentage belong to the relationship, not to a learner alone or a course alone. This is why bridge tables are useful beyond course enrollment: product tags, users in teams, actors in films, and permissions assigned to users all need a row that describes the connection itself.

Before you write a query, say its result grain out loud. The learner-course query below is “one row per enrollment,” so Amina appears twice and that is correct. The count query is “one row per learner,” so it groups Amina's two bridge rows back into one summary row. Naming that difference prevents accidental duplicates and inflated totals.

SQL BROWSER RUNNER

Count each learner's courses correctly

Keep every learner while counting only real bridge rows.

CREATE TABLE learners (learner_id INTEGER PRIMARY KEY, learner_name TEXT NOT NULL);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, course_title TEXT NOT NULL);
CREATE TABLE enrollments (
  enrollment_id INTEGER PRIMARY KEY,
  learner_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TEXT NOT NULL,
  progress_percent INTEGER NOT NULL
);
INSERT INTO learners VALUES (1, 'Amina'), (2, 'Bilal'), (3, 'Celia'), (4, 'Dina');
INSERT INTO courses VALUES (10, 'SQL Foundations'), (11, 'PostgreSQL'), (12, 'Data Modeling');
INSERT INTO enrollments VALUES
  (101, 1, 10, '2026-01-05', 100), (102, 1, 11, '2026-02-01', 40),
  (103, 2, 10, '2026-01-12', 80), (104, 3, 12, '2026-03-03', 20);

SELECT learner.learner_name, COUNT(enrollment.course_id) AS course_count
FROM learners AS learner
LEFT JOIN enrollments AS enrollment ON enrollment.learner_id = learner.learner_id
GROUP BY learner.learner_id, learner.learner_name
ORDER BY course_count DESC, learner.learner_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why does Dina have zero instead of one? Compare COUNT(enrollment.course_id) with COUNT(*).

Protect the relationship from duplicates

In a production schema, foreign keys ensure that every enrollment points to a real learner and course. A composite UNIQUE (learner_id, course_id) constraint prevents the same learner-course relationship from being stored twice. If repeat enrollment is meaningful, model that business rule deliberately—for example with enrollment attempts or terms—rather than allowing accidental duplicates.

Think of the constraints as separate promises. The learner foreign key says “this learner exists.” The course foreign key says “this course exists.” The composite unique rule says “this exact pair has not already been recorded.” Together, they keep invalid or repeated relationship facts out of the database instead of asking every application screen to remember the rules.

SQL BROWSER RUNNER

Audit duplicate learner-course pairs

Group the bridge table by the relationship keys to find accidental duplicates.

CREATE TABLE learners (learner_id INTEGER PRIMARY KEY, learner_name TEXT NOT NULL);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, course_title TEXT NOT NULL);
CREATE TABLE enrollments (
  enrollment_id INTEGER PRIMARY KEY,
  learner_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TEXT NOT NULL,
  progress_percent INTEGER NOT NULL
);
INSERT INTO learners VALUES (1, 'Amina'), (2, 'Bilal'), (3, 'Celia'), (4, 'Dina');
INSERT INTO courses VALUES (10, 'SQL Foundations'), (11, 'PostgreSQL'), (12, 'Data Modeling');
INSERT INTO enrollments VALUES
  (101, 1, 10, '2026-01-05', 100), (102, 1, 11, '2026-02-01', 40),
  (103, 2, 10, '2026-01-12', 80), (104, 3, 12, '2026-03-03', 20);

SELECT learner_id, course_id, COUNT(*) AS relationship_rows
FROM enrollments
GROUP BY learner_id, course_id
HAVING COUNT(*) > 1;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Insert a second row for learner 1 and course 10, then rerun the audit. What constraint would prevent it?

COMMA-SEPARATED IDS

Hidden relationships

They are hard to validate, index, and join. A bridge row keeps every connection queryable.

VAGUE ALIASES

Unclear self joins

Name aliases for roles so reviewers can trace which table copy supplies a value.

WRONG RESULT GRAIN

Surprising duplicates

A learner repeats once per enrollment. That is correct unless your report promises one row per learner.

MISSING CONSTRAINT

Duplicate facts

Use foreign keys and a composite unique rule to protect relationship rows at write time.

Independent lab: report each learner's enrollments

Build a report that keeps every learner and shows their course count plus latest enrollment date. Amina should have two courses and a latest date of 2026-02-01. Dina must remain with a count of zero and no date. Start from learners, left join the bridge table, count a bridge-table key, and group at learner grain.

SQL BROWSER RUNNER

Build a learner enrollment snapshot

Preserve learners with no enrollments while summarizing relationship rows.

CREATE TABLE learners (learner_id INTEGER PRIMARY KEY, learner_name TEXT NOT NULL);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, course_title TEXT NOT NULL);
CREATE TABLE enrollments (
  enrollment_id INTEGER PRIMARY KEY,
  learner_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TEXT NOT NULL,
  progress_percent INTEGER NOT NULL
);
INSERT INTO learners VALUES (1, 'Amina'), (2, 'Bilal'), (3, 'Celia'), (4, 'Dina');
INSERT INTO courses VALUES (10, 'SQL Foundations'), (11, 'PostgreSQL'), (12, 'Data Modeling');
INSERT INTO enrollments VALUES
  (101, 1, 10, '2026-01-05', 100), (102, 1, 11, '2026-02-01', 40),
  (103, 2, 10, '2026-01-12', 80), (104, 3, 12, '2026-03-03', 20);

-- Preserve every learner, count their courses, and show their latest enrollment date.
SELECT learner.learner_id, learner.learner_name,
       COUNT(enrollment.course_id) AS course_count,
       MAX(enrollment.enrolled_at) AS latest_enrollment
FROM learners AS learner
LEFT JOIN enrollments AS enrollment ON enrollment.learner_id = learner.learner_id
GROUP BY learner.learner_id, learner.learner_name
ORDER BY course_count DESC, learner.learner_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add course titles only after you have checked this learner-level report. Predict why joining courses does not add rows here.

Lab review criteria

The report begins with the population it promises—learners. COUNT(enrollment.course_id) ignores the NULL placeholder created for Dina. The grouping includes the learner key and name, so one result row represents one learner.

Lesson review

Use a self join when one row refers to another row in the same table, and give each copy a clear role name. Use a bridge table when each side can have many connections to the other. Treat each bridge row as a meaningful fact, protect it with keys and constraints, and always say what one query result row represents before aggregating.

  • I can use aliases to join an employee table to itself.
  • I can choose the starting side of a hierarchy report.
  • I can model a many-to-many relationship with a bridge table.
  • I can explain why relationship-specific fields belong on the bridge.
  • I can preserve zero-activity entities in a bridge-table report.
KNOWLEDGE CHECK

Check relationship reasoning

Answer all ten questions, then rerun the example that challenged your mental model.

01What is a self join?
02Why are aliases essential in a self join?
03In an employee hierarchy, what does manager_id usually reference?
04Which join keeps an employee who has no manager?
05What does a bridge table represent?
06How is many-to-many modeled relationally?
07What constraint prevents the same learner-course pair appearing twice?
08Where should enrollment-specific fields such as enrolled_at live?
09Why can a learner appear more than once after joining through enrollments?
10Which count reports courses per learner after a LEFT JOIN?
PREVIOUS LESSONINNER, LEFT, RIGHT, and FULL joins
NEXT LESSONSubqueries and correlated subqueries
ON THIS PAGESelf joins and many-to-many dataRecognize the relationshipSelf joinsHierarchy directionBridge tablesRelationship grainIntegrity rulesIndependent labLesson reviewKnowledge check
Course contents