CTE in SQL
Introduction
In this blog post I will discuss two types of functions on SQL. The first type is a CTE function or a common table expression. And the second type of function is a window function. SQL is a computer programming language that is used to manage data that is held in relational databases. It is most beneficial to use this when you have structured data. To make this simpler, sometimes it is easy to think of CTE’s as a different form of subqueries.
CTE
A CTE is a temporary named set or a in other words, a temporary named table.This table is created in the beginning of the query and can only be referenced in that specific query. It is defined within the execution scope of a SELECT, UPDATE INSERT OR DELETE statements.
You define a CTE with a WITH clause before the SELECT, UPDATE INSERT OR DELETE statements. Then you can aggregate the CTE statements by adding a comma. Once this is done, the CTE is treated as any other table and can be referred to as any other table. But, it can only be referenced directly after the WITH clause. It is important to remember that the CTE result can only be referenced in that query statement and not others.
Rules
This next section will go over the rules to follow when writing a CTE query.
- The SQL query CTE must follow a WITH statement and be followed by a SELECT, UPDATE INSERT OR DELETE statement.
- The SELECT, UPDATE INSERT OR DELETE statement must use at most all of the CTE columns, but can’t use additional columns.
- It can back reference, but it cannot forward reference. Meaning, you can reference a previously defined CTE, but you cannot reference a CTE that will soon be referenced.
Syntax
WITH Expression_Name (Column_Name1, ColumnName2,…..ColumnNameN)
AS
(CTE Definition) — Write a query
SELECT Column_Name1, ColumnName2,…..ColumnNameN
FROM Expression_Name — Or, CTE Name
Arguments in CTE
- Expression_Name — this is a unique name for the “table” that you want to create. This has to be different than any other CTE name defined in the same WITH clause.
- Column_Name1…Column_NameN — these need to be unique as well. The number of column names that are specified here need to match the result set of the CTE definition.
- CTE definition — the place where you write your query.
CTE Example
WITH Total_Sale (Profession, Qualification, Income, Sale)
AS
(
SELECT [Occupation]
,[Education]
,SUM([YearlyIncome]) AS Income
,SUM([Sales]) AS Sale
FROM [Employee Table]
GROUP BY [Education], [Occupation]
)
SELECT * FROM Total_Sale
Analysis
As you can see in this query that you can perform aggregations within the query and also use the GROUP BY clause.
Multiple CTE’s
WITH Highest_Record AS
(
SELECT Id
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,ROW_NUMBER() OVER (PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC) AS Number
,[Sales]
,[HireDate]
FROM [Employee Table]
),
— Use Comma to Seperate multiple CTEs — Highest_Record and Department_Name
Department_Names AS
(
SELECT *
FROM [Department]
)
— We are using LEFT Join to join both the CTEs
SELECT hr.[FirstName] + ‘ ‘ + hr.[LastName] AS [Full Name]
,hr.[Education]
,hr.[Occupation]
,dept.DepartmentName
,hr.[YearlyIncome]
,hr.[Sales]
,hr.[HireDate]
,hr.Number AS [Rank]
FROM Highest_Record AS hr
LEFT JOIN
Department_Names AS dept ON
hr.Id = dept.id
Why use a CTE?
In most cases you can just use a subquery instead of a CTE but there are many advantages to using a CTE.
Organization: Writing long queries is often messy and it is much better practice to use CTE’s.
- You can have a name for each CTE in a query and you cannot have a name for each subquery
- Human logic: you start with the temporary result set and then refer to it later on in the query. When you write a subquery you begin with the main query and end with the subquery
Readability: The person that writes the code is never the only person to look at the code. This is important when taking into account how your code looks to other people who need to understand it. It is also important for people to write clean code so when they look back at it they know exactly what it means.
