? CTE (Common Table Expression) in SQL

A CTE is a temporary named result set you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It makes complex queries easier to write and read.

? Syntax

WITH cte_name AS (    -- Your query here    SELECT column1, column2    FROM table_name    WHERE condition)SELECT *FROM cte_nameWHERE some_filter;

? Example

Suppose you have a table sales and want to find total sales per customer, then select customers with sales over 1000:

WITH total_sales AS (    SELECT customer_id, SUM(amount) AS total_amount    FROM sales    GROUP BY customer_id)SELECT customer_id, total_amountFROM total_salesWHERE total_amount > 1000;

? Features

• Makes queries modular and readable.

• Can be recursive (especially in databases like PostgreSQL, SQL Server).

• Only available within the scope of the query where defined.

? Recursive CTE Example (SQL Server/PostgreSQL)

Calculate factorial of 5:

WITH RECURSIVE factorial(n, fact) AS (    SELECT 1, 1    UNION ALL    SELECT n + 1, fact * (n + 1)    FROM factorial    WHERE n < 5)SELECT * FROM factorial;

If you want me to show examples for a specific database or use CTEs in updates or deletes, just ask!