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
rom 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 )
HostForLIFEASP.NET SQL Server 2019 Hosting
