รู้จักกับ 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 ครับ

สามารถดูเนื้อหาหลักสูตร ได้ที่

https://www.9experttraining.com/microsoft-sql-server-database-queries-advanced-training-course

บทความโดย

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

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

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