Skip to the content
Start

Recursive queries

SQL · Unit 10: WITH and sets

At the company, each join of employees to itself went up one level, from a person to their boss. Getting from Rachel to Carol took two, because you knew it was two levels. What if you don't know how many there are? You need a query that repeats on its own.

The smallest one counts. WITH RECURSIVE opens a step that reads from itself. It has a starting point, the 1, then UNION ALL, and a query that takes the last row and makes the next one. The WHERE checks the row you start from. With the 4 it's true, and that makes the 5. With the 5 it isn't, and that's where it ends.

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM numbers
  WHERE n < 5
)
SELECT n
FROM numbers;

Returns

n
1
2
3
4
5

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Predict the output

    What does this query return?

  2. 2. Put the lines in order

    Put in order the query that counts from 1 to 10, and not one number more. Two lines don't belong.

  3. 3. Find the bug

    Another countdown, from 5 to 1. The count goes up instead of down, and it doesn't stop. This app shows the first 1,000 rows and cuts it off. Which line has the problem?

  4. 4. Multiple choice

    The query that goes up from Frank has no WHERE to stop it. Why does it end when it reaches Carol?

  5. 5. Predict the output

    Grace is the illustrator, the only person in Design. Her boss_id is 1, which is Carol. Carol's is empty. What does this query return?

  6. 6. Complete the code

    The chain for Lydia, a developer who reports to Paige. Fill in the blank with where the repeating query reads from.

  7. 7. Predict the output

    Three people point to Carol with their boss_id: Mark, Paige, and Grace. Two point to Mark, and three to Paige. Nobody points to the rest. What does this query return?

  8. 8. Put the lines in order

    Rachel earns 1900, and she's been promised 150 more each year. In what year does she reach the 2600 that Frank earns? Put in order the query that gives the year and the salary for each year, up to the first one where she reaches or passes 2600. Two lines don't belong.

  9. 9. Find the case that fails

    The list of everyone above Frank, up to management. Today that's Paige and Carol. Someone wrote it with joins, one level in the first half and two in the second. Which of these changes makes it leave someone out?

  10. 10. Write the code

    How many levels separate Nicole from management? The starting point is already there, with its level at 0. Write the repeating query and the one below it, so that it returns two columns, in this order: the name and the level of Nicole and of each person above her. From the highest level to the lowest, with management on top. The step plays e, and employees plays b.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →