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:
- 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
It can also be used in defining a view by providing the columns.
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.
