Using WITH

Define a CTE with WITH.

You define a CTE with WITH and use it in the main query. It breaks the query into parts.

WITH big_orders AS (
  SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big_orders;

This query makes orders over 100 a CTE.

WITH defines a temporary result set (CTE) named big_orders; the query after it uses it like a table. All columns are selected from big_orders.

Using WITH — details

The CTE name is used like a table name in the main query. You write FROM big_orders.

A CTE only lives for the query that directly follows it; when the query ends it disappears and no permanent table is created.

Define a big_orders CTE and select all columns from it.

Show example solution

WITH big_orders AS (
  SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big_orders;

Add AS

WITH big_orders ____ (
  SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big_orders;

Show answer

WITH big_orders AS (
  SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big_orders;