A Common Table Expression (CTE) is used in an SQL query to fetch data temporarily and show it in a single row or table.But it is temporary—once the query execution ends, the CTE gets deleted. It helps to make complex queries easy and fetches data clearly in one table. For example, if there is an employee salary table and we want to fetch the data of those employees whose salary is more than 50,000, then CTE is defined using the WITH keyword, and it shows all those employee records with a salary above 50,000 in one table.After the query execution ends, the CTE also ends.
Syntax:
WITH HighSalary AS (
SELECT employee_id, salary
FROM salaries
WHERE salary > 50000
)
SELECT e.employee_name, h.salary
FROM employees e
JOIN HighSalary h
ON e.employee_id = h.employee_id;