SQL WITH

What is SQL WITH?

WITH defines one or more Common Table Expressions (CTEs) that act like temporary, named result sets for the duration of a single statement.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL WITH: PostgreSQL, MySQL 8+, SQL Server 2005+, Oracle 9i+, SQLite 3.8.3+, Snowflake, BigQuery, Redshift

SQL WITH Full Explanation

WITH introduces Common Table Expressions, named subqueries that you can reference later in the same statement as if they were tables. A CTE improves readability, eliminates duplicate subqueries, and supports recursion. Multiple CTEs can be declared, separated by commas, and each may specify an optional column list. Many dialects accept the RECURSIVE modifier before the first CTE to enable recursive queries that iterate until no new rows are produced. CTEs exist only for the statement that follows the WITH clause; they are not stored objects. Some engines materialize a CTE, while others inline it, so performance can vary. Recursive CTEs must include a UNION [ALL] between an anchor member and a recursive member and require a termination condition to avoid infinite loops. CTEs obey the same privilege rules as the underlying tables and cannot reference each other in a cycle.

SQL WITH Syntax

WITH [RECURSIVE] cte_name [ (column1, column2, ...) ] AS (
    subquery
) [ , cte_name2 AS (
    subquery2
) ... ]
SELECT ... FROM cte_name ...;

SQL WITH Parameters

RECURSIVE (keyword) - Allows self-referencing CTEs
cte_name (identifier) - Name of the temporary result set
column_list (identifiers) - Optional explicit column names for the CTE output
subquery (SQL query) - Defines the rows returned by the CTE

Example Queries Using SQL WITH

-- 1. Simple CTE
WITH recent_orders AS (
    SELECT id, user_id, created_at
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT user_id, COUNT(*) AS orders_last_week
FROM recent_orders
GROUP BY user_id;

-- 2. Recursive hierarchy
WITH RECURSIVE org_chart AS (
    SELECT id, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL        -- anchor
    UNION ALL
    SELECT e.id, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id   -- recursion
)
SELECT * FROM org_chart;

-- 3. Multiple CTEs
WITH sales_2023 AS (
    SELECT * FROM sales WHERE year = 2023
),
profits_2023 AS (
    SELECT region, SUM(revenue - cost) AS profit
    FROM sales_2023 GROUP BY region
)
SELECT * FROM profits_2023 ORDER BY profit DESC;

Expected Output Using SQL WITH

Returns one row per user with a count of orders made in the last 7 days.
Returns every employee with their level in the company hierarchy starting at 0 for top managers.
Returns regional profit totals for 2023 ordered from highest to lowest.

Use Cases with SQL WITH

Break complex queries into logical steps for readability
Eliminate repeated subqueries and make maintenance easier
Build recursive queries such as hierarchical data, bill of materials, or graph traversals
Stage data transformations before an INSERT or UPDATE statement
Limit the scope of temporary calculations without creating permanent tables

Common Mistakes with SQL WITH

Forgetting the comma between multiple CTE definitions
Referencing a CTE outside the statement that defines it
Creating a recursive CTE without a termination condition, causing an infinite loop
Assuming all engines materialize CTEs; some inline them, which may change performance
Omitting UNION ALL in a recursive CTE when duplicates are acceptable, leading to slower DISTINCT processing

First Introduced In

SQL:1999 standard

Frequently Asked Questions

What is a Common Table Expression?

A Common Table Expression (CTE) is a temporary, named result set defined with WITH and used within the same SQL statement. It works like an inline view but with better readability.

Do CTEs replace subqueries?

CTEs do not replace subqueries entirely but offer a cleaner, reusable alternative. You can reference the CTE multiple times without duplicating code.

When should I use RECURSIVE?

Use RECURSIVE when you need to traverse hierarchical or graph-like data, such as organizational charts or parent-child structures. Ensure you add a termination condition.

Can I index a CTE?

You cannot add indexes directly on a CTE because it is not a stored object. Indexing must be applied to the underlying base tables instead.