รู้จักกับ Common Table Expressions หรือ CTEs

รู้จักกับ Common Table Expressions หรือ CTEs

Common Table Expressions หรือ CTEs ถูกกล่าวถึงในมาตรฐาน ANSI ปี 1999

โดย Hierarchical queries สำหรับ Microsoft SQL Server นั้นจะเรียกว่า CTE
ส่วนใน Oracle จะเรียกว่า Recursive Subquery Factoring
ซึ่งอาจมี Syntax ที่ต่างกันออกไปบ้างในแต่ละผลิตภัณฑ์ 
ทั้งนี้ สามารถอ่านเพิ่มเติมได้จาก Wikipedia  https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
สำหรับบทความนี้ จะกล่าวถึง CTEs ที่ใช้ใน Microsoft SQL Server เท่านั้น
CTEs ถือเป็นนิพจน์ตาราง (Table Expression) แบบหนึ่ง
นิพจน์ตาราง คือ นิพจน์ที่ทำหน้าที่เสมือนตารางได้
สังเกตว่าอะไรที่สามารถประกาศได้ในประโยค FROM ของคำสั่ง SELECT ได้
นั่นก็คือ นิพจน์ตาราง ซึ่งมีทั้งที่สามารถจัดเก็บลงในฐานข้อมูลได้ และจัดเก็บไม่ได้

นิพจน์ตารางที่จัดเก็บเป็น Object ลงในฐานข้อมูล

• Views

• Table-Valued Functions (หรือ Parameterized Views ในกรณี Oracle)

ผู้อ่านน่าจะคุ้นเคยกับ View เป็นอย่างดี ผู้เขียนจะอธิบายคร่าว ๆ เกี่ยวกับ View ดังนี้
หากผู้เขียนสร้างคำสั่ง SELECT ของตนเองขึ้นมา อาทิเช่น

    USE AdventureWorks;
  GO
  SELECT
        E.BusinessEntityID as EmpID
  ,      P.FirstName
  ,      P.MiddleName
  ,      P.LastName
  ,      E.BirthDate
  ,      E.HireDate
  ,      E.Gender
  ,      E.MaritalStatus
  ,      A.AddressLine1
  ,      A.AddressLine2
  ,      A.City
  ,      SP.StateProvinceCode as State
  ,      SP.CountryRegionCode as Country
  FROM HumanResources.Employee as E
  INNER JOIN Person.Person as P
  ON E.BusinessEntityID=P.BusinessEntityID
  INNER JOIN Person.BusinessEntityAddress as BA
  ON P.BusinessEntityID=BA.BusinessEntityID
  INNER JOIN Person.Address as A
  ON BA.AddressID=A.AddressID
  INNER JOIN Person.StateProvince as SP
  ON A.StateProvinceID=SP.StateProvinceID;

ผลลัพธ์ที่ได้จากคำสั่ง SELECT นี้คือ

จากนั้นผู้เขียนต้องการจะสร้างคำสั่ง SELECT นี้ให้กลายเป็น View
(ก่อนอื่นจะต้องมีสิทธิในการสร้าง View เสียก่อน อาจได้รับมาจาก Fixed Database Role ชื่อว่า db_ddladmin ก็ได้)
ผู้เขียนก็เพียงแค่เพิ่มคำสั่ง CREATE VIEW เหนือคำสั่ง SELECT 
โดย View ที่สร้างชื่อว่า vEmployees

เพียงเท่านี้ก็จะเป็นการสร้าง View แล้วดังแสดง

        USE AdventureWorks;
    GO
    CREATE VIEW HumanResources.vEmployees
    as
    SELECT
    E.BusinessEntityID as EmpID
    ,      P.FirstName
    ,      P.MiddleName
    ,      P.LastName
    ,      E.BirthDate
    ,      E.HireDate
    ,      E.Gender
    ,      E.MaritalStatus
    ,      A.AddressLine1
    ,      A.AddressLine2
    ,      A.City
    ,      SP.StateProvinceCode as State
    ,      SP.CountryRegionCode as Country
    FROM HumanResources.Employee as E
    INNER JOIN Person.Person as P
    ON E.BusinessEntityID=P.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress as BA
    ON P.BusinessEntityID=BA.BusinessEntityID
    INNER JOIN Person.Address as A
    ON BA.AddressID=A.AddressID
    INNER JOIN Person.StateProvince as SP
    ON A.StateProvinceID=SP.StateProvinceID;

เมื่อสร้าง View เสร็จเรียบร้อยแล้ว View ดังกล่าวจะถูกจัดเก็บลงในฐานข้อมูล เราสามารถจัดการกับ View อาทิการให้สิทธิ์การเข้าถึง ผู้ใช้ที่มีสิทธิ์ก็จะสามารถเรียกใช้ View ได้
ด้วยการ Select ข้อมูลจาก View ดังแสดง

        USE AdventureWorks;
    GO
    SELECT
    *
    FROM HumanResources.vEmployees;

ผลลัพธ์ที่ได้จากคำสั่ง SELECT นี้คือ

สรุปได้ว่า View ได้กลายเป็น Object ในฐานข้อมูลเรียบร้อยแล้ว ผู้ใช้คนใดที่มีสิทธิ์ก็เรียกใช้ได้

ส่วนนิพจน์ตารางแบบจัดเก็บลงฐานข้อมูลอีกอย่างหนึ่งคือ Table-Valued Functions หรือ TVFs นั้น
ผู้เขียนขอยกตัวอย่างอย่างง่ายผ่าน Inline TVF หรือ TVF ที่จบในหนึ่งบรรทัดดังแสดง

            USE AdventureWorks;
      GO
      CREATE FUNCTION HumanResources.udfEmployees (@pamCountry as nchar(2)) RETURNS TABLE
      as
      RETURN
      SELECT
      E.BusinessEntityID as EmpID
      ,      P.FirstName
      ,      P.MiddleName
      ,      P.LastName
      ,      E.BirthDate
      ,      E.HireDate
      ,      E.Gender
      ,      E.MaritalStatus
      ,      A.AddressLine1
      ,      A.AddressLine2
      ,      A.City
      ,      SP.StateProvinceCode as State
      ,      SP.CountryRegionCode as Country
      FROM HumanResources.Employee as E
      INNER JOIN Person.Person as P
      ON E.BusinessEntityID=P.BusinessEntityID
      INNER JOIN Person.BusinessEntityAddress as BA
      ON P.BusinessEntityID=BA.BusinessEntityID
      INNER JOIN Person.Address as A
      ON BA.AddressID=A.AddressID
      INNER JOIN Person.StateProvince as SP
      ON A.StateProvinceID=SP.StateProvinceID
      WHERE SP.CountryRegionCode=@pamCountry;
    

ที่เรียกว่า Table-Valued Function ก็เพราะเป็นฟังก์ชั่นที่คืนค่าออกมาเป็นตาราง
ซึ่งสามารถนำไปประกาศหลังประโยค FROM ในคำสั่ง SELECT ได้จึงถือว่าเป็นนิพจน์ตาราง ดังแสดง

          USE AdventureWorks;
    GO
    SELECT
    *
    FROM HumanResources.udfEmployees('GB');
  

ผลลัพธ์ที่ได้จากคำสั่ง SELECT นี้คือ

จะเห็นว่าเมื่อสร้างฟังก์ชันด้วยคำสั่ง CREATE FUNCTION ฟังก์ชันดังกล่าวจะถูกจัดเก็บลงฐานข้อมูล
และสามารถให้สิทธิ์การเข้าถึงฟังก์ชันดังกล่าวแก่ผู้ใช้ได้ และผู้ใช้ที่มีสิทธิ์ก็สามารถเรียกใช้ฟังก์ชันดังกล่าวได้ เช่นเดียวกันกับ VIEW

แต่ที่ต่างกันก็คือ เราสามารถส่งพารามิเตอร์ เข้าไปข้างในฟังก์ชันเพื่อกรองข้อมูลตามพารามิเตอร์ที่ส่งเข้าไป ทำให้เกิดความ Dynamic มากขึ้น
ซึ่งจากตัวอย่าง จะกรองข้อมูลให้แสดงเฉพาะ Record ที่ Country เป็น GB เท่านั้น

ใน Oracle ถึงเรียกสิ่งนี้ว่า Parameterized View หรือ View ที่สามารถส่งพารามิเตอร์ได้นั่นเอง

นิพจน์ตารางที่ไม่จัดเก็บลงฐานข้อมูล

• Derived Tables

• Common Table Expressions หรือ CTEs

ผู้เขียนขอยกตัวอย่าง Derived Tables ให้ดูก่อนเพื่อจะเปรียบเทียบกับ CTEs ได้ง่ายขึ้น ดังคำสั่งต่อไปนี้

                USE AdventureWorks;
      GO
      SELECT
            OH.PurchaseOrderNumber
      ,      OH.OrderDate
      ,      C.CustomerName
      ,      S.SaleName
      ,      OH.TotalDue as Total
      FROM Sales.SalesOrderHeader as OH
      INNER JOIN    (
                          SELECT
                                SP.BusinessEntityID as SalesPersonID
                          ,      CONCAT (      FirstName
                                              ,      ' '
                                              ,      ISNULL(MiddleName+' ',' ')
                                              ,      LastName
                                              ) as SaleName
                          FROM Sales.SalesPerson as SP
                          INNER JOIN Person.Person as P
                          ON SP.BusinessEntityID=P.BusinessEntityID
                          ) as S
      ON OH.SalesPersonID=S.SalesPersonID
      INNER JOIN    (
                          SELECT
                                C.CustomerID
                          ,      CONCAT (      FirstName
                                              ,      ' '
                                              ,      ISNULL(MiddleName+' ',' ')
                                              ,      LastName
                                              ) as CustomerName
                          FROM Sales.Customer as C
                          INNER JOIN Person.Person as P
                          ON C.PersonID=P.BusinessEntityID
                          ) as C
      ON OH.CustomerID=C.CustomerID;
    

มีหลายคนมักเข้าใจว่า Derived Tables นั้นคือ Sub-Queries
แต่อันที่จริงแล้วเป็นเพียงนิพจน์ตารางเท่านั้น
สังเกตจากการที่ คำสั่ง SELECT ที่อยู่ภายในวงเล็บนั้นอยู่หลังประโยค FROM
(ต้องตั้งชื่อเล่น (Alias) ให้กับ SELECT ในวงเล็บเสมอ)
ผลลัพธ์ที่ได้จากคำสั่ง SELECT นี้คือ

ผู้เขียนจะทำการแปลง Derived Table นี้ให้กลายเป็น CTE ดังแสดง

              USE AdventureWorks;
    GO
    SELECT
          OH.PurchaseOrderNumber
    ,      OH.OrderDate
    ,      C.CustomerName
    ,      S.SaleName
    ,      OH.TotalDue as Total
    FROM Sales.SalesOrderHeader as OH
    INNER JOIN    (
                        SELECT
                              SP.BusinessEntityID as SalesPersonID
                        ,      CONCAT (      FirstName
                                            ,      ' '
                                            ,      ISNULL(MiddleName+' ',' ')
                                            ,      LastName
                                            ) as SaleName
                        FROM Sales.SalesPerson as SP
                        INNER JOIN Person.Person as P
                        ON SP.BusinessEntityID=P.BusinessEntityID
                        ) as S
    ON OH.SalesPersonID=S.SalesPersonID
    INNER JOIN    (
                        SELECT
                              C.CustomerID
                        ,      CONCAT (      FirstName
                                            ,      ' '
                                            ,      ISNULL(MiddleName+' ',' ')
                                            ,      LastName
                                            ) as CustomerName
                        FROM Sales.Customer as C
                        INNER JOIN Person.Person as P
                        ON C.PersonID=P.BusinessEntityID
                        ) as C
    ON OH.CustomerID=C.CustomerID;
  

และผลลัพธ์ที่ได้จากคำสั่ง SELECT นี้  คือ

ซึ่งผลลัพธ์ที่ได้จาก CTE ไม่ต่างจาก Derived Table
จะเห็นว่าเราสามารถแปลง Derived  Table ให้เป็น CTE ได้
โดยการทำเอา Derived Table ไปประกาศหลังประโยค WITH แทน
ซึ่งสามารถมี CTE กี่นิพจน์ก็ได้คั่นด้วยเครื่องหมาย (,) แต่เวลานำมาใช้ต้องใช้ใน SELECT หลักเพียงครั้งเดียว
ข้อดีก็น่าจะเป็นการไล่อ่าน Script ได้ง่ายสะอาดตา และ สามารถอ้างถึง CTE เดียวกันได้หลายครั้งใน SELECT หลัก
แต่ถ้าเป็น Derived Table อ้างได้เพียงครั้งเดียว หากต้องการอ้างซ้ำต้องสร้างแบบเดิมขึ้นใหม่ทั้งหมด
หากผู้อ่านยังไม่เข้าใจลองดู Script ทั้งสองแบบดังนี้
กรณี Derived Table 

            USE AdventureWorks;
  GO
  SELECT
        S1.FirstName
  ,      S2.LastName
  FROM   (
               SELECT
                      FirstName
               ,      LastName
               FROM Sales.SalesPerson as SP
               INNER JOIN Person.Person as P
               ON SP.BusinessEntityID=P.BusinessEntityID
               ) S1
  CROSS JOIN
               (
               SELECT
                      FirstName
               ,      LastName
               FROM Sales.SalesPerson as SP
               INNER JOIN Person.Person as P
               ON SP.BusinessEntityID=P.BusinessEntityID
               ) S2; 

แต่หากเป็นกรณี CTE

          USE AdventureWorks;
  GO
  WITH Sales as
  (
  SELECT
  FirstName
  ,      LastName
  FROM Sales.SalesPerson as SP
  INNER JOIN Person.Person as P
  ON SP.BusinessEntityID=P.BusinessEntityID
  )
  SELECT
  S1.FirstName
  ,      S2.LastName
  FROM Sales as S1
  CROSS JOIN Sales as S2;

ตัวอย่างดังกล่าวคงพอช่วยให้ผู้อ่านเข้าใจขอดีของ CTEs ขึ้นมาบ้าง
ส่วนรายละเอียดปลีกย่อยอื่น ๆ ผู้อ่านสามารถพบได้ในหลักสูตร Microsoft SQL Server Advanced Query ของทาง 9Expert ครับ

บทความโดย

อาจารย์ภัคพงศ์ กฤตวัฒน์

วิทยากรดูแลและออกแบบหลักสูตร

กลุ่มวิชา SQL Server/Window Server