Prev Next

Common Table Expressions (CTE)

A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are useful for simplifying complex queries and improving readability.

Syntax for CTE

WITH CTE_Name (column1, column2, ...) AS (
    -- CTE query
    SELECT column1, column2, ... FROM table_name WHERE condition
)
SELECT * FROM CTE_Name;

Example: Simple CTE

WITH FighterCTE AS (
    SELECT Name, City, BirthDate
    FROM FreedomFighters
    WHERE BirthDate < '1900-01-01'
)
SELECT * FROM FighterCTE;

Output:

NameCityBirthDate
Mahatma GandhiPorbandar1869-10-02
Bal Gangadhar TilakRatnagiri1856-07-23

Recursive CTE

Recursive CTEs are used to perform hierarchical or recursive queries, such as retrieving data from a parent-child relationship.

Example: Recursive CTE

WITH NumbersCTE (Number) AS (
    SELECT 1
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE WHERE Number < 10
)
SELECT * FROM NumbersCTE;

Output:

Number
1
2
3
...
10

When to Use CTEs

  • When you need to simplify complex queries.
  • When dealing with hierarchical or recursive data.
  • When performing data transformations that require multiple references to a subquery.

Do's and Don'ts

Do's

  • Use CTEs to make your queries easier to read and maintain.
  • Consider using recursive CTEs for hierarchical data retrieval.
  • Break down complex logic into multiple CTEs for clarity.

Don'ts

  • Don't use CTEs if a simple subquery or a temporary table would be more efficient.
  • Don't use recursive CTEs for large datasets without testing performance.
  • Don't forget to analyze query plans to ensure performance is not degraded.
Prev Next