logo

TechCodeview

SQL Server의 CTE(공용 테이블 표현식)

복잡한 조인과 하위 쿼리를 더 쉽게 만들기 위해 SQL Server의 공통 테이블 표현식(CTE)을 사용하겠습니다. 또한 조직 계층과 같은 계층적 데이터를 쿼리하는 방법도 제공합니다. 이 문서에서는 CTE, CTE 유형, 장점, 단점 및 SQL Server에서 CTE를 사용하는 방법에 대한 전체 개요를 제공합니다.

SQL Server의 CTE란 무엇입니까?

CTE(Common Table Expression)는 쿼리 기간 동안에만 존재하는 일회성 결과 집합입니다. . 이를 통해 단일 SELECT, INSERT, UPDATE, DELETE, CREATE VIEW 또는 MERGE 문의 실행 범위 내에서 데이터를 참조할 수 있습니다. 결과는 어디에도 저장될 수 없고 쿼리 실행이 완료되는 즉시 손실되므로 일시적입니다. 처음에는 SQL Server 2005 버전과 함께 제공되었습니다. DBA는 항상 하위 쿼리/뷰 대신 CTE를 사용하는 것을 선호했습니다. 이는 ANSI SQL 99 표준을 따르며 SQL을 준수합니다.

SQL Server의 CTE 구문

CTE 구문에는 CTE 이름, 선택적 열 목록, CTE(공용 테이블 표현식)를 정의하는 문/쿼리가 포함됩니다. CTE를 정의한 후 이를 SELECT, INSERT, UPDATE, DELETE 및 MERGE 쿼리의 뷰로 사용할 수 있습니다.

다음은 SQL Server의 CTE 기본 구문입니다.

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;

이 구문에서는:

  • 나중에 쿼리에서 참조할 CTE 이름을 먼저 지정했습니다.
  • 다음 단계는 쉼표로 구분된 열 목록을 만드는 것입니다. 이는 CTE 정의 인수의 열 수와 쿼리의 열 수가 동일해야 함을 보장합니다. CTE 인수의 열을 정의하지 않은 경우 CTE를 정의하는 쿼리 열을 사용합니다.
  • 그런 다음 표현식 이름 뒤에 AS 키워드를 사용한 다음 결과 집합이 CTE를 채우는 SELECT 문을 정의합니다.
  • 마지막으로 SELECT, INSERT, UPDATE, DELETE, MERGE 문과 같은 쿼리에 CTE 이름을 사용하겠습니다.

CTE 쿼리 정의를 작성할 때 염두에 두어야 할 점은 다음과 같습니다. 다음 조항은 사용할 수 없습니다.

  1. TOP 절로도 사용하지 않는 한 ORDER BY
  2. 안으로
  3. 쿼리 힌트가 있는 OPTION 절
  4. 찾아보기

아래 이미지는 CTE 쿼리 정의를 표현한 것입니다.

SQL Server의 CTE

여기서 첫 번째 부분은 SQL에서 독립적으로 실행될 수 있는 SQL 쿼리가 포함된 CTE 표현식입니다. 두 번째 부분은 CTE를 사용하여 결과를 표시하는 쿼리입니다.

예

다양한 예를 사용하여 SQL Server에서 CTE가 어떻게 작동하는지 이해해 보겠습니다. 여기서는 '테이블'을 사용하겠습니다. 고객 ' 시연을 위해. 이 테이블에 다음 데이터가 포함되어 있다고 가정합니다.

SQL Server의 CTE

이 예에서 CTE 이름은 다음과 같습니다. 고객_인_뉴욕 , CTE를 정의하는 하위 쿼리는 세 개의 열을 반환합니다. 고객 이름, 이메일, 그리고 상태 . 결과적으로 CTE Customer_in_newyork는 뉴욕주에 거주하는 모든 고객을 반환합니다.

CTE Customer_in_newyork를 정의한 후 이를 참조했습니다. 선택하다 뉴욕에 있는 고객의 세부 정보를 얻기 위한 성명입니다.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork;

위의 명령문을 실행하면 다음과 같은 결과가 출력됩니다. 여기서는 결과가 뉴욕주에 있는 고객 정보만 반환하는 것을 볼 수 있습니다.

SQL Server의 CTE

다중 CTE

어떤 경우에는 여러 CTE 쿼리를 만들고 함께 결합하여 결과를 확인해야 합니다. 이 시나리오에서는 여러 CTE 개념을 사용할 수 있습니다. 여러 CTE 쿼리를 생성하고 이를 단일 문으로 병합하려면 쉼표 연산자를 사용해야 합니다. 여러 CTE를 구별하려면 ',' 쉼표 연산자 앞에 CTE 이름이 와야 합니다.

여러 CTE는 결국 함께 결합되는 복잡한 쿼리를 단순화하는 데 도움이 됩니다. 각 복잡한 부분에는 자체 CTE가 있으며, 이는 WITH 절 외부에서 참조되고 조인될 수 있습니다.

