Lesson 21. Derived Tables

Derived tables are an alternative method to CTEs. Technically, derived tables perform better than CTEs, but I prefer the readability of CTEs because the performance hit is usually negligible.

Derived Table Syntax

SELECT *
FROM (
--SQL statement that encapsulates complex business logic
) AS x

Examples

An Example Of A Derived Table

USE AdventureWorks2016

SELECT 
pwnmn.FirstName,
pwnmn.LastName,
e.EmailAddress
FROM Person.EmailAddress e
JOIN(
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE MiddleName IS NULL
) AS pwnmn
ON pwnmn.BusinessEntityID = e.BusinessEntityID