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.
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.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.
This step involves creating a new database named example_db
.
CREATE DATABASE example_db;
In this example, we create a database named example_db
.
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.
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.
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.
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.