Part 48 Derived tables and common table expressions in sql server
In SQL Server, both derived tables and Common Table Expressions (CTEs) are temporary, named result sets that can be referenced within a larger query. They are used to simplify complex queries and improve readability.
A derived table is essentially a subquery defined within the FROM clause of an outer query. It returns a result set that is treated as a temporary table for the duration of the outer query's execution.
A CTE is a temporary, named result set defined with the WITH clause before the main SELECT, INSERT, UPDATE, or DELETE statement. CTEs can be referenced multiple times within the same query and can even reference other CTEs, including themselves (for recursive CTEs).