Author - Mamta Sharma Post Views - 27 views

Common Table Expression in SQL

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
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 (
DATEDIFF(day, p.effective_date, p.expiration_date) AS date_diff
accounts a
policies p ON = p.account_id
WHEN date_diff <= 90 THEN 'approved'
ELSE 'not'
END AS policy_status
FROM PolicyData;



Leave a Reply

Your email address will not be published. Required fields are marked * cratosroyalbet betwoon grandpashabet grandpashabet giriş deneme bonusu veren siteler casino siteleri