12. CTE (WITH) Reference

Common Table Expression structure.

Basic CTE

Return: Value

Temporary named result sets.

Syntax signature:WITH cte_name AS (SELECT ... ) SELECT * FROM cte_name;

Code snippet:

python

WITH active_users AS (
  SELECT id, name FROM users WHERE status = 'Active'
)
SELECT * FROM active_users;

Expected Output:Selected results from CTE query.

Remember: Always ensure correct syntax formatting when calling Basic CTE.

Multiple CTE

Return: Value

Chained multiple CTE declarations.

Syntax signature:WITH c1 AS (...), c2 AS (...) SELECT * FROM c1 JOIN c2 ON c1.id = c2.id;

Code snippet:

python

WITH u AS (
  SELECT id FROM users
), o AS (
  SELECT user_id FROM orders
)
SELECT * FROM u JOIN o ON u.id = o.user_id;

Expected Output:Joined CTE datasets.

Remember: Always ensure correct syntax formatting when calling Multiple CTE.

Recursive CTE

Return: Value

Iterative loop matching hierarchy.

Syntax signature:

WITH RECURSIVE r AS (
  anchor_query UNION ALL recursive_query
) SELECT * FROM r;

Code snippet:

python

WITH RECURSIVE counts(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counts WHERE n < 5
)
SELECT n FROM counts;

Expected Output:Sequence 1 to 5.

Remember: Always ensure correct syntax formatting when calling Recursive CTE.