SQL Tutorial - Difference between CTEs and Derived Tables

In this SQL Tutorial I talk through the differences between CTEs and Derived Tables in SQL Server. CTEs actually came along to address some of the limitations of Derived Tables, when nesting Derived Tables it can be difficult to understand what the query is doing, CTEs address this by allowing you to read the SQL code from top to bottom, as Derived Tables are also declared in the FROM clause of a query, it prevents reuse, declaring CTEs outside of the final SELECT allows for reuse.
Both CTEs and Derived Tables are Table Expressions that are inlined when the query executes. CTEs offer better readability and usability over Derived Tables, however Derived Tables offer better visibility, you don't need to change anything to see intermediate query results, you just run the inner queries whereas with a CTE you need to change the query to see an intermediate step.
Remember that CTEs and Derived Tables can also be used together.
#sqltutorials #sqlserver #dataengineering #data
Code Samples:
-- create table
IF OBJECT_ID(N'dbo.CustomerSalesRaw', N'U') IS NOT NULL
DROP TABLE dbo.CustomerSalesRaw;
CREATE TABLE dbo.CustomerSalesRaw
(
Customer VARCHAR(50),
[Location] VARCHAR(50),
CustomerType VARCHAR(50),
StartDate DATE,
[Date] DATE,
Amount DECIMAL(6, 2)
);
INSERT INTO dbo.CustomerSalesRaw (Customer, [Location], CustomerType, StartDate, [Date], Amount)
VALUES
('Polly McFadden', 'United Kingdom', 'Online', '20220101', '20220601', 1000.00),
('Polly McFadden', 'United Kingdom', 'Online', '20220101', '20220601', 1000.00),
('Polly McFadden', 'United Kingdom', 'Online', '20220101', '20220601', 1000.00),
('Polly McFadden', 'United Kingdom', 'Online', '20220101', '20220516', 300.00),
('Daisy Smith', 'United Kingdom', 'Store', '20220301', '20220602', 300.00),
('Daisy Smith', 'United Kingdom', 'Store', '20220301', '20220602', 300.00),
('Paul Kirkland', 'United Kingdom', 'Online', '20220301', '20220502', 680.00),
('Paul Kirkland', 'United Kingdom', 'Online', '20220301', '20220601', 200.00),
('Betsy Jones', 'United Kingdom', 'Online', '20220301', '20220511', 720.00),
('Betsy Jones', 'United Kingdom', 'Online', '20220301', '20220511', 720.00),
('Mark Porter', 'United Kingdom', 'Store', '20220301', '20220522', 32.00),
('Mark Porter', 'United Kingdom', 'Store', '20220301', '20220522', 32.00);
-- nested derived table
SELECT
Aggr.Customer,
Detail.CustomerType,
Detail.StartDate,
Aggr.Mnth,
Aggr.Total
FROM
(
SELECT
Customer,
MONTH([Date]) AS Mnth,
SUM(Amount) AS Total
FROM
(
SELECT DISTINCT
Customer,
[Location],
CustomerType,
StartDate,
[Date],
Amount
FROM dbo.CustomerSalesRaw
) AS Clean
GROUP BY
Customer,
MONTH([Date])
) AS Aggr
INNER JOIN
(
SELECT DISTINCT
Customer,
CustomerType,
StartDate
FROM dbo.CustomerSalesRaw
) AS Detail
ON Aggr.Customer = Detail.Customer;
-- nested CTE
WITH Clean AS
(
SELECT DISTINCT
Customer,
[Location],
CustomerType,
StartDate,
[Date],
Amount
FROM dbo.CustomerSalesRaw
), Aggr AS
(
SELECT
Customer,
MONTH([Date]) AS Mnth,
SUM(Amount) AS Total
FROM Clean
GROUP BY
Customer,
MONTH([Date])
)
SELECT
A.Customer,
B.CustomerType,
B.StartDate,
A.Mnth,
A.Total
FROM Aggr AS A
INNER JOIN
(
SELECT DISTINCT
Customer,
CustomerType,
StartDate
FROM dbo.CustomerSalesRaw
) AS B
ON A.Customer = B.Customer;
-- create clean table
SELECT DISTINCT
Customer,
[Location],
CustomerType,
StartDate,
[Date],
Amount
INTO dbo.CustomerSalesClean
FROM dbo.CustomerSalesRaw;
-- reusing derived table
SELECT
[Current].Customer,
[Current].Total - Previous.Total AS [Difference]
FROM
(
SELECT
Customer,
MONTH([Date]) AS Mnth,
SUM(Amount) AS Total
FROM CustomerSalesClean
GROUP BY
Customer,
MONTH([Date])
) AS Previous
LEFT OUTER JOIN
(
SELECT
Customer,
MONTH([Date]) AS Mnth,
SUM(Amount) AS Total
FROM CustomerSalesClean
GROUP BY
Customer,
MONTH([Date])
) AS [Current]
ON Previous.Customer = [Current].Customer
AND Previous.Mnth - 1 = [Current].Mnth;
-- reusing CTE
WITH Aggr AS
(
SELECT
Customer,
MONTH([Date]) AS Mnth,
SUM(Amount) AS Total
FROM CustomerSalesClean
GROUP BY
Customer,
MONTH([Date])
)
SELECT
[Current].Customer,
[Current].Total - Previous.Total
FROM Aggr AS Previous
LEFT OUTER JOIN Aggr AS [Current]
ON Previous.Customer = [Current].Customer
AND Previous.Mnth - 1 = [Current].Mnth;