SQL WITH Clause

SQL WITH Clause (Common Table Expressions)

The SQL WITH clause, also known as Common Table Expressions (CTEs), is used to define temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. This command is essential for simplifying complex queries and improving readability.

---

Syntax

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

-- For recursive CTE
WITH RECURSIVE cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
    UNION ALL
    SELECT column1, column2, ...
    FROM cte_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;

• WITH cte_name AS: This is the SQL keyword used to define a Common Table Expression (CTE).
• cte_name: This specifies the name of the CTE.
• SELECT column1, column2, ... FROM table_name WHERE condition: This specifies the query that defines the CTE.
• SELECT column1, column2, ... FROM cte_name: This specifies the query that uses the CTE.
• WITH RECURSIVE cte_name AS: This specifies the keyword for defining a recursive CTE.
• UNION ALL: This is used to combine the results of the initial query and the recursive query.

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the WITH clause to simplify a complex query.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named sales within the previously created database.

USE example_db;

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2)
);

Here, we define the sales table with columns for sale_id, product_id, sale_date, quantity, and price. The sale_id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the sales table.

INSERT INTO sales (product_id, sale_date, quantity, price) VALUES (1, '2023-01-01', 2, 19.99);
INSERT INTO sales (product_id, sale_date, quantity, price) VALUES (2, '2023-01-02', 1, 9.99);
INSERT INTO sales (product_id, sale_date, quantity, price) VALUES (1, '2023-01-03', 3, 19.99);
INSERT INTO sales (product_id, sale_date, quantity, price) VALUES (3, '2023-01-04', 5, 29.99);

Here, we insert four rows of data into the sales table.

Step 4: Using the WITH Clause

This step involves using the WITH clause to simplify a query that calculates the total sales for each product.

WITH total_sales AS (
    SELECT product_id, SUM(quantity * price) AS total_revenue
    FROM sales
    GROUP BY product_id
)
SELECT product_id, total_revenue
FROM total_sales;

This command defines a CTE named total_sales that calculates the total revenue for each product, and then retrieves the product ID and total revenue from the CTE.

Step 5: Using a Recursive CTE

This step involves using a recursive CTE to generate a sequence of numbers from 1 to 10.

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT n
FROM numbers;

This command defines a recursive CTE named numbers that generates a sequence of numbers from 1 to 10, and then retrieves the sequence from the CTE.