All about CTEs

A CTE example

The only problem with CTE’s, I’ve found, is that you cannot use Order By in the preceeding “With” datasets.

In this way, it’s a bit different to temporary-real tables. This Lack-of-Order-By can make it less handy when dealing with large sets of data, when I’ve wanted to make an Order-By while I write into a table, so I can be sure the data is sorted the way I want it to be. This is so that it can be joined to more efficiently after – Like using an index.

— Creating the demo tables and data…

Create Table [dbo].[tblPerson](

[PersonID] [int] Null,

[Firstname] [nvarchar](50) Null,

[Surname] [nvarchar](50) Null

) On [Primary]

Create Table [dbo].[tblCountry](

[CountryID] [int] Null,

[CountryName] [nvarchar](50) Null

) On [Primary]

Create Table [dbo].[tblWorkingInCountry](

[WorkingInCountry] [int] Null,

[PersonID] [int] Null,

[CountryID] [int] Null

) On [Primary]

— tblPerson

Insert dbo.tblPerson (PersonID, Firstname, Surname) Values (1, ‘May’, ‘Day’)

Insert dbo.tblPerson (PersonID, Firstname, Surname) Values (2, ‘Frank’, ‘Holiday’)

Insert dbo.tblPerson (PersonID, Firstname, Surname) Values (3, ‘Chris’, ‘Newark’)

— tblCountry

Insert dbo.tblCountry (CountryID, CountryName) Values (1, ‘France’)

Insert dbo.tblCountry (CountryID, CountryName) Values (2, ‘Canada’)

Insert dbo.tblCountry (CountryID, CountryName) Values (3, ‘Germany’)

Insert dbo.tblCountry (CountryID, CountryName) Values (4, ‘Mexico’)

— tblWorkingInCountry

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (1, 1, 2)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (2, 1, 4)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (3, 2, 1)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (4, 3, 2)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (5, 3, 3)

Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (6, 3, 4)

— The example…

With CTE1 As — For Person

(

Select

*

From

tblPerson

)

,

CTE2 As — For Working in Country

(

Select

*

From

tblWorkingInCountry

)

,

CTE3 As — For Country

(

Select

*

From

tblCountry

)

Select

*

From

CTE1

Inner Join CTE2 On CTE1.PersonID = CTE2.PersonID

Inner Join CTE3 On CTE2.CountryID = CTE3.CountryID

Advertisements