참고: 다중 CTE 정의는 UNION, UNION ALL, JOIN, INTERSECT 또는 EXCEPT를 사용하여 정의할 수 있습니다.

아래 구문은 이를 더 명확하게 설명합니다. 

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name;

예

SQL Server에서 여러 CTE가 작동하는 방식을 이해해 보겠습니다. 여기서는 위의 ''를 사용하겠습니다. 고객 ' 시연을 위한 테이블입니다.

이 예에서는 두 개의 CTE 이름을 정의했습니다. 고객_인_뉴욕 그리고 고객_캘리포니아 . 그런 다음 이러한 CTE의 하위 쿼리 결과 집합이 CTE를 채웁니다. 마지막으로 다음 위치에 있는 모든 고객을 반환하는 쿼리에 CTE 이름을 사용하겠습니다. 뉴욕 그리고 캘리포니아 주 .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California;

뉴욕주와 캘리포니아주.

SQL Server의 CTE

왜 CTE가 필요한가요?

데이터베이스 뷰 및 파생 테이블과 마찬가지로 CTE를 사용하면 복잡한 쿼리를 더 읽기 쉽고 단순하게 만들어서 더 쉽게 작성하고 관리할 수 있습니다. 복잡한 쿼리를 쿼리 재작성 시 재사용할 수 있는 간단한 블록으로 분해하여 이러한 특성을 달성할 수 있습니다.

사용 사례 중 일부는 다음과 같습니다.

  • 단일 쿼리 내에서 파생 테이블을 여러 번 정의해야 할 때 유용합니다.
  • 데이터베이스의 뷰에 대한 대안을 만들어야 할 때 유용합니다.
  • 여러 쿼리 구성 요소에 대해 동일한 계산을 여러 번 동시에 수행해야 할 때 유용합니다.
  • ROW_NUMBER(), RANK(), NTILE()과 같은 순위 함수를 사용해야 할 때 유용합니다.

그 장점 중 일부는 다음과 같습니다.

후보 키
  • CTE는 코드 유지 관리를 더 쉽게 해줍니다.
  • CTE는 코드의 가독성을 높입니다.
  • 쿼리 성능이 향상됩니다.
  • CTE를 사용하면 재귀 쿼리를 쉽게 구현할 수 있습니다.

SQL Server의 CTE 유형

SQL Server는 CTE(공통 테이블 표현식)를 두 가지 광범위한 범주로 나눕니다.

  1. 재귀적 CTE
  2. 비재귀적 CTE

재귀적 CTE

공통 테이블 표현식은 자신을 참조하는 재귀 CTE로 알려져 있습니다. 그 개념은 '로 정의되는 재귀(Recursion)에 기반을 두고 있습니다. 재귀적 프로세스나 정의를 반복적으로 적용하는 것 .' 재귀 쿼리를 실행하면 데이터의 하위 집합에 대해 반복적으로 반복됩니다. 단순히 자신을 호출하는 쿼리로 정의됩니다. 어느 시점에는 종료 조건이 있기 때문에 무한히 자신을 호출하지는 않습니다.

재귀적 CTE에는 다음이 있어야 합니다. 유니온 올 문과 재귀성을 위해 CTE 자체를 참조하는 두 번째 쿼리 정의입니다.

예

SQL Server에서 재귀 CTE가 작동하는 방식을 이해해 보겠습니다. 아래 진술을 고려하십시오. 일련의 처음 5개 홀수를 생성합니다. 

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

이 CTE는 직원 데이터의 계층 구조를 볼 수 있는 다음 출력을 제공합니다.

SQL Server의 CTE

비재귀적 CTE

자신을 참조하지 않는 공통 테이블 식을 비재귀 CTE라고 합니다. 비재귀 CTE는 재귀 개념을 사용하지 않기 때문에 간단하고 이해하기 쉽습니다. CTE 구문에 따르면 각 CTE 쿼리는 ' 와 함께 ' 절 뒤에 CTE 이름과 열 목록이 오고 그 뒤에 괄호가 있는 AS가 옵니다.

CTE의 단점

다음은 SQL Server에서 CTE를 사용할 때의 제한 사항입니다.

  • CTE 회원은 Distinct, Group By, Have, Top, Joins 등과 같은 키워드 절을 사용할 수 없습니다.
  • CTE는 재귀 멤버에 의해 한 번만 참조될 수 있습니다.
  • 저장 프로시저에서는 테이블 변수와 CTE를 매개 변수로 사용할 수 없습니다.
  • 우리는 CTE가 뷰 대신 사용될 수 있다는 것을 이미 알고 있지만 CTE는 중첩될 수 없지만 뷰는 중첩될 수 있습니다.
  • 쿼리나 하위 쿼리에 대한 바로가기일 뿐이므로 다른 쿼리에서는 재사용할 수 없습니다.
  • CTE 인수의 열 수와 쿼리의 열 수는 동일해야 합니다.