The Common Table Expression is a table expression that most database developers and DBAs work on. The CTE was first introduced in SQL Server 2005. The main intent of the introduction was to ease the developers with the result sets. Let’s take a look at the details of the CTE.
What is a Common Table Expression?
The Common Table
Expressions (CTEs) are a temporary result set. It is another standard form of
table expression very similar to derived tables. Additionally, Common Table
Expressions are represented in short form as CTE. While considering the CTEs,
there comes a bundle of more advantages over the standard table
expressions.
The inner query defined in the CTE must always follow all the requirements of SELECT from your table to be valid to define a table expression.
Syntax of a CTE:
Every CTE has a
WITH statement and has the below general format.
WITH <Your CTE Name>[<target_Column_list>]
AS
(
<inner_query_defining_CTE>
)
<outer_query_against_CTE>;
Example:
with CTE1 as
(
select * from sys.databases
)
select name from CTE1;
Assigning Column
Aliases in CTEs
There are two
forms of column aliases, inline and external.
Inline Alias:
Specify column alias <column_alias> right after the expression
<expression>.
Example:
with CTE1 as
(
select name, database_id as
InternalAlias from sys.databases
)
select name from CTE1;
External Alias:
Specify column alias <column_alias> right after the CTE name in the
parentheses.
Example:
with CTE1(External_Alias, ID) as
(
select name, database_id as ID from sys.databases
)
select External_Alias, ID from CTE1;
Using Arguments in
CTE
Like most database developers and administrators are much familiar with using the
parameters and arguments while writing the query, the similar applies to CTE
too. You can use arguments inside CTE.
Example:
declare @DB_ID int=1;
with CTE1(External_Alias, ID) as
(
select name, database_id as ID from sys.databases where
database_id=@DB_ID
)
select External_Alias, ID from CTE1;
Defining Multiple
CTEs
The option of
defining multiple CTEs comes with a great advantage. And one of them is to avoid
the query nesting that usually happens with the derived tables.
Example:
with CTE1(External_Alias, ID) as
(
select name, database_id as ID from sys.databases
),
CTE2 AS
(
Select name as database_name from sys.databases
)
select CTE1.External_Alias, CTE1.ID, CTE2.database_name from CTE1
inner join
CTE2
on
CTE1.External_Alias=CTE2.database_name;
Multiple
References in CTEs
In most cases, we
tend to reference the CTE only once. But we can have multiple references to the
same CTE and it comes with added advantages. Defining a single CTE and
referencing the same CTE multiple times from the FROM clause of the outer query
avoids multiple derivatives from the user tables.
This approach significantly
increases the performance as it is considered a “modular approach” verse the “derived
tables”.
Example:
with CTE1 as
(
select name, database_id as ID from sys.databases
)
select a.name, b.ID from CTE1
a
left outer join CTE1
b
on
a.ID=b.ID;
From our example, we
have declared just one CTE by the name CTE1 and referenced the CTE1 twice in the
outer FROM clause. This is logically equivalent to deriving the data from two
different derived tables. Additionally, this approach can give us a clearer picture
and lessen the errors.
