SQL WITH Clause

The SQL WITH clause allows you to define a temporary result set, also known as a common table expression (CTE), which can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It makes complex queries easier to read and manage by breaking them down into simpler, reusable parts.

The CTE can be used to store the intermediate results of a query and then refer to that result in a main query. This is especially useful when dealing with multiple subqueries that are used repeatedly in a query.
The WITH clause is commonly used for complex queries involving aggregations, recursion, or when multiple references to the same query are needed.

Table Structure

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    Product VARCHAR(50),
    CustomerID INT,
    Price DECIMAL(10, 2)
); 

Example

In this example, we will use the WITH clause to create a CTE that stores the average price of products in the Orders table. We will then use the CTE to filter orders with a price above the average price.

-- Use WITH clause to define a CTE that calculates the average price
WITH AvgPrice AS (
    SELECT AVG(Price) AS avg_price FROM Orders
)
-- Select orders where the price is above the average price
SELECT * FROM Orders
WHERE Price > (SELECT avg_price FROM AvgPrice);
            

Explanation

- The WITH clause defines a common table expression (CTE) named AvgPrice, which calculates the average price of all products in the Orders table.
- The main query then uses the CTE to filter orders with a Price greater than the average price.
- This approach allows for easier readability and reuse of the logic within the query, especially for complex conditions.