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
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;

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

fiteesports.com rivierarw.com cratosroyalbet betwoon grandpashabet grandpashabet giriş deneme bonusu veren siteler casino siteleri