Lesson 28 of 30

WITH: naming your steps

Today at the studio

Kou's queries had parentheses inside parentheses inside parentheses, and nobody could read them. "Tidy up," says Nagi. "Give each step a name."

The idea

WITH name AS (SELECT ...) defines a temporary result, called a common table expression, that you can use in the query that follows as if it were a table. You can define several, separated by commas.

It does not make the query faster. It makes it readable: one step per name, read from top to bottom. Use it when a subquery gets long or when you need the same result twice.

Example

WITH cheap AS (SELECT * FROM merch WHERE price < 10)
SELECT product FROM cheap WHERE units_sold > 2000

Your turn

0 of 4 questions

Question 1 of 4

Using WITH, total the merch units per show, then show the title and units of the shows above 1500.