Derived Tables Vs Common Table Expressions

T-SQL supports different types of table expressions,

Views
Functions
Derived Tables (DT)
Common Table Expressions (CTE)

We will have separate articles on functions and views. But today’s post is related to Derived Table and Common Table Expression and their differences.

By the way, what is table expression?

Table expression - is a special type of named query which allows us to build relational-oriented queries. You create a special query, rename it, and can use it in multiple types ( at least as a view and a function)

You can wrap your query into views and functions, store them as an object in the database and call them as a user-defined table which can have an argument (function) or without it.

But unfortunately, the above sentence is not correct for DT and CTE. T-SQL provides your to wrap your query into Derived table and Common Table Expressions but storing them directly as a separate object is not possible.

Why do we need to use Derived Table and Common Table Expression?

When working with SELECT, we usually apply special searching/filtering to our queries. But in a few cases, it is not possible to do it.

SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD

Let’s try to filter numbers that are less than 200.

SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD
WHERE rownum < 200

As we know from this article, every statement has its logical execution number and Where executes before SELECT and defined alias in SELECT can’t be detected in WHERE. How about using expression directly:

SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD
WHERE Row_number() over(order by SalesOrderDetailId) < 200

It is also not possible because Windowed functions can only appear in the SELECT and ORDER clauses. (read error message)

So, how we can filter numbers?

Except for using views and functions there are 2 more options,

Derived Tables
Common Table Expressions

2 types of writing Derived tables,

SELECT * FROM
(
SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD) AS DT
WHERE DT.rownum < 200


--second version of writing derived tables

SELECT * FROM
(
SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD) AS DT(RowNumber, SalesOrderID, CTrackingNumber)
WHERE DT.RowNumber < 200

Now, it is possible to wrap and filters our query. When comparing with Subqueries, Derived tables allow us to wrap the entire query but in subqueries, the inner query “lived” in the WHERE clause.

There are 2 “but”s when using derived tables:

It is really hard to refer from one derived table to another when building complex queries:

SELECT ...
FROM (SELECT 
    FROM (SELECT ...
	    FROM T1
			WHERE ...) AS MyDT1
		 WHERE ....) AS MyDT2
	WHERE ....

If you want to combine multiple instances of the same derived table, it will not be possible.

SELECT * FROM
 (
SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD) 
AS DT(RowNumber, SalesOrderID, CTrackingNumber)
INNER JOIN DT as DT2
ON DT.RowNumber = DT2.RowNumber

The problem here is related to the same-time execution of expression in the T-SQL lifetime.

To write it correctly, you need to type DT twice.

SELECT * FROM
 (
SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD) 
AS DT(RowNumber, SalesOrderID, CTrackingNumber)
INNER JOIN (SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD) 
AS DT2(RowNumber, SalesOrderID, CTrackingNumber) 
ON DT.RowNumber = DT2.RowNumber

From the usage point of view, CTE does the same thing DT does. We can wrap and filter, join, and order our queries like in DT. But you can think about it as version 2.0 of Derived Tables.

WITH CTE
AS(
SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD)
SELECT * FROM CTE
WHERE rownum < 200;

What are the advantages of using Common Table Expressions?

It is really easy to refer from one CTE to another,

WITH CTE
AS(
SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD)
,
CTE2 AS --from CTE2 we referred to CTE
(SELECT * FROM CTE)
SELECT * FROM CTE2;

If you want to combine multiple instances of the same expressions, it is also easy to do it with CTE.

WITH CTE
AS(
SELECT 
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD)
SELECT * FROM CTE
INNER JOIN CTE as CTE2
ON CTE.rownum = CTE2.rownum

Writing recursive queries with CTE:

CREATE TABLE [dbo].[RecursiveTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Level] [int] NULL,
	[SubLevel] [int] NULL,
	[Name] [nvarchar](30) NOT NULL,
	[Position] [varchar](30) NULL
) ON [PRIMARY]
GO

WITH CTE
AS
(SELECT Id, Level, SubLevel, Name, Position 
FROM Rekursiya WHERE Id = 1
UNION ALL
SELECT R.Id, R.Level, R.SubLevel, R.Name, R.Position
FROM CTE
INNER JOIN RecursiveTable AS R
ON R.SubLevel = CTE.Level)
SELECT * FROM CTE;

Working with PIVOT and Unpivot ( I’ll write a separate article about it )