Microsoft documentation defines Common Table Expression thus,
"A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
It is unlike a temporary table (persisted) during a connection or a view persisted in the database. CTE is only in memory.
We will consider CTE's many use case scenarios in later posts and the objective of this post is to give a simple example using the Northwind database.
A CTE should have the following:
After a CTE is defined it can be referenced like a table or view and you can do all of the following statements:
Now let me define CTE using the Employees table in Northwind. The example is just to show the syntax as the result can be obtained from a single query without using CTE.
USE Northwind
Go
---define the common table expression
WITH NEmp_CTE (FirstName,LastName,CITY) /*Name: Nemp_CTE, Column List:FirstName, LastName,City*/
AS
---define the CTE query
(SELECT FirstName,LastName,City
from Employees
where City in ('Tacoma','London')
)
---Use it in a Select Query
SELECT LastName,FirstName,CITY
From NEmp_CTE
where LastName <>'Buchanan'
go
The response to this query is:
"A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
It is unlike a temporary table (persisted) during a connection or a view persisted in the database. CTE is only in memory.
We will consider CTE's many use case scenarios in later posts and the objective of this post is to give a simple example using the Northwind database.
A CTE should have the following:
- CTE with a CTE Expression name
- An Optional Column list
- A query defining the CTE
After a CTE is defined it can be referenced like a table or view and you can do all of the following statements:
- Select
- Insert
- Update
- Delete
Now let me define CTE using the Employees table in Northwind. The example is just to show the syntax as the result can be obtained from a single query without using CTE.
USE Northwind
Go
---define the common table expression
WITH NEmp_CTE (FirstName,LastName,CITY) /*Name: Nemp_CTE, Column List:FirstName, LastName,City*/
AS
---define the CTE query
(SELECT FirstName,LastName,City
from Employees
where City in ('Tacoma','London')
)
---Use it in a Select Query
SELECT LastName,FirstName,CITY
From NEmp_CTE
where LastName <>'Buchanan'
go
The response to this query is:
Note:
The response such as the above does not really need a Common Table
Expression but the point was to show the construction of the Syntax used
for CTE. In fact, it is to make complex queries more readable.
No comments:
Post a Comment