Common Table Expressions (CTEs) in SQL

-

Common Table Expressions (CTEs) in SQL — A Beginner's Guide

A Common Table Expression (CTE) is a named, temporary result set you can reference within a single query. Think of it as giving a subquery a name and using that name like a regular table — it makes complex queries dramatically more readable.

📘 Basic Syntax

WITH high_earners AS (
  SELECT employee_id, name, salary
  FROM employees
  WHERE salary > 80000
)
SELECT * FROM high_earners
WHERE name LIKE 'A%';

The WITH ... AS (...) block defines the CTE, and it can then be queried just like a table in the main SELECT below it.

🎯 Why Use a CTE Instead of a Subquery?

Compare the same logic without a CTE:

-- Without CTE — nested and harder to read
SELECT * FROM (
  SELECT employee_id, name, salary
  FROM employees
  WHERE salary > 80000
) high_earners
WHERE name LIKE 'A%';

Functionally identical, but for anything more complex than this toy example, nested subqueries get hard to follow fast. CTEs let you name each logical step, top to bottom, like a recipe rather than a deeply nested set of parentheses.

🧪 Multiple CTEs in One Query

WITH regional_sales AS (
  SELECT region, SUM(amount) AS total_sales
  FROM orders
  GROUP BY region
),
top_regions AS (
  SELECT region
  FROM regional_sales
  WHERE total_sales > 100000
)
SELECT o.*
FROM orders o
JOIN top_regions t ON o.region = t.region;

Each CTE can reference the ones defined before it, letting you break a complex transformation into clear, named steps.

🔁 Recursive CTEs

CTEs can also reference themselves, which is how you handle hierarchical data (org charts, category trees, bill-of-materials structures):

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level employees (no manager)
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive part: employees whose manager is already in org_chart
  SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart
ORDER BY level;

This walks down the management hierarchy level by level — something that's genuinely difficult to express any other way in plain SQL.

⚠️ Common Mistakes

  • Assuming a CTE is materialized/cached like a temp table — in most databases (including BigQuery), a CTE is re-evaluated each time it's referenced, so referencing the same CTE multiple times can mean repeated computation
  • Forgetting the RECURSIVE keyword when writing a self-referencing CTE — syntax requirements vary slightly by database
  • Building deeply nested CTEs when a simple intermediate table would be clearer and better for reuse across multiple queries

🔗 Related Posts

  • SQL JOIN Types Explained (INNER, LEFT, RIGHT, FULL) with Examples
  • SQL Window Functions Explained: ROW_NUMBER, RANK, LAG, LEAD

Comments

Post a Comment

Popular Posts

BigQuery MERGE Statement – Explained

-
Image
BigQuery MERGE Statement – Explained with Syntax & Use Cases BigQuery MERGE Statement – Explained 📘 What is BigQuery MERGE? The MERGE statement in BigQuery allows you to conditionally INSERT , UPDATE , or DELETE rows in a target table based on matching rows from a source table. It’s ideal for data synchronization, staging table merges, and incremental updates. 🖼️ Thumbnail Preview 🧪 Syntax Overview MERGE target_table AS T USING source_table AS S ON T.id = S.id WHEN MATCHED THEN UPDATE SET T.name = S.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (S.id, S.name); 🎯 Example Use Case MERGE dataset.inventory AS I USING dataset.new_arrivals AS N ON I.product_id = N.product_id WHEN MATCHED THEN UPDATE SET I.quantity = I.quantity + N...
Read more

LRT Masjid Jamek-Bandaraya

-
Image
LRT Ampang Line Closure – Masjid Jamek to Bandaraya Suspension Update LRT Ampang Line Update – Service Suspension Extended The section of the LRT Ampang Line between Masjid Jamek and Bandaraya stations will remain closed for an additional two weeks following the discovery of significant structural damage on an overhead viaduct near Bandaraya. Transport Minister Anthony Loke stated that the line will reopen only after corrective measures and safety assessments are fully completed. “Once it is okay and safety is ensured, we will reopen the line. I apologise to all commuters who are affected,” he added. Public transport operator Rapid Rail has engaged a professional engineering firm to conduct a detailed investigation of the cracked viaduct and pier. This probe will help determine the source of damage and inform the restoration strategy. 🚍 S...
Read more

Kavalkaran - MGR Tamil movie Part 4

-
Kavalkaran (1967) - MGR Tamil movie Please subscribe to channel & comment ur favourite puratchi thalaivar movie Kaavalkaaran (1967) - M.G.R | Tamil Full movie Kaavalkaaran is a 1967 Indian Tamil-language spy film, directed by P. Neelakantan and produced and co-written by R. M. Veerappan. The film stars M. G. Ramachandran and Jayalalithaa, with M. N. Nambiar, S. A. Ashokan, Sivakumar and Nagesh in supporting roles. It was released on 7 September 1967. The film became a major success, and Ramachandran considered it his ninth turning point. As Per wikipedia , Before Kaavalkaaran began production, MGR's voice had been damaged due to a gunshot to his throat caused by M. R. Radha. Despite this, MGR refused others' suggestion to use a dubbing artiste, and dubbed in his own voice. Check out more videos like ...
Read more