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