Self-Joins and Complex Joins

Join a table to itself, then use CTEs to make complex multi-table queries readable

The tv_db show_cast table links shows to cast members. Each cast member has a role and a billing order. This is a many-to-many relationship — one show has many cast members, one cast member appears in many shows.\n\nBut this lab also creates a demo employees table with a manager_id column that points back to the same employees table. To answer "who is each employee's manager?" you must join employees to itself using two aliases — one for the employee row, one for the manager row.\n\nFor complex joins involving many tables, CTEs (Common Table Expressions) let you name intermediate results and build the final query in readable, testable steps.

Self-Join: A Table Joined to Itself

A self-join is just a regular JOIN where both sides reference the same table — using different aliases. The classic use case is a hierarchy table where a row references another row in the same table.

-- Employees with their manager's name
SELECT emp.name  AS employee,
       mgr.name  AS manager
FROM   employees emp
LEFT JOIN employees mgr ON mgr.id = emp.manager_id
ORDER BY mgr.name NULLS LAST, emp.name;

The LEFT JOIN handles the CEO (Alice) gracefully: she has no manager_id, so mgr.name is NULL. INNER JOIN would exclude her entirely.

CTEs — Common Table Expressions

CTEs let you name intermediate results and reference them like temporary tables.

WITH czech_breweries AS (
    SELECT id, name, city
    FROM   breweries
    WHERE  country = 'Czech Republic'
),
czech_beers AS (
    SELECT b.name AS beer, b.style, b.abv, br.city
    FROM   beers b
    INNER JOIN czech_breweries br ON b.brewery_id = br.id
)
SELECT beer, style, abv, city
FROM   czech_beers
WHERE  abv > 6.0
ORDER BY abv DESC;

Each CTE is defined once and can be referenced multiple times below it. CTEs make long queries readable and testable — you can run each named block independently.

Complex Join: Shows → Cast Members → Roles

SELECT s.title        AS show,
       cm.full_name   AS cast_member,
       sc.role_name,
       sc.billing_order
FROM   shows s
INNER JOIN show_cast sc    ON sc.show_id = s.id
INNER JOIN cast_members cm ON cm.id = sc.cast_member_id
WHERE  s.status = 'ongoing'
ORDER BY s.title, sc.billing_order;

Self-join

A JOIN where both sides reference the same physical table, distinguished by two different aliases. Self-joins resolve recursive relationships — rows that point to other rows in the same table, such as employees with manager_id, categories with parent_category_id, or product variants with parent_product_id. Without two aliases, PostgreSQL cannot distinguish which side of the join is which.

Common Table Expression (CTE)

A named subquery defined with WITH … AS (…) before the main SELECT. CTEs are computed once (in most cases) and can be referenced by name in the main query. They improve readability by giving meaningful names to intermediate result sets, and they allow complex queries to be built and tested incrementally. Multiple CTEs are separated by commas; each can reference the ones defined before it.

🔌 Connect to tv_db

Connect to tv_db as the tv_db user. This lab uses the shows, show_cast, and cast_members tables, plus a demo employees table that was created in the setup step.

psql -U tv_db -d tv_db

psql (18.4) Type "help" for help. tv_db=>

🏢 Explore the Employees Table

Look at the employees table structure. Notice the manager_id column — it references the id column of the same table. Alice (id=1) has NULL for manager_id — she is the CEO.

SELECT id, name, department, manager_id FROM employees ORDER BY id;

🔗 Self-Join: Employee with Manager Name

Write a self-join: join employees to itself to show each employee's name alongside their manager's name. Use aliases emp for the employee side and mgr for the manager side. Use LEFT JOIN so Alice (the CEO) also appears, with NULL as her manager.

SELECT emp.name AS employee, emp.department, mgr.name AS manager FROM employees emp LEFT JOIN employees mgr ON mgr.id = emp.manager_id ORDER BY mgr.name NULLS LAST, emp.name;

🌳 Three-Level Hierarchy

Extend the self-join to show three levels: employee, their direct manager, and their manager's manager (grandmanager). This requires two self-joins in sequence.

SELECT emp.name AS employee, mgr.name AS manager, mgr2.name AS grandmanager FROM employees emp LEFT JOIN employees mgr ON mgr.id = emp.manager_id LEFT JOIN employees mgr2 ON mgr2.id = mgr.manager_id ORDER BY mgr2.name NULLS LAST, mgr.name NULLS LAST, emp.name;

🎬 Complex Join: Shows with Lead Actors

Now in tv_db proper: join shows → show_cast → cast_members to list every ongoing show with the name of its Lead Actor (billing_order = 1). This is a 3-table join — practice chaining multiple JOINs.

SELECT s.title, cm.full_name AS lead_actor, cm.nationality FROM shows s INNER JOIN show_cast sc ON sc.show_id = s.id INNER JOIN cast_members cm ON cm.id = sc.cast_member_id WHERE s.status = 'ongoing' AND sc.role_name = 'Lead Actor' ORDER BY s.title;

📦 Refactor with a CTE

Rewrite the previous query using a CTE for readability. Define a CTE called ongoing_shows that selects just id and title for ongoing shows, then join it to show_cast and cast_members in the main SELECT.

WITH ongoing_shows AS (SELECT id, title FROM shows WHERE status = 'ongoing') SELECT os.title, cm.full_name AS lead_actor, cm.nationality FROM ongoing_shows os INNER JOIN show_cast sc ON sc.show_id = os.id INNER JOIN cast_members cm ON cm.id = sc.cast_member_id WHERE sc.role_name = 'Lead Actor' ORDER BY os.title;

Lab complete! You can now handle hierarchies and complex multi-table queries:\n\n\n Self-join (two aliases) : ✅ parent-child in one table\n LEFT JOIN self-join : ✅ CEO handled gracefully\n 3-level hierarchy : ✅ three aliases, two JOINs\n 3-table JOIN chain : ✅ shows → cast → members\n CTE refactor : ✅ named intermediate steps\n

Enable JavaScript to run the live terminal and track your progress.