Skip to content
  • Official
  • Intermediate
  • English
  • 11 steps · 2 quizzes

SQL Subqueries and CTEs: A Hands-On Lesson for Analysts

SQL subquery exercises you run as you learn: put a query inside a query, use IN, EXISTS and WITH (CTEs), and see the inner result feed the outer one.

  • Free
  • No account needed
  • Stop any time, pick up where you left off
Workshopy.io

WorkshopyOfficial

78 lessons · used by 2 teachers

For teachers

Teaching this? Run it live with your class.

  • Students join with a code — no accounts
  • See who’s done and who’s stuck, step by step
  • It’s your copy — change anything before class

Sign in to copy it to your workspace. It’s free.

Your path

11 steps · 2 quizzes · open any step, or read it here

  1. SQL subqueries and CTEs: a query inside a queryStart hereIn this lesson you practise SQL subqueries and CTEs on three small tables from a sales team: you put one query inside another, use what the inner query returns as a value, a list or a table, and name the steps of a long query with WITH.InteractiveHands-on
    Read this step

    In this lesson you practise SQL subqueries and CTEs on three small tables from a sales team: you put one query inside another, use what the inner query returns as a value, a list or a table, and name the steps of a long query with WITH. Every exercise runs here, and you check your answers as you go.

    Here is the whole idea in one picture. The part in brackets is a query of its own. It runs first, its result goes where the brackets were, and only then does the outer query run. Pick a question and watch the inner result travel into the outer query.

    Interactive · The inner query runs first and feeds the outer one — try it in the lesson

    In this lesson you'll

    • answer a question too big for one query by putting a query inside another
    • use an inner query as one value, as a list for IN, and as a table in FROM
    • write correlated subqueries with EXISTS, and avoid the NOT IN and NULL trap
    • name each step of a long query with WITH, and read any nested query from the inside out

    About 85 minutes, with a short check after the first four ideas, a playground, a practice set and a final quiz.

  2. A question too big for one queryYour manager asks: which orders are bigger than our average order? It sounds like one question, but it hides two: What is the average order? (one number for the whole table) · Which orders are above that number?InteractiveHands-on
    Read this step

    Your manager asks: which orders are bigger than our average order? It sounds like one question, but it hides two:

    1. What is the average order? (one number for the whole table)
    2. Which orders are above that number? (a filter, row by row)

    The first idea most people try is WHERE amount > AVG(amount). The database refuses it: WHERE looks at one row at a time, before any grouping, so there is no average yet when it runs. "aggregate functions are not allowed in WHERE" is the error.

    You could run the two questions as two queries: get 633, then type WHERE amount > 633. That works today and is wrong tomorrow, when new orders move the average. A subquery does both in one statement: write the first question in brackets where the number would go.

    Try it: press each way in turn. Watch the third one: the bracketed query runs first, returns 633, and that value lands in the outer WHERE.

    Interactive · From two queries to one: the subquery — try it in the lesson

    The subquery and the typed number give the same four orders today. Only the subquery stays right when the orders change.

    Check your work · Run way 2a. Paste the average order amount it returns. — check it in the lesson

    Takeaway: when a question needs a number you don't have yet, ask for that number in a subquery, in brackets, where the number goes.

  3. Scalar subquery: one value, anywhere a value goesA subquery that returns one row and one column is a scalar subquery: to the outer query it is just a number (or a text, or a date).InteractiveHands-on
    Read this step

    A subquery that returns one row and one column is a scalar subquery: to the outer query it is just a number (or a text, or a date). So it can go anywhere a value can go:

    • in WHERE: WHERE amount > (SELECT AVG(amount) FROM orders)
    • in SELECT, as a column: amount - (SELECT AVG(amount) FROM orders) AS diff
    • in a calculation: amount * 100.0 / (SELECT SUM(amount) FROM orders) is each order's share of all sales, in per cent

    It runs once, before the outer query, and the same value is used for every row.

    It must return one value. If it returns two rows the database stops with "more than one row returned by a subquery used as an expression": it can't compare one amount with two numbers. If it returns no rows, the value is NULL.

    Try it: run the presets, then do the three tasks. Each one needs a subquery, not a typed number.

    Interactive · Scalar subqueries on the orders table — try it in the lesson

    Takeaway: a scalar subquery is one value: it works in WHERE, in SELECT and in arithmetic, and it must never return more than one row.

  4. IN with a subquery: match against a listNow a question across two tables: which orders came from customers in Leeds? The orders table has no city; the customers table does. Split it: Which customers are in Leeds? SELECT customerid FROM customers WHERE city = 'Leeds' gives the list (1, 3).InteractiveHands-on
    Read this step

    Now a question across two tables: which orders came from customers in Leeds? The orders table has no city; the customers table does. Split it:

    1. Which customers are in Leeds? SELECT customer_id FROM customers WHERE city = 'Leeds' gives the list (1, 3).
    2. Which orders have a customer_id in that list? WHERE customer_id IN (…).

    IN (subquery) is IN (1, 3) with the list worked out for you. The inner query must return one column (the values to match), any number of rows. NOT IN keeps the rows whose value is not in the list.

    A subquery with IN never repeats a row of the outer table. Cobalt Inc ordered twice in March; "customers who ordered in March" with IN lists Cobalt once, where a JOIN would list it twice (once per matching order).

    Try it: watch the list (1, 3) form, then the orders table keep the matching rows. Then do the tasks.

    Interactive · IN and NOT IN with a subquery — try it in the lesson

    Takeaway: IN (subquery) matches each row against a list another query makes; the inner query returns one column.

  5. Check: values and listsFour questions on what you've seen so far.Quiz · 4 questions
    Read the questions

    Four questions on what you've seen so far.

    Question 1. In `SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders)`, what happens first?

    • The inner query runs once and returns one value, the average
    • The outer query runs, then the inner query filters its result
    • The inner query runs once for every order
    • Both run at the same time and are joined

    Question 2. A query stops with "more than one row returned by a subquery used as an expression". What went wrong?

    • The outer query returned too many rows
    • A subquery used as one value returned two or more rows
    • The subquery has no alias
    • IN can't take a subquery

    Question 3. `WHERE customer_id IN (SELECT …)`: what must the inner query return?

    • Exactly one row
    • Exactly one column, any number of rows
    • The same columns as the outer table
    • A number, never text

    Question 4. Why does `WHERE amount > AVG(amount)` fail?

    • AVG only works with GROUP BY
    • amount must be inside brackets
    • WHERE checks rows one at a time, before any average exists; aggregates aren't allowed there
    • It doesn't fail; it returns every order
  6. Reading a query inside outQueries nest more than one level. Which orders came from customers whose rep is on team North? Three tables, three questionsInteractiveHands-on
    Read this step

    Queries nest more than one level. Which orders came from customers whose rep is on team North? Three tables, three questions:

    SELECT order_id, amount
    FROM orders
    WHERE customer_id IN (
      SELECT customer_id
      FROM customers
      WHERE rep_id IN (
        SELECT rep_id
        FROM reps
        WHERE team = 'North'
      )
    );
    

    Read it the way the database runs it, from the inside out:

    1. Find the deepest brackets. Read that query alone: the reps on team North.
    2. Replace it in your head by its result, a short list. The next query out is now a simple one.
    3. Repeat until you reach the outer query.

    Write long queries the same way: start with the innermost question, run it alone, check its result, then wrap the next question around it. When a nested query gives a strange answer, run its inner parts one at a time; the wrong one shows itself.

    Try it: press Start. Before each part runs, pick what it returns. The columns and the tables are in the query; open the tables to look.

    Interactive · Predict each level, from the inside out — try it in the lesson

    Takeaway: read and write nested queries from the inside out: each inner query becomes a value or a list, and the query around it gets simpler.

  7. Correlated subqueries and EXISTS: once per rowSo far every inner query could run on its own. Now look at this one: which customers have placed an order of 1000 or more?InteractiveHands-on
    Read this step

    So far every inner query could run on its own. Now look at this one: which customers have placed an order of 1000 or more?

    SELECT name
    FROM customers c
    WHERE EXISTS (
      SELECT 1
      FROM orders o
      WHERE o.customer_id = c.customer_id
        AND o.amount >= 1000
    );
    

    The inner query mentions c.customer_id, a column of the outer row. It can't run alone: it has no customer until the outer query hands it one. This is a correlated subquery, and the database runs it (in principle) once for each outer row: for Acme Ltd it looks for Acme's big orders, for Birch & Co it looks for Birch's, and so on.

    EXISTS (subquery) is TRUE when the inner query returns at least one row, FALSE when it returns none. What it selects doesn't matter, so people write SELECT 1. NOT EXISTS is the opposite: keep the customer when nothing is found.

    Correlated subqueries also work in SELECT, as a value per row: (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders counts each customer's orders, zero included.

    Try it: run the presets and watch the list of runs, one per customer. Then do the tasks.

    Interactive · Correlated subqueries: the inner query runs for each row — try it in the lesson

    The "Above own average" preset compares each order with the average of its own customer's orders: 101 is above Acme's average of 683.33, 105 above Birch's 800, 104 above Cobalt's 566.67. One number for the whole table couldn't say that.

    Takeaway: a subquery that reads the outer row is correlated and runs once per row; EXISTS asks "is there at least one?".

  8. NOT IN and NULL: the query that returns nothingWhich reps have no customers yet? Dev has none, so the natural query looks rightInteractiveHands-on
    Read this step

    Which reps have no customers yet? Dev has none, so the natural query looks right:

    SELECT name FROM reps
    WHERE rep_id NOT IN (SELECT rep_id FROM customers);
    

    It returns no rows at all. The reason is one customer: Ember Labs has no rep yet, so its rep_id is NULL, and the list is (11, 12, 13, 11, NULL, 12).

    14 NOT IN (11, 12, 13, 11, NULL, 12) means 14 <> 11 AND 14 <> 12 AND … AND 14 <> NULL. Any comparison with NULL is unknown (NULL means "we don't know the value", so the database can't say 14 differs from it). TRUE AND unknown is unknown, and WHERE keeps only rows where the test is TRUE. So Dev is dropped, and so is every other rep. One NULL in a NOT IN list empties the result. IN doesn't have this problem: a match is still TRUE.

    Two fixes, both correct:

    • NOT EXISTS with a correlated subquery: it asks "is there a customer with this rep?", and a NULL rep_id matches nobody.
    • Keep NOT IN, but take NULLs out of the list: WHERE rep_id IS NOT NULL inside the subquery.

    Prefer NOT EXISTS: it stays right even if someone later adds NULLs to the column.

    Try it: run the NOT IN preset and look at Dev's row in the reps table: a question mark, unknown, dropped. Switch Ember's NULL off and run it again. Then fix the query both ways.

    Interactive · NOT IN, NULL and NOT EXISTS — try it in the lesson

    Takeaway: NOT IN returns nothing when its list holds a NULL; use NOT EXISTS, or filter the NULLs out of the list.

  9. Subquery in FROM: a table made on the spotA query's result is a table, so a subquery can stand where a table stands: in FROM. The outer query reads it like any other table.InteractiveHands-on
    Read this step

    A query's result is a table, so a subquery can stand where a table stands: in FROM. The outer query reads it like any other table. It needs a name after the closing bracket (AS t); MySQL, SQL Server and PostgreSQL before version 16 refuse it without one, so always give one.

    The classic use is an aggregate of aggregates. What does an average customer spend in total? First total each customer, then average the totals:

    SELECT AVG(total)
    FROM (SELECT customer_id, SUM(amount) AS total
          FROM orders
          GROUP BY customer_id) AS t;
    

    AVG(SUM(amount)) in one query fails: "aggregate function calls cannot be nested". The subquery in FROM is the way to do it in two steps.

    Try it: run the inner query alone first and read its five rows, then the whole query. Then do the tasks.

    Interactive · A subquery in FROM: totals first, then the average — try it in the lesson

    Check your work · Run task 1. Paste the average customer total. — check it in the lesson

    Takeaway: a subquery in FROM is a table made on the spot; name it, then query it like any table.

  10. WITH: give each step a name (CTEs)A subquery in FROM reads from the middle out, and long ones get hard to follow. WITH moves the step to the top and gives it a name. That named step is a common table expression, a CTEInteractiveHands-on
    Read this step

    A subquery in FROM reads from the middle out, and long ones get hard to follow. WITH moves the step to the top and gives it a name. That named step is a common table expression, a CTE:

    WITH customer_totals AS (
      SELECT customer_id, SUM(amount) AS total
      FROM orders
      GROUP BY customer_id
    )
    SELECT AVG(total)
    FROM customer_totals;
    

    Same answer, 1266, but now it reads top to bottom: first the totals, then their average. Three more things WITH gives you:

    • Several steps, separated by commas; each can use the ones before it: WITH a AS (…), b AS (SELECT … FROM a) SELECT … FROM b.
    • Reuse: the outer query can read a CTE twice, for example to compare each total with the average total.
    • Names that explain: customer_totals says what the step is; t doesn't.

    A CTE exists only while its statement runs: it isn't saved like a table or a view.

    Try it: run the presets and watch each named step run in order, then feed the next. Then do the tasks.

    Interactive · WITH: named steps that feed each other — try it in the lesson

    Takeaway: WITH names each step of a long query; later steps and the final query read the earlier ones like tables.

Comments

You’ll sign in with Google before it’s posted.Comments are public — don’t name your students.
0 / 1,500

No comments yet. Be the first to say how it went.

Using it

Workshopy shares this lesson under CC BY 4.0. Anyone may use and change it, with credit to you. Anyone can learn it here, free. A copy in your workspace is yours to change and run live; your students never need an account.

Versions

  1. 1

    Version 1Current

    · First published

Copies are made from the current version. A copy you already have doesn’t change.

More from Workshopy

All 78 lessons
All 21 Data & AI lessons