Subqueries & Correlated Subqueries
Nest queries inside queries, use EXISTS for existence checks, and write correlated subqueries that reference the outer row.
- subquery
- EXISTS
- NOT EXISTS
- correlated
- IN
Use this when you need to:
- EXISTS / NOT EXISTS — find records that have or don't have a related row (customers who never bought, students without enrollments)
- Compare each row to a global aggregate (anyone above the average?) with a scalar subquery
- Filter by a dynamic list of IDs from another query — `WHERE id IN (SELECT ...)`
- Correlated subqueries — for each outer row, look up something tied to that row
A subquery is a SELECT statement nested inside another query. There are two kinds: non-correlated (runs once, its result is handed to the outer query) and correlated (references a column from the outer query, so PostgreSQL re-evaluates it once per outer row).
Non-Correlated Subquery
-- The inner query runs once. Its result is a list used by the outer WHERE.
-- Students whose program sits in a department of the School of Arts & Sciences
SELECT p.first_name, p.last_name, sal.stpr_dept
FROM ods_stu_acad_levels sal
LEFT JOIN ods_person p ON p.id = sal.students_id
WHERE sal.stpr_dept IN (
SELECT departments_id FROM ods_departments WHERE dept_school = 'SAS'
);Correlated Subquery
A correlated subquery references a column from the outer query — note sal.stpr_dept below. PostgreSQL evaluates it freshly for every row of the outer query.
-- Students whose GPA is above the average for THEIR OWN department
SELECT p.first_name, p.last_name, sal.stpr_dept, sal.stpr_gpa
FROM ods_stu_acad_levels sal
LEFT JOIN ods_person p ON p.id = sal.students_id
WHERE sal.stpr_gpa > (
SELECT AVG(sal2.stpr_gpa)
FROM ods_stu_acad_levels sal2
WHERE sal2.stpr_dept = sal.stpr_dept -- references the outer row
);EXISTS and NOT EXISTS
EXISTS returns true as soon as the subquery finds one matching row — it does not scan the rest. This makes it efficient for existence checks. NOT EXISTS is the opposite.
-- Students registered for at least one course in Fall 2026
SELECT p.first_name, p.last_name
FROM ods_students s
LEFT JOIN ods_person p ON p.id = s.students_id
WHERE EXISTS (
SELECT 1
FROM ods_stu_course_sec scs
WHERE scs.scs_student = s.students_id
AND scs.scs_term = '2026FA'
);
-- Students with NO course registrations in Fall 2026
SELECT p.first_name, p.last_name
FROM ods_students s
LEFT JOIN ods_person p ON p.id = s.students_id
WHERE NOT EXISTS (
SELECT 1
FROM ods_stu_course_sec scs
WHERE scs.scs_student = s.students_id
AND scs.scs_term = '2026FA'
);For large tables, EXISTS is usually faster than IN (subquery) because it short-circuits on the first match. Correlated subqueries always run as nested loops — rewrite as a JOIN or CTE when performance matters.
Subquery in FROM (Derived Table)
-- Calculate per-student credit totals first, then filter
SELECT scs_student, term_credits
FROM (
SELECT scs_student, SUM(scs_credits) AS term_credits
FROM ods_stu_course_sec
WHERE scs_term = '2026SP'
GROUP BY scs_student
) credit_totals
WHERE term_credits > 15;Find all students registered in more course sections than the average number of sections per student in the 2026SP term. Return the student id, first_name, last_name (names are on ods_person) and section_count. Sort by section_count DESC.
Solution
SELECT
p.id AS student_id,
p.first_name,
p.last_name,
COUNT(scs.stu_course_sec_id) AS section_count
FROM ods_stu_course_sec scs
LEFT JOIN ods_person p ON p.id = scs.scs_student
WHERE scs.scs_term = '2026SP'
AND scs.scs_status = 'A'
GROUP BY p.id, p.first_name, p.last_name
HAVING COUNT(scs.stu_course_sec_id) > (
SELECT AVG(cnt) FROM (
SELECT COUNT(*) AS cnt
FROM ods_stu_course_sec scs2
WHERE scs2.scs_term = '2026SP'
AND scs2.scs_status = 'A'
GROUP BY scs2.scs_student
) sub
)
ORDER BY section_count DESC, p.last_name;Practise this on real university data
Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.