Using Common Table Expression (CTE) in SQL Server

Hello to everyone,

In this article, I will talk about the use of Common Table Expression, or CTE, in a basic sense.

What is Common Table Expression?

Expressions that allow one or more use of temporary results obtained at the time of execution of a query.

1. Common Table Expression was included with SQL Server’ 2005 version.
2. Common Table Expression is not a Table or View.
3. Common Table Expressions do not contain any data such as temporary and permanent tables.
4. Common Table Expressions are defined with the word WITH.
5. The operation of Common Table Expressions depends on the external query. Common Table Expression expires when the outer query expires.
6. Requires names for each of the columns in Common Table Expression’s SELECT statement and also for the table expression.
7. In Common Table Expressions, aliases can be defined for columns inline or externally.
8. Common Table Expressions can be used multiple times with a definition in the same query.
9. Multiple Common Table Expressions can be defined in the same WITH statement.
10. Common Table Expressions support iterative operation in which the expression is defined within itself.
11. The most important feature of Common Table Expressions is that they can call themselves and can be run more than once from the same query. That’s why it is used in Recursive (Self-recursive) operations.
12. Common Table Expressions are different from TABLE and VIEW structures, with PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT. constraints are not supported.
13. Common Table Expressions can be used not only in SELECT but also in INSERT, UPDATE and DELETE structures.
14. Common Table Expressions are highly legible.
15. Common Table Expressions do not have their own index. They use the indexes of the tables in their content.
16. The results of Common Table Expressions are not stored.
17. Common Table Expressions are like VIEW, TEMP TABLE and TABLE-VALUED VARIABLES, but there is no need to define fields at all.

Where is Common Table Expression Used?

1. Common Table Expressions can be used for complex tasks such as comparing information content in different tables.
2. Common Table Expressions can be used for cumulative calculation of quantities in the bill of materials used in production.
3. They can be used in complex reporting processes.

Common Table Expression Example

Here, I will try to explain it to you by making a simple Common Table Expression example. I made my transactions on the Northwind database. You can also try it in your own database.

WITH CTENameHere
AS (SELECT *
    FROM dbo.Categories)
SELECT *
FROM CTENameHere;

When you run the query, you will see the following result.

In the example above, I wanted to write the Common Table Expression expression in its most basic and raw form, without writing any column expressions.

Here we give a name to the Common Table Expression after the WITH statement, here is our CTE, I gave a name here, then I put AS and opened my parenthesis, I wrote my query.

At the bottom, we called our Common Table Expression, which we created in the SELECT * FROM CTENameHere here section.

Well, if we wanted to use Common Table Expression by selecting a column in the table, how would we use it? We would use it as follows.

WITH CTENameHere
AS (SELECT CategoryName
    FROM dbo.Categories)
SELECT *
FROM CTENameHere;

When you run the query, you will see the following result.

As you can see, we have brought a single column.

You can also use it by specifying a column name, as in the Common Table Expression example below, or you can see expressions like this when examining the codes written by others.

WITH CTENameHere (CategoryExampleName, DescriptionExample)
AS (SELECT CategoryName,
           Description
    FROM dbo.Categories)
SELECT *
FROM CTENameHere;

When you run the query, you will see the following result.

When you run the code above, you should see English column names appear.

After naming and defining the Common Table Expression, you should open parentheses and give the names we want to the columns we want to appear, and then specify these columns in the query after  CTEAnameHere(CategoryName, Description). Otherwise, it will give the following error.

WITH CTENameHere (CategoryExampleName, DescriptionExample) --Misuse Example
AS (SELECT *
    FROM dbo.Categories)
SELECT *
FROM CTENameHere;

When you run the query, you will see the following result.

We got the error because we wrote SELECT * FROM dbo.Categories. If we wrote SELECT CategoryName,Description FROM dbo.Categories, we would not get an error.

I think these are the most basic things to know about Common Table Expression. I will make more detailed examples in the future.

Good luck to everyone in business and life.