1. What is cte in sql?
A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
The CTE is preferred to use as an alternative to a Subquery/View. A sub-query is a query within a query. It is also called an inner query or a nested query. A sub-query is usually added in a where clause of the SQL statement.
CTE Vs temp table :
CTE has its uses - when data in the CTE is small and there is strong readability improvement as with the case in recursive tables. However, its performance is certainly no better than table variables and when one is dealing with very large tables, temporary tables significantly outperform CTE.
WITH Employee_CTE (EmployeeNumber, Title)
AS
(SELECT NationalIDNumber,JobTitle FROM HumanResources.Employee)
SELECT EmployeeNumber,Title FROM Employee_CTE
// CTE To Get Nth Highest Salary using ROW_NUMBER()
with ordered_salary as
(
SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) rn FROM salary_table
)
select name, salary from ordered_salary where rn = 5