Joining tables · Lesson 12 of 20

Subqueries and CTEs

A query inside a query, and WITH to break a problem into steps.

11 minutes2 games

Sometimes you need a number before you can ask: "who's above average?" The average itself is a query. A subquery is a query in brackets inside another, and it runs first.

SQL

SELECT name, xp
FROM students
WHERE xp > (SELECT AVG(xp) FROM students)
ORDER BY xp DESC;

A subquery can return a list too, used with IN. Or use EXISTS, which asks "is there at least one row?":

SQL

-- Students taking at least one web course
SELECT name
FROM students s
WHERE EXISTS (
  SELECT 1
  FROM enrollments e
  JOIN courses c ON c.id = e.course_id
  WHERE e.student_id = s.id AND c.track = 'web'
);

WITH (CTE): one step at a time

Nested subqueries get hard to read. WITH lets you name an intermediate result (like a variable) and use it afterwards. That's a Common Table Expression.

SQL

WITH per_student AS (
  SELECT student_id,
         COUNT(*) AS courses,
         AVG(progress) AS avg_progress
  FROM enrollments
  GROUP BY student_id
)
SELECT s.name, p.courses, ROUND(p.avg_progress) AS avg_progress
FROM per_student p
JOIN students s ON s.id = p.student_id
WHERE p.courses >= 2
ORDER BY p.avg_progress DESC;

WITH per_student: one row per student with course count and average progress

JOIN students for the names

WHERE and ORDER BY on the ready result

What's the output?

SELECT COUNT(*) AS above_avg
FROM students
WHERE xp > (SELECT AVG(xp) FROM students);

What will this code print?

Exercise

Using WITH: compute each student's average progress, then get the name of students whose average is above 60, alphabetically.

Expected result:

name
Karim
Laila
Nour

SQL

WITH avg_progress AS (
  SELECT student_id, AVG(progress) AS avg
  FROM enrollments
  GROUP BY student_id
)
SELECT

Learn more from

Played 0 of 2 games

SQL