Using WITH
- SQL course
- Advanced
- Lesson 2/81
- 4 min
Define a CTE with WITH.
Using 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.
Practice Using WITH
Define a big_orders CTE and select all columns from it.
Starter code
-- use with
WITHSample solution
WITH big_orders AS (
SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big_orders;Expected output
id amount 1 250 3 180
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add AS
WITH big_orders ____ (
SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big_orders;WITH big_orders AS (
SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big_orders;Explanation: AS follows the CTE name and binds the query in parentheses to that name.
2 How is a CTE used in the main query?
- WHERE ile
- FROM ile Correct answer
- JOIN ile
- HAVING ile
Explanation: A CTE is used in the main query with FROM.
3 Using WITH: match the terms
| Term | Meaning |
|---|---|
WITH |
start |
AS |
define |
FROM |
source |
CTE |
temporary |