SQL WITH

What is SQL WITH?

The SQL WITH clause, also known as a Common Table Expression (CTE), is used to create a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It makes complex queries more readable by breaking them into smaller, named, and reusable subqueries.

When you would use it

You would use the SQL WITH clause when dealing with complex SQL queries where you need to perform multiple operations, subqueries, or recursive queries. It enhances query readability and maintainability by isolating subqueries into easily referenced sections.

Syntax

The basic syntax of the SQL WITH clause is as follows:

WITH cte_name (column1, column2, ...) AS (
    subquery
)
SELECT columns
FROM cte_name
WHERE condition;

• cte_name: The name of the Common Table Expression.
• (column1, column2, ...): An optional list of column names for the CTE.
• subquery: The subquery that defines the CTE.
• SELECT columns: The main query that references the CTE.
• WHERE condition: An optional condition to filter the results.

Parameter values

• cte_name: A name you give to the CTE, which should be unique within the query.
• (column1, column2, ...): An optional list of column names, typically used if the CTE has multiple columns.
• subquery: A standard SQL query that defines the CTE.
• SELECT columns: The columns you want to retrieve in the main query.
• WHERE condition: An optional condition that filters the results of the main query.

Example query

Suppose we have a table named "orders" with columns "order_id," "product_id," and "order_date." We want to find the total number of orders for each product in the year 2023 using a CTE:

WITH ProductOrders AS (
    SELECT product_id, COUNT(*) AS order_count
    FROM orders
    WHERE YEAR(order_date) = 2023
    GROUP BY product_id
)
SELECT product_id, order_count
FROM ProductOrders
WHERE product_id IN (1, 2);
-- Comment the line below to show it doesn't affect the query.
-- AND order_count > 5;

In the above query, we create a CTE named "ProductOrders" to count the orders for each product in the year 2023 and then retrieve the product_id and order_count in the main query.

Example table response

Assuming the "orders" table contains the following data:

| order_id | product_id | order_date  |
|--------- |------------|------------ |
| 1       | 1         | 2023-01-05  |
| 2       | 2         | 2023-01-15  |
| 3       | 1         | 2023-02-10  |
| 4       | 2         | 2023-02-20  |

The query mentioned earlier would return the following result:

| product_id | order_count |
|------------|------------ |
| 1         | 2           |
| 2         | 2           |

This provides the total number of orders for each product in the year 2023.

Use cases

• Simplifying complex queries by breaking them into reusable parts.
• Performing recursive queries or hierarchical data manipulation.
• Enhancing query readability and maintainability.

SQL languages this is available for

The SQL WITH clause, or Common Table Expression, is a standard SQL feature and is available in most modern relational database management systems (RDBMS), including but not limited to MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. The specific syntax may vary slightly between database systems, but the core functionality remains the same.