What is the SQL WITH clause

The SQL WITH clause, also known as the Common Table Expression (CTE), is a powerful feature that allows you to define a temporary named result set that can be used within a single SQL statement. This means that you can break down complex queries into smaller, more manageable parts that are easier to understand and maintain.

The WITH clause can also be used to create recursive queries, which can be used to traverse hierarchical data structures.

By using the SQL WITH clause, you can simplify your SQL code and make it more efficient, while improving its readability and maintainability.

Basic Syntax and Usage

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

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

Here is an example of how the SQL WITH clause can be used:

WITH sales_data AS
(
    SELECT product_id, SUM(quantity) AS total_sales
    FROM orders
    WHERE order_date BETWEEN '2022-01-01' AND '2022-01-31'
    GROUP BY product_id
)
SELECT products.product_name, sales_data.total_sales
FROM products
INNER JOIN sales_data ON products.product_id = sales_data.product_id;

In this example, the SQL WITH clause creates a temporary result set named sales_data that calculates the total sales for each product during the month of January 2022. The main SELECT statement then joins this result set with the products table to retrieve the product names and their respective total sales for the specified time period.

By using the SQL WITH clause, complex queries can be broken down into smaller, more manageable steps, making the code easier to read and maintain. Additionally, because the WITH clause creates a temporary result set that can be reused multiple times within a single query, it can also improve query performance by reducing the amount of data that needs to be processed.

Comparison with Subqueries

Subqueries and WITH clauses are both used in SQL to break down complex queries into simpler components. However, there are some key differences between the two.

Subqueries are queries that are embedded within another query. They can be used in various parts of a query, such as the SELECT, FROM, and WHERE clauses. The subquery returns a result set that is used in the outer query. Subqueries can be either correlated or non-correlated.

Here is an example of a non-correlated subquery in the WHERE clause:

SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > '2023-01-03');

In this example, the subquery returns a list of customer IDs who have placed an order after January 1, 2023. The outer query then uses this list to retrieve all the information about those customers from the "customers" table.

On the other hand, a WITH clause, also known as a Common Table Expression (CTE), is used to define a temporary result set that can be used within the context of a larger query. A WITH clause is useful when you need to reuse a subquery multiple times within a larger query, or when you need to break down a complex query into smaller, more manageable pieces.

Here is an example of a WITH clause:

WITH sales_data AS (
  SELECT customer_id, SUM(total_price) AS total_sales
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM sales_data
WHERE total_sales > 1000;

In this example, the WITH clause defines a temporary result set called "sales_data" that calculates the total sales for each customer from the "orders" table. The outer query then uses this result set to retrieve only those customers whose total sales exceed 1000.

The main difference between subqueries and WITH clauses is that subqueries are used to break down a query into smaller pieces within the same query, while WITH clauses are used to define a temporary result set that can be referenced multiple times within a larger query. Additionally, subqueries can be either correlated or non-correlated, while WITH clauses are always non-correlated.

Recursive Queries

In SQL, the recursive queries with clause is a powerful feature that allows you to perform hierarchical queries on a table that has a parent-child relationship. A recursive query is one that refers to itself in the definition of the query, resulting in a result set that depends on the result set of the same query.

Here is an example of how to use recursive queries with the with clause in SQL:

Suppose we have a table called categories that has a parent-child relationship. Each category can have many subcategories, and each subcategory can have many child categories. We want to select all categories and their subcategories, regardless of how deep the nesting goes.

First, we define the tree table as a recursive common table expression (CTE), using the WITH RECURSIVE keywords. The tree table contains the id, parent_id, name, and level columns, which are used to build the hierarchical structure of the categories.

WITH RECURSIVE tree(id, parent_id, name, level) AS (
  SELECT id, parent_id, name, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT *
FROM tree
ORDER BY level, name;

The above query starts by selecting all the rows that have a NULL parent_id, indicating that they are top-level categories. The result set is then recursively expanded to include all the child categories of those top-level categories.

The UNION ALL operator is used to combine the non-recursive and recursive parts of the query. In the non-recursive part, we select the top-level categories from the categories table. In the recursive part, we join the categories table with the tree table on the id and parent_id columns, recursively expanding the result set to include all child categories.

Finally, the outer SELECT statement selects all the columns from the tree table to display the hierarchical structure of the categories, ordered by level and name.

Using the WITH clause with UPDATE, INSERT, and DELETE statements

While the WITH clause is commonly used with SELECT statements, it can also be used with UPDATE, INSERT, and DELETE statements.

Using the WITH clause with UPDATE statements allows you to update a table based on the results of a CTE. For example, let's say you want to update the salaries of employees in a department based on the average salary of that department. You can use a CTE to calculate the average salary and then update the table based on that result:

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
UPDATE employees
SET salary = dept_avg.avg_salary
FROM dept_avg
WHERE employees.department = dept_avg.department;

Using the WITH clause with INSERT statements allows you to insert data into a table based on the results of a CTE. For example, let's say you have a table of orders and you want to insert the total sales for each month into a new table. You can use a CTE to calculate the total sales and then insert the data into a new table:

WITH monthly_sales AS (
  SELECT date_trunc('month', order_date) AS month, SUM(price) AS total_sales
  FROM orders
  GROUP BY date_trunc('month', order_date)
)
INSERT INTO sales_by_month (month, total_sales)
SELECT month, total_sales
FROM monthly_sales;

Using the WITH clause with DELETE statements allows you to delete data from a table based on the results of a CTE. For example, let's say you have a table of customers and you want to delete all customers who have not placed an order in the last year. You can use a CTE to find the customers who have not placed an order in the last year and then delete them:

WITH inactive_customers AS (
  SELECT customer_id
  FROM customers
  WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
    AND order_date > now() - interval '1 year'
  )
)
DELETE FROM customers
WHERE customer_id IN (SELECT customer_id FROM inactive_customers);

References

https://learnsql.com/blog/what-is-with-clause-sql/
https://www.geeksforgeeks.org/sql-with-clause/