Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
TSQL Developers sometimes want to use the same code block in more than one place when writing a query. Sometimes this case can complicate the query. Thats why we can create a Common Table Expression (CTE) for the code block to be reused. Thus, we can use this CTE like a table by giving it an alias.
You can also do the same using view. If your application will use this block of code continuously, it makes sense to create a view. But if it’s not a block of code that the application will use continuously, we can choose CTE instead of view.
In CTE, INSERT, UPDATE, and DELETE statements can also be used.
Within the CTEs, ORDER BY (available if there is a TOP statement in the query), INTO, FOR BROWSE, and OPTION are not available.
Below is a sample CTE that runs on the AdventureWorks database.
1 2 3 4 5 6 7 8 9 10 11 12 | WITH CTE_Example (SalesPersonID, SalesOrderID, SalesDay) AS ( SELECT SalesPersonID, SalesOrderID, DAY(OrderDate) AS SalesDay FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesDay FROM CTE_Example GROUP BY SalesDay, SalesPersonID ORDER BY SalesDay,SalesPersonID; |
As you can see in the example above, we defined 3 columns in the brackets after the CTE name. And we read three columns in the code block in the CTE. The query will run even if we do not define these columns when defining the CTE. But identifying will be better in terms of the readability of the code.
More than one CTE can be defined in a CTE as follows. You must separate CTEs with “,” as follows, and you must use one of the following expressions when joining these two cte.
- UNION ALL
- UNION
- INTERSECT
- EXCEPT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | WITH CTE_Example (SalesPersonID, SalesOrderID, SalesDay) AS ( SELECT SalesPersonID, SalesOrderID, DAY(OrderDate) AS SalesDay FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ), CTE_Example2 (SalesPersonID, SalesOrderID, SalesDay) AS ( SELECT SalesPersonID, SalesOrderID, DAY(OrderDate) AS SalesDay FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) SELECT * FROM CTE_Example UNION SELECT * FROM CTE_Example2 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE TABLE [dbo].[Staff]( [StaffID] [smallint] NOT NULL, [Name] [nvarchar](30) NOT NULL, [Surname] [nvarchar](40) NOT NULL, [Title] [nvarchar](50) NOT NULL, [DepartmentID] [smallint] NOT NULL, [ManagerID] [int] NULL ) ON [PRIMARY] GO INSERT [dbo].[Staff] ([StaffID], [Name], [Surname], [Title], [DepartmentID], [ManagerID]) VALUES (1, N'Nurullah', N'CAKIR', N'Database Department Manager', 1, 2) GO INSERT [dbo].[Staff] ([StaffID], [Name], [Surname], [Title], [DepartmentID], [ManagerID]) VALUES (2, N'Hayriye', N'SULUGOZ', N'IT MANAGER', 1, NULL) GO INSERT [dbo].[Staff] ([StaffID], [Name], [Surname], [Title], [DepartmentID], [ManagerID]) VALUES (3, N'Faruk', N'Erdem', N'Senior Database Administrator', 1, 1) GO INSERT [dbo].[Staff] ([StaffID], [Name], [Surname], [Title], [DepartmentID], [ManagerID]) VALUES (4, N'Kemal', N'Sevim', N'Junior Database Administrator', 1, 3) |
Normally, we can find a manager of a staff with a select query, but if we need a manager’s manager information or all of his or her managers, we need to do this with a recursive CTE.
As you can see in Insert operations, the person named Kemal Sevim is at the bottom of the hierarchy in this table. In the script below, let’s write the StaffID of Kemal Sevim to the StaffID section and see all the managers. Then, write Faruk Erdem’s ID value and see the result.
1 2 3 4 5 6 7 8 9 10 | with CTE as ( select StaffID,ManagerID,Title from dbo.Staff where StaffID=4 union all select stf.StaffID,stf.ManagerID,stf.Title from dbo.Staff stf join CTE on stf.StaffID=CTE.ManagerID ) select * from CTE |
As you can see, we listed the managers of Kemal Sevim respectively.
Let’s do the same for Faruk Erdem.
As you can see, when we did the same for Faruk Erdem, the value of Kemal Sevim did not return as a result. Because Kemal Sevim is not the manager of Faruk Erdem. TSQL developers mostly use Recursive CTEs for this purpose.
