CTE(Common Table Expressions) in SQL
CTE( Common Table Expressions) was first introduced with SQL 2005. It is the best replacement for Views.
There are times when we might require to work on small data sets that is a part of large database. For example, we might have a huge sized table that contains data for all transactions that a company has made, but we require current month's data only and apply some grouping or filters on that particular data of current month. Typically views are used to do the task. But, views sometimes over occupy our resources as views are stored at system level. CTE is the best substitute for Views. It is also helpful when we don't have access to create object in a database.
Common Table Expressions or CTE are more readable form of derived table. CTE can be declared once and can be used in multiple queries. CTE can be recursive and non-recursive (we will cover this topic in next post)
There are times when we might require to work on small data sets that is a part of large database. For example, we might have a huge sized table that contains data for all transactions that a company has made, but we require current month's data only and apply some grouping or filters on that particular data of current month. Typically views are used to do the task. But, views sometimes over occupy our resources as views are stored at system level. CTE is the best substitute for Views. It is also helpful when we don't have access to create object in a database.
Common Table Expressions or CTE are more readable form of derived table. CTE can be declared once and can be used in multiple queries. CTE can be recursive and non-recursive (we will cover this topic in next post)
Common Table Expression Syntax
A Common Table Expression contains three core parts:
A Common Table Expression contains three core parts:
- The CTE name (this is what follows the
WITH
keyword) - The column list (optional)
- The query (appears within parentheses after the
AS
keyword)
EXAMPLE:
WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
SELECT
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)
(
SELECT
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)
SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC
The CTE is part of the subsequent statement only.The subsequent statement can be a single SELECT/INSERT/UPDATE/DELETE, or a compound (with UNION, INTERSECT etc). So if we modify our above example and include a query between CTE creation query(WITH) and the query that refer the CTE.(like below)
WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
SELECT
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)
(
SELECT
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)
SELECT * FROM Products
SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC
Here, we will see that when the query that refers to CTE(ProductAndCategoryNamesOverTenDollars) is executed we will get an error "Invalid object name 'ProductAndCategoryNamesOverTenDollars'"
elazığ
ReplyDeletegümüşhane
kilis
siirt
sakarya
PJİ
denizli evden eve nakliyat
ReplyDeletekars evden eve nakliyat
çorum evden eve nakliyat
kars evden eve nakliyat
malatya evden eve nakliyat
K2MFVJ
A2B7A
ReplyDeleteIsparta Şehirler Arası Nakliyat
Sincan Boya Ustası
Edirne Evden Eve Nakliyat
AAX Güvenilir mi
Sincan Parke Ustası
Yenimahalle Parke Ustası
Bartın Lojistik
Düzce Parça Eşya Taşıma
Edirne Parça Eşya Taşıma
FFC96
ReplyDeleteEskişehir Evden Eve Nakliyat
buy deca durabolin
boldenone
Karaman Evden Eve Nakliyat
Çerkezköy Evden Eve Nakliyat
oxandrolone anavar
Bitlis Evden Eve Nakliyat
Çerkezköy Fayans Ustası
buy oxandrolone anavar
113B5
ReplyDeleteMith Coin Hangi Borsada
Bulut Madenciliği Nedir
Mexc Borsası Kimin
Coin Nasıl Alınır
Mexc Borsası Kimin
Youtube İzlenme Satın Al
Bitcoin Nedir
Parasız Görüntülü Sohbet
Clysterum Coin Hangi Borsada
DE2E6
ReplyDeleteLovely Coin Hangi Borsada
Bitcoin Çıkarma
Ceek Coin Hangi Borsada
Coin Para Kazanma
Coin Nasıl Kazılır
Bitranium Coin Hangi Borsada
Kripto Para Madenciliği Nedir
Youtube Beğeni Hilesi
Periscope Takipçi Hilesi