WITH clause syntax in PostgreSQL - Step-by-Step Execution

Concept Flow - WITH clause syntax

Start Query

↓

Define CTE(s) with WITH

↓

Execute CTE(s)

↓

Use CTE(s) in main query

↓

Return final result

↓

End

The WITH clause defines temporary named result sets (CTEs) that run first, then the main query uses these results to produce the final output.

Execution Sample

PostgreSQL

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT * FROM recent_orders;

This query defines a CTE named recent_orders with orders after 2024-01-01, then selects all rows from it.

Execution Table

Step	Action	Evaluation	Result
1	Start query execution	WITH clause found	Prepare to run CTE
2	Run CTE recent_orders	SELECT * FROM orders WHERE order_date > '2024-01-01'	Temporary table recent_orders created with filtered rows
3	Run main query	SELECT * FROM recent_orders	Fetch all rows from recent_orders CTE
4	Return result	Output rows from recent_orders	Final result set returned
5	End	No more steps	Query execution complete

💡 All steps completed, final result returned from CTE usage

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
recent_orders	undefined	temporary table with filtered orders	used in main query	final result set

Key Moments - 2 Insights

Why does the CTE run before the main query?

Because the execution_table row 2 shows the CTE is evaluated first to create a temporary result used by the main query in row 3.

Can the main query use the CTE multiple times?

Yes, the CTE acts like a temporary table available throughout the main query, as shown in row 3 where it is referenced.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 2?

A The main query runs and returns results

B The CTE recent_orders is executed and temporary table created

C The query ends without running

D The database connection closes

💡 Hint

Check execution_table row 2 describing CTE execution

At which step does the main query use the CTE?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at execution_table row 3 where main query selects from recent_orders

If the CTE query returned no rows, what would the final result be?

A The main query runs on all orders

B An error occurs

C The final result set is empty

D The query runs indefinitely

💡 Hint

Consider variable_tracker showing recent_orders content after step 2

Concept Snapshot

WITH clause syntax:
WITH cte_name AS (
  subquery
)
SELECT * FROM cte_name;

- Defines temporary named result sets (CTEs)
- CTE runs first, main query uses its results
- Helps organize complex queries and reuse results

Full Transcript

The WITH clause in PostgreSQL lets you define temporary named queries called Common Table Expressions (CTEs). These CTEs run first and create temporary result sets. Then the main query uses these results to produce the final output. For example, you can define a CTE named recent_orders that selects orders after a certain date. The main query then selects all rows from recent_orders. Execution starts by running the CTE, storing its results temporarily, then the main query runs using that temporary data. This helps break complex queries into simpler parts and reuse intermediate results. The execution table shows each step: starting the query, running the CTE, running the main query, returning results, and ending. Variables like recent_orders hold the temporary data after the CTE runs. Understanding that the CTE runs before the main query is key to using WITH clauses effectively.

Practice

1. What is the main purpose of the WITH clause in PostgreSQL?

easy

A. To create indexes on columns

B. To permanently store data in a new table

C. To create temporary named query results called CTEs

D. To delete rows from a table

Solution

1. Step 1: Understand the role of WITH clause

   The WITH clause defines Common Table Expressions (CTEs) which are temporary named result sets used within a query.

2. Step 2: Differentiate from other SQL commands

   It does not create permanent tables, delete data, or create indexes; it only helps organize complex queries.

3. Final Answer:

   To create temporary named query results called CTEs -> Option C

4. Quick Check:

   WITH clause = Temporary named queries [OK]

Hint: WITH clause defines temporary named queries (CTEs) [OK]

Common Mistakes:

• Thinking WITH creates permanent tables
• Confusing WITH with DELETE or CREATE INDEX
• Assuming WITH modifies data directly

2. Which of the following is the correct syntax to start a query using a WITH clause in PostgreSQL?

easy

A. WITH cte_name SELECT * FROM table_name AS cte_name;

B. WITH (SELECT * FROM table_name) AS cte_name SELECT * FROM cte_name;

C. WITH cte_name = SELECT * FROM table_name; SELECT * FROM cte_name;

D. WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name;

