SQL CTEs

A CTE stands for common table expressions and is a named subquery normally defined with a WITH course.

A CTE exists temporarily and is used within the context of a larger query. They are useful because:

  • Simplify complex queries by organising them into logical parts
  • Can reference a CTE multiple times within the same query
  • They're not stored in the database and only exists during the execution of the query

Syntax

WITH cte_name AS (

SELECT column1, column2

FROM table_name

WHERE condition

)

SELECT *

FROM cte_name;

IMPORTANT TO NOTE: with clause is processed before the main query execution begins as it acts as a preparatory step. It therefore ensures the CTE can be used and referred to in the main query.

Author:
Priya Kondola
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab