What is CTE?
CTE stands for Common Table Expression in SQL. It’s a temporary result set that you can reference within SELECT, INSERT, UPDATE, or DELETE statements. CTEs provide a way to simplify complex queries and make SQL queries more readable.
A CTE is defined using the WITH keyword.
The syntax can vary based on the type of DBMS you are using; I’m explaining it in MSSQL.
The basic syntax is:
WITH cte_name (column1, column2, ...) AS (
-- CTE query definition
SELECT column1, column2, ...
FROM your_table
WHERE your_conditions
)
-- Main query using the CTE
SELECT *
FROM cte_name;
The number of parameters in CTE should be the same as mentioned in the select list of its definition.
Example –
WITH PolicyData AS (
SELECT
a.id,
Name,
p.policy_number,
DATEDIFF(day, p.effective_date, p.expiration_date) AS date_diff
FROM
accounts a
JOIN
policies p ON a.id = p.account_id
)
SELECT *,
CASE
WHEN date_diff <= 90 THEN 'approved'
ELSE 'not'
END AS policy_status
FROM PolicyData;