Solution

1. Step 1: Recall correct WITH clause syntax

   The correct syntax is: WITH cte_name AS (subquery) followed by the main SELECT using cte_name.

2. Step 2: Check each option

   WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; matches the correct syntax. Options A, B, and D have misplaced keywords or wrong order.

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; -> Option D

4. Quick Check:

   WITH cte_name AS (query) = correct syntax [OK]

Hint: WITH clause uses AS (subquery) syntax [OK]

Common Mistakes:

• Omitting AS keyword
• Placing SELECT before AS
• Using = instead of AS

3. Given the query:

WITH cte AS (SELECT id, name FROM users WHERE active = true) SELECT * FROM cte WHERE id < 5;

What will this query return?

medium

A. All users with id less than 5 regardless of active status

B. All users with active = true and id less than 5

C. All users with active = false and id less than 5

D. Syntax error due to incorrect WHERE clause

Solution

1. Step 1: Analyze the CTE definition

   The CTE named 'cte' selects users where active = true, so only active users are included.

2. Step 2: Analyze the main query filtering

   The main query selects from 'cte' where id < 5, so it filters active users with id less than 5.

3. Final Answer:

   All users with active = true and id less than 5 -> Option B

4. Quick Check:

   CTE filters active=true, main query filters id<5 [OK]

Hint: CTE filters first, main query filters second [OK]

Common Mistakes:

• Ignoring CTE filter and only applying main WHERE
• Assuming all users regardless of active status
• Thinking syntax error due to WHERE

4. Identify the error in the following query:

WITH cte1 AS (SELECT * FROM orders), cte2 (SELECT * FROM customers) SELECT * FROM cte1 JOIN cte2 ON cte1.customer_id = cte2.id;

medium

A. Missing AS keyword before the second CTE's subquery

B. Incorrect JOIN syntax between cte1 and cte2

C. CTEs cannot be used together in one WITH clause

D. SELECT statement missing after WITH clause

Solution

1. Step 1: Check syntax of multiple CTEs

   Multiple CTEs must be separated by commas, each with the syntax: name AS (subquery).

2. Step 2: Identify error in second CTE

   The second CTE 'cte2' is missing the AS keyword before its subquery.

3. Final Answer:

   Missing AS keyword before the second CTE's subquery -> Option A

4. Quick Check:

   Each CTE needs AS (subquery) [OK]

Hint: Each CTE must have AS before its query [OK]

Common Mistakes:

• Omitting AS keyword in CTE definition
• Misplacing commas between CTEs
• Incorrect JOIN syntax confusion

5. You want to write a query that uses two CTEs: one to get all active users, and another to get their orders. Which of the following is the correct way to write this using the WITH clause?

hard

A. WITH active_users AS (SELECT * FROM users WHERE active = true), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT active_users.name, user_orders.order_date FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id;

B. WITH active_users (SELECT * FROM users WHERE active = true), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT * FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id;

C. WITH active_users AS SELECT * FROM users WHERE active = true, user_orders AS (SELECT * FROM orders) SELECT * FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id;

D. WITH active_users AS (SELECT * FROM users WHERE active = true) user_orders AS (SELECT * FROM orders) SELECT * FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id;

Solution

1. Step 1: Verify correct syntax for multiple CTEs

   Multiple CTEs must be separated by commas, each defined as name AS (subquery).

2. Step 2: Check each option's syntax and logic

   WITH active_users AS (SELECT * FROM users WHERE active = true), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT active_users.name, user_orders.order_date FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id; correctly uses AS and commas, and references active_users in user_orders. Options B, C, and D have syntax errors or missing commas.

3. Final Answer:

   WITH active_users AS (SELECT * FROM users WHERE active = true), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT active_users.name, user_orders.order_date FROM active_users JOIN user_orders ON active_users.id = user_orders.user_id; -> Option A

4. Quick Check:

   Multiple CTEs separated by commas with AS (query) [OK]

Hint: Separate multiple CTEs with commas and use AS (query) [OK]

Common Mistakes:

• Missing commas between CTEs
• Omitting AS keyword
• Not referencing CTEs properly in later queries