WITH 2 Tables JOIN

A WITH clause lets you name filtered sub-results and then join them as if they were tables. This page shows three versions of the same task: find students in AI-related majors, keep those with graduated curriculum records, attach their grades and course details, and return a random sample. The sections after the queries explain how the joins are evaluated, how CTEs compare to temporary tables and sub-queries, and the mistakes that most often produce duplicated or missing rows.

1. Materialise a filtered temporary table

The first pattern writes the filtered students to a physical table. It is useful when the same filtered set is reused by several later statements in a script, or when you want to inspect the intermediate rows while debugging.

DROP TABLE IF EXISTS tmp_Filter_Students;

-- ---------------------------------------------------------------------------- --
CREATE OR REPLACE TABLE tmp_Filter_Students AS
SELECT 
    student_id, first_name, last_name, email, major 
FROM students 
WHERE 
       LOWER(major) LIKE LOWER('%Artificial Intelligence%') 
    OR LOWER(major) LIKE LOWER('%Large Language Model%');

2. Two CTEs joined on student_id

The second pattern expresses both filters as common table expressions (CTEs) inside one statement and joins them. Nothing is written to disk; the CTEs are named sub-queries that exist only for the duration of the query. Note that this version filters on subject and body columns and selects every column, while the next version uses explicit columns.

WITH 
-- ---------------------------------------------------------------------------- --
Filter_Students AS (
    SELECT * FROM students
    WHERE LOWER(subject) LIKE LOWER('%Artificial Intelligence%')
       OR LOWER(subject) LIKE LOWER('%Large Language Model%')
),
-- ---------------------------------------------------------------------------- --
Filter_Curriculum AS (
    SELECT * FROM curriculum
    WHERE LOWER(body) LIKE LOWER('%Graduated%')
)
-- ---------------------------------------------------------------------------- --
SELECT *
FROM Filter_Students 
JOIN Filter_Curriculum ON Filter_Students.student_id = Filter_Curriculum.student_id

ORDER BY RAND() LIMIT 100;
-- ---------------------------------------------------------------------------- --

3. Four CTEs feeding a final join

The third pattern scales the idea up: each source table gets its own CTE that projects only the needed columns and applies its own filter, then a Joined_Results CTE joins all four with table aliases, and the outer query samples 100 rows.

WITH 
-- ---------------------------------------------------------------------------- --
Filter_Students AS (
    SELECT student_id, first_name, last_name, email, major 
    FROM students 
    WHERE 
           LOWER(major) LIKE LOWER('%Artificial Intelligence%') 
        OR LOWER(major) LIKE LOWER('%Large Language Model%')
),
-- ---------------------------------------------------------------------------- --
Filter_Curriculum AS (
    SELECT curriculum_id, student_id, curriculum_name, program_type 
    FROM curriculum 
    WHERE 
        LOWER(program_type) LIKE LOWER('%Graduated%')
),
-- ---------------------------------------------------------------------------- --
Grades_Table AS (
    SELECT student_id, course_id, grade, semester 
    FROM grades 
    WHERE grade IS NOT NULL
),
-- ---------------------------------------------------------------------------- --
Courses_Table AS (
    SELECT course_id, course_name, instructor 
    FROM courses
),
-- ---------------------------------------------------------------------------- --
Joined_Results AS (
    SELECT s.student_id, s.first_name, s.last_name, s.email, 
           c.curriculum_name, 
           g.grade, g.semester, 
           cr.course_name, cr.instructor

    FROM Filter_Students   AS s
    JOIN Filter_Curriculum AS c  ON s.student_id = c.student_id
    JOIN Grades_Table      AS g  ON s.student_id = g.student_id
    JOIN Courses_Table     AS cr ON g.course_id  = cr.course_id
)
-- ---------------------------------------------------------------------------- --
SELECT * 
FROM Joined_Results
ORDER BY RAND() LIMIT 100;
-- ---------------------------------------------------------------------------- --

4. How the CTE join flows

Data flow of the four-CTE query students filter: major LIKE curriculum filter: Graduated grades grade IS NOT NULL courses projection only Joined_Results s JOIN c JOIN g JOIN cr ORDER BY RAND() LIMIT 100 random sample

Each CTE reads one base table, removes rows it does not need, and keeps only the columns used later. The join CTE then matches rows on keys: students to curriculum and grades by student_id, grades to courses by course_id. Logically, filtering happens before joining. Physically, most optimisers (PostgreSQL 12+, MySQL 8, Snowflake, Spark SQL, BigQuery) inline non-recursive CTEs and push filters down to the table scans anyway, so a CTE is primarily a readability tool. Some engines can materialise a CTE that is referenced more than once, and PostgreSQL lets you force either behaviour with AS MATERIALIZED or AS NOT MATERIALIZED.

5. Join cardinality

Row counts multiply across one-to-many joins 1 student student_id = 7 x 2 curriculum rows two programs x 5 grade rows five courses = 10 joined rows student repeated 10 times

An inner join returns one output row for every matching pair. Because one student can have several curriculum records and several grades, joining both to the student multiplies them: two curricula times five grades gives ten rows for that single student, and each grade appears twice. This is the most common reason a CTE query "returns too many rows". If you only need one row per student, aggregate the many side first (for example a Grade_Summary CTE with GROUP BY student_id) or use EXISTS for pure filtering.

6. Worked example: filter with EXISTS, aggregate before joining

WITH ai_students AS (
    SELECT student_id, first_name, last_name, email
    FROM students
    WHERE LOWER(major) LIKE '%artificial intelligence%'
       OR LOWER(major) LIKE '%large language model%'
),
grade_summary AS (
    SELECT student_id,
           COUNT(*)   AS courses_taken,
           AVG(grade) AS avg_grade
    FROM grades
    WHERE grade IS NOT NULL
    GROUP BY student_id
)
SELECT s.student_id, s.first_name, s.last_name,
       g.courses_taken, g.avg_grade
FROM ai_students s
JOIN grade_summary g ON g.student_id = s.student_id
WHERE EXISTS (
    SELECT 1 FROM curriculum c
    WHERE c.student_id = s.student_id
      AND LOWER(c.program_type) LIKE '%graduated%'
)
ORDER BY g.avg_grade DESC;

This returns exactly one row per qualifying student. The curriculum condition becomes a semi-join through EXISTS, so multiple curriculum rows cannot duplicate output, and grades are pre-aggregated so the join is one-to-one. AVG assumes a numeric grade column; for letter grades, map them to points first.

7. CTE, temporary table or sub-query

OptionLifetimeBest forWatch out for
CTE (WITH)One statementReadable multi-step logic, recursionMay be recomputed if referenced twice
Temporary tableSessionReusing a result across statements, adding indexesExtra write; stale if sources change
Inline sub-queryOne statementSmall one-off filtersHard to read when nested deeply
ViewPermanentShared, reusable definitionsSchema object to manage and grant

8. Pitfalls