The WITH clause in SQL is used to define a Common Table Expression (CTE), which allows you to create a temporary result set that you can reference within the subsequent SELECT, INSERT, UPDATE, or DELETE statements. CTEs can improve the readability of complex queries by breaking them down into simpler parts.
Here's the basic syntax for using the WITH clause to define a CTE:
WITH cte_name AS (
-- Your SQL query here
SELECT column1, column2, ...
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;
Steps to Use a CTE:
1. Declare the CTE:
- Use the WITH clause followed by the name of the CTE.
- Specify the CTE in parentheses that contain a valid SQL query.
2. Reference the CTE:
- After the CTE is defined, you can use it as if it were a regular table or view in your main query.
Example:
Suppose you have a table named employees and you want to calculate the average salary by department and then select those departments where the average salary exceeds $50,000.
WITH avg_salaries AS (
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
)
SELECT *
FROM avg_salaries
WHERE average_salary > 50000;
Explanation of the Example:
- CTE Definition: The CTE named avg_salaries collects the average salary per department.
- Main Query: The main query selects all columns from the avg_salaries CTE and filters for departments with an average salary greater than $50,000.
Benefits of Using CTEs:
- Improved Readability: Break down complex queries into manageable parts.
- Recursion: CTEs can be recursive, allowing you to perform hierarchical queries.
- Modularity: Define the CTE once and reference it multiple times within the primary query if needed.
Notes:
- The scope of a CTE is limited to the statement immediately following it.
- You can define multiple CTEs by separating them with commas.
Example with Multiple CTEs:
WITH sales_data AS (
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id
),
product_data AS (
SELECT product_id, product_name
FROM products
)
SELECT p.product_name, s.total_sales
FROM product_data p
JOIN sales_data s ON p.product_id = s.product_id
WHERE s.total_sales > 1000;
In this example, two CTEs (sales_data and product_data) are defined and then used in the main query to join them and filter results.