Common Table Expressions (CTEs) Flashcards

Which keyword introduces a Common Table Expression in standard SQL?

Answer: WITH

A CTE is defined using the WITH keyword followed by the CTE name and its query.

How long does a non-recursive CTE persist?

Answer: Only for the single statement it is attached to

A CTE exists only for the duration of the single query in which it is defined.

What separates multiple CTEs defined in the same WITH clause?

Answer: A comma

Multiple CTEs are chained in one WITH clause, separated by commas.

Can a later CTE in the same WITH clause reference an earlier CTE?

Answer: Yes, earlier CTEs are visible to later ones

CTEs are evaluated in order, so a later CTE can reference any CTE defined before it.

Which statement type can a CTE precede in many databases like PostgreSQL?

Answer: SELECT, INSERT, UPDATE, and DELETE

In PostgreSQL, a WITH clause can prefix SELECT, INSERT, UPDATE, or DELETE statements.

What is a primary readability benefit of using a CTE over a nested subquery?

Answer: It names a query block so it can be referenced clearly

A CTE assigns a readable name to a query block, improving clarity over deeply nested subqueries.

In the syntax WITH cte_name AS (...), what does cte_name represent?

Answer: The temporary result set name used later in the query

cte_name is the identifier you use to reference the CTE's result set in the main query.