Поиск  
Always will be ready notify the world about expectations as easy as possible: job change page
Jan 2, 2023

Common Table Expression (CTE) in SQL Server

Автор:
Rajesh Gami
Источник:
Просмотров:
2770

Simplify complex joins and subqueries using SQL Server Common Table Expressions or CTEs. It also provides a way to query hierarchical data. This article provides a complete overview of CTEs, types of CTEs, benefits, drawbacks, and how to use them with SQL Server.

A Common Table Expression or CTE is a short-lived named result set created from an easy SELECT statement employed in a subsequent SELECT statement. Every SQL CTE is sort of a named query, the result of which is kept in a very virtual table (a CTE) which is referenced later within the main query.

Syntax

WITH cte_expression_name[ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

To view the CTE results, use a select query with the name of the CTE expression.

Select [Column1,Column2,Column3,Column4...] from cte_expression_name

OR

SELECT * FROM cte_expression_name

Types of CTE (Common Table Expression)

There are 2 types of CTEs,

  1. Recursive CTE. 
  2. Non-Recursive CTE.

Recursive CTE

A Recursive common table expression (CTE) could be a CTE that references itself. By doing so, the CTE repeatedly executes, and returns subsets of information, till it returns the whole result set.

A recursive CTE is beneficial in querying ranked data adore organization charts wherever one worker reports to a manager or multi-level bill of materials once a product consists of many components, and every component itself additionally consists of many different components.

We'll see how to use a CTE to create a simple recursive query that displays row numbers from 1 to 10.

First, he declared an integer variable as 'RowNo', set the default value to 1, and created our first CTE query as the expression name 'ROW_CTE'. This CTE first displays the default row number, then uses union ALL to increment the row number by 1 until the row number reaches the incremented value of 10. To view the results, use the query of your choice to view the CTE results.

Declare @RowNo int =1;
;with ROW_CTE as
   (
      SELECT @RowNo as ROWNO
        UNION ALL
      SELECT  ROWNO+1
  FROM  ROW_CTE
  WHERE RowNo < 10
    )

SELECT * FROM ROW_CTE

Output

ROWNO
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

Non-Recursive CTE

Non-Recursive CTEs are easy in which the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create an easy Non-Recursive CTE to show the row variety from 1 to 10.

As in keeping with the CTE Syntax, every CTE question will begin with a "With" observed with the aid of using the CTE Expression call with a column list.

Here we had been the usage of only one column as ROWNO. Next is the Query part, right here we write our pick-out question to be executed for our CTE. After developing our CTE question to run the CTE uses the pick out an announcement with the CTE Expression call.

;with ROW_CTE(ROWNO) as
   (
     SELECT
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM table_name
WHERE id <= 10
    )

SELECT * FROM ROW_CTE

Output:
ROWNO
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

Nested CTE (Common Table Expression)

WITH First_CTE
   AS (SELECT 1 EmpId, 'Rajesh Gami' Name)

  ,Second_CTE
   AS (SELECT EmpID, Name, 'Gujarat' State FROM First_CTE)

SELECT *   FROM Second_CTE

Похожее
22 апреля 2013 г.
Автор: Max Koverdyaev
В некоторых проектах достаточно часто возникает необходимость в хранении данных, объем которых уже нельзя назвать маленьким, но в тоже время использовать какую-либо СУБД слишком накладно из-за сложности развертывания приложения. И тут на помощь приходит такая прекрасная вещь как SQLite –...
Jun 14
Author: Sithi Asma Basheer
The Top 15 Advanced SQL commands for Data Analysis and Data Engineering. Table of index Window functions Common Table Expressions (CTEs) Recursive queries Pivot tables Analytic functions Unpivot Conditional aggregation Date functions Merge statements Case statements String functions Grouping sets...
Mar 25
Author: Henrique Siebert Domareski
Pagination allows you to retrieve a large number of records split into pages, instead of returning all the results at once. This is especially useful in scenarios where you need to retrieve a large number of records. In this article,...
Mar 20
Author: Lorenzo Uriel
Nobody likes dealing with a slow query. Besides being stressfull to wait for, it can be a huge impact in your database. Some simpler approaches can help resolve this. I want to start a series of three articles explaining about...
Написать сообщение
Тип
Почта
Имя
*Сообщение