SQL › Cleaner, Step-by-Step Queries

CTEs and the WITH Clause

A CTE (common table expression) gives a query a name with WITH, so the main query can use it like a table. Long queries become a list of small, named steps. Oracle calls it subquery factoring.

Syntax

Order of execution: each CTE, in the order written (a later one can use an earlier one) → the main query

Code
WITH step_one AS (
  SELECT column, AGG(column) AS alias FROM table_name GROUP BY column
),
step_two AS (
  SELECT * FROM step_one WHERE condition
)
SELECT * FROM step_two ORDER BY column;

Examples

One named step

Pick the rows once, then summarise them.

Code
WITH upi AS (
  SELECT status, amount FROM payments WHERE method = 'upi'
)
SELECT status, COUNT(*) AS payments, SUM(amount) AS total
FROM upi
GROUP BY status
ORDER BY status;

Naming the columns

A list after the name gives the step's columns their names, so the query inside needs no aliases. The list must have as many names as the query has columns.

Code
-- city and customers come from the list
WITH city_counts (city, customers) AS (
  SELECT city, COUNT(*) FROM customers GROUP BY city
)
SELECT city, customers FROM city_counts ORDER BY customers DESC, city LIMIT 3;

Steps built on steps

A later CTE can read an earlier one.

Code
WITH product_stars AS (
  SELECT product_id, ROUND(AVG(rating), 2) AS avg_stars
  FROM reviews
  GROUP BY product_id
),
well_liked AS (
  SELECT * FROM product_stars WHERE avg_stars >= 4.5
)
SELECT p.product_name, w.avg_stars
FROM well_liked w
JOIN products p ON p.product_id = w.product_id
ORDER BY w.avg_stars DESC, p.product_name;

Using a step twice

Compare each row with a summary of the same step.

Code
WITH review_counts AS (
  SELECT customer_id, COUNT(*) AS reviews FROM reviews GROUP BY customer_id
)
SELECT customer_id, reviews
FROM review_counts
WHERE reviews > (SELECT AVG(reviews) FROM review_counts)
ORDER BY reviews DESC, customer_id;

The same query without a CTE

Written with subqueries, the step has to appear twice.

Code
SELECT customer_id, reviews
FROM (SELECT customer_id, COUNT(*) AS reviews FROM reviews GROUP BY customer_id) AS rc
WHERE reviews > (SELECT AVG(reviews)
                 FROM (SELECT customer_id, COUNT(*) AS reviews
                       FROM reviews GROUP BY customer_id) AS rc2)
ORDER BY reviews DESC, customer_id;

Readable, not automatically faster

A CTE makes a long query easier to read, check and change. It doesn't make it faster by itself.

Databases differ in how they run one: some work out a step used twice once and reuse the rows, SQL Server runs it again each time it's named, and most can fold a step into the main query. When speed matters, check the plan.

A CTE belongs to one statement

A CTE isn't saved anywhere. Unlike a table or a view, it exists only while its statement runs.

Code
WITH cities AS (SELECT DISTINCT city FROM customers)
SELECT COUNT(*) FROM cities;
 
-- Fails: the name ended with the statement above
SELECT * FROM cities;

Temporary tables

A temporary table is a real table that disappears when the session (the connection) ends. Fill it once, then use it in several statements; you can even index it. In the lab, everything a run creates is gone when it ends.

PostgreSQL and MySQL write CREATE TEMPORARY TABLE; SQL Server starts the name with #, as in #big_orders; Oracle's global temporary tables keep their structure, but each session sees only its own rows.

Code
CREATE TEMPORARY TABLE big_orders AS
SELECT order_id, customer_id, total_amount FROM orders WHERE total_amount > 15000;
 
SELECT COUNT(*) AS big_orders FROM big_orders;
SELECT customer_id, SUM(total_amount) AS big_spend
FROM big_orders
GROUP BY customer_id
ORDER BY big_spend DESC
LIMIT 3;

Remember

• One WITH, then the steps separated by commas.
• A step can use the steps defined before it.
• The main query can use a step several times, which a subquery would have to repeat.
• A CTE only exists inside its own statement.
• A CTE is for readability; whether a step used twice is worked out once depends on the database.
• A temporary table lasts for the session and stores its rows, so several statements can use it.

Practice

1. Top Five Delivery Cities (Easy)
2. Customers Above Average Spend (Medium)
3. Category Share of Revenue (Medium)
4. Customers Who Came Back the Next Month (Hard)