Common Table Expression(CTE)

 

TSQL Developers sometimes want to use the same code block in more than one place when writing a query. Sometimes this case can complicate the query. Thats why we can create a Common Table Expression (CTE) for the code block to be reused. Thus, we can use this CTE like a table by giving it an alias.

You can also do the same using view. If your application will use this block of code continuously, it makes sense to create a view. But if it’s not a block of code that the application will use continuously, we can choose CTE instead of view.

In CTE, INSERT, UPDATE, and DELETE statements can also be used.

Within the CTEs, ORDER BY (available if there is a TOP statement in the query), INTO, FOR BROWSE, and OPTION are not available.

Below is a sample CTE that runs on the AdventureWorks database.

As you can see in the example above, we defined 3 columns in the brackets after the CTE name. And we read three columns in the code block in the CTE. The query will run even if we do not define these columns when defining the CTE. But identifying will be better in terms of the readability of the code.

More than one CTE can be defined in a CTE as follows. You must separate CTEs with “,” as follows, and you must use one of the following expressions when joining these two cte.

  • UNION ALL
  • UNION
  • INTERSECT
  • EXCEPT
The most commonly used form of CTEs is the recursive CTE. The Recursive CTE is self-referenced. Generally used to determine hierarchy. To make an example, let’s create a table in the name of Staff where hierarchical information is stored and add a few records.
The StaffID and ManagerID columns will be important to us. Using such a hierarchy table, we can list manager of each staff with the help of CTE as follows.

Normally,  we can find a manager of a staff with a select query, but if we need a manager’s manager information or all of his or her managers, we need to do this with a recursive CTE.

As you can see in Insert operations, the person named Kemal Sevim is at the bottom of the hierarchy in this table. In the script below, let’s write the StaffID of Kemal Sevim to the StaffID section and see all the managers. Then, write Faruk Erdem’s ID value and see the result.

As you can see, we listed the managers of Kemal Sevim respectively.

Let’s do the same for Faruk Erdem.

As you can see, when we did the same for Faruk Erdem, the value of Kemal Sevim did not return as a result. Because Kemal Sevim is not the manager of Faruk Erdem. TSQL developers mostly use Recursive CTEs for this purpose.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *