What are SQL CTE?
SQL CTE or common table expressions are temporary tables created by the result of some other query. They have their name and can be referenced in SQL statements.
They are created using WITH clause encapsulating SQL statements.
Syntax Of CTE:
WITH CTE_NAME AS (
SQL STATEMENTS
)
SQL STATEMENT;
EXAMPLE:
For Example we are going to use the EMPLOYEE table.
For Example purpose we are using this simple example.
In the example below, we use Common Table Expression(CTE) to show how user can get EMPLOYEE details where Department(DEPT_NAME) is ‘IT’ and have SALARY above 4000.
WITH CTE AS (
SELECT *
FROM EMPLOYEE
WHERE DEPT_NAME = 'IT'
) SELECT *
FROM CTE
WHERE SALARY > 4000;
In the below example, we use recursive CTE to create to CTE tables and later use them to get the final result set.
WITH CTE AS (
SELECT *
FROM EMPLOYEE
WHERE DEPT_NAME = 'IT'
), CTE2 AS (
SELECT *
FROM CTE
WHERE SALARY > 4000
) SELECT * FROM CTE2 WHERE SALARY >6000;
Thanks For Reading!
SOURCE: Microsoft T-SQL Documentation CTE
READ MORE:
SQL Window Function
SQL SubQueries
