นิพจน์ตารางทั่วไปของ SQL:

มันคืออะไร เหตุใดจึงมีความสำคัญ และใช้งานอย่างไร

Common Table Expression (CTE) หรือที่เรียกว่า "แบบสอบถามด้วย" ใช้เพื่อแยกย่อยแบบสอบถามที่ซับซ้อนออกเป็นส่วนที่เรียบง่ายและจัดการได้มากขึ้น ช่วยเพิ่มความสามารถในการอ่านโค้ด SQL มันมีประโยชน์มากเมื่อเขียนแบบสอบถามที่มีแบบสอบถามย่อยที่ซับซ้อน โดยจะตั้งชื่อชั่วคราวให้กับแบบสอบถามย่อยที่ซับซ้อนก่อนที่จะใช้ในแบบสอบถามที่ใหญ่กว่า
CTE มีสองประเภท: ปกติและแบบเรียกซ้ำ (CTE แบบเรียกซ้ำไม่ครอบคลุมในบริบทนี้)

การสร้างนิพจน์ตารางทั่วไป

แบบสอบถาม CTE ถูกเขียนโดยใช้ไวยากรณ์ต่อไปนี้:

WITH cte_name (column_name) AS (query)
SELECT * FROM CTE_NAME;

พิจารณาตารางที่เรียกว่า `บุคคล`;

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | [email protected]    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | [email protected]     | Female | United Kingdom  | Plymouth    |
| 3  | Ileana       | Guerin      | [email protected]         | Female | Bulgaria        | null        |
| 4  | Walden       | Milmo       | null                        | Male   | Russia          | Ford        |
| 5  | Quincy       | Bromont     | null                        | Male   | China           | Lexus       |
| 6  | Maria        | Iddon       | [email protected]     | Female | Philippines     | Land Rover  |
| 7  | Rog          | McArdell    | [email protected]  | Male   | Poland          | null        |

ซอร์สโค้ด รวมถึงการสืบค้นสำหรับการสร้างตารางนี้ สามารถพบได้บน GitHub

https://github.com/TheDataIsaac/The-Data-Diaries-with-Isaac/blob/main/PostgreSQL/Common%20Table%20Expression/cte.sql

เราต้องการค้นหาผู้ที่มีที่อยู่อีเมล เราสามารถใช้ CTE เพื่อทำสิ่งนี้ได้ดังนี้:

-- Create A CTE named 'got_email' to select rows where the email is non-null
-- and select specific columns from the 'got_email' cte
WITH got_email AS (SELECT * FROM person WHERE email IS NOT NULL)
SELECT id,first_name,last_name FROM got_email;

บรรทัดแรกของ CTE กำหนด CTE ที่เรียกว่า `got_email` CTE จะเลือกแถวทั้งหมดจากตาราง `person` โดยที่คอลัมน์ `email` ไม่ใช่ค่าว่าง
บรรทัดที่สองของการสืบค้นจะเลือกชุดย่อยของคอลัมน์จาก CTE `got_email`

แบบสอบถามนี้จะส่งกลับผลลัพธ์ต่อไปนี้:

| id | first_name   | last_name   |
|----|--------------|-------------|
| 1  | Vikki        | Balsillie   |
| 2  | Lorettalorna | Fetteplace  |
| 3  | Ileana       | Guerin      |
| 6  | Maria        | Iddon       |
| 7  | Rog          | McArdell    |

มีคำหลักบางคำที่ไม่สามารถนำมาใช้โดยตรงภายใน CTE ได้ คำสำคัญเช่น INSERT, UPDATE, DELETE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET CTE ส่วนใหญ่จะใช้ในการสืบค้นและเลือกข้อมูล

นิพจน์ตารางทั่วไปหลายรายการ

สามารถสร้างนิพจน์ตารางทั่วไปหลายรายการได้โดยใช้ไวยากรณ์ด้านล่าง

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

CTE แรกถูกแยกออกจาก CTE ที่สองด้วยตัวดำเนินการลูกน้ำ จากนั้นรวมเข้ากับคำสั่ง SELECT นอกคำจำกัดความของ CTE
CTE หลายตัวสามารถใช้สำหรับการดำเนินการ UNION, UNION ALL, JOIN, INTERSECT หรือ EXCEPT
เราจะใช้ตารางเดียวกันด้านบนเพื่ออธิบาย CTE หลายรายการ ในตาราง มีเรกคอร์ดอยู่สองประเภท(บุคคล) ผู้ที่มีทั้งที่อยู่อีเมลและรถยนต์ ผู้ที่มีทั้งที่อยู่อีเมลหรือรถยนต์

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | [email protected]    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | [email protected]     | Female | United Kingdom  | Plymouth    |
| 3  | Ileana       | Guerin      | [email protected]         | Female | Bulgaria        | null        |
| 4  | Walden       | Milmo       | null                        | Male   | Russia          | Ford        |
| 5  | Quincy       | Bromont     | null                        | Male   | China           | Lexus       |
| 6  | Maria        | Iddon       | [email protected]     | Female | Philippines     | Land Rover  |
| 7  | Rog          | McArdell    | [email protected]  | Male   | Poland          | null        |

คราวนี้ เราต้องการกรองตารางเพื่อแสดงเฉพาะบันทึกที่บุคคลนั้นมีทั้งที่อยู่อีเมลและรถยนต์
เราสามารถทำได้โดยใช้ CTE หลายรายการ:

--Create two CTEs named 'no_email' and 'no_car'
-- filter rows from the 'person' table except those in 'no_email' and 'no_car' CTEs
WITH no_email AS (SELECT * FROM person WHERE email IS NULL),
no_car AS (SELECT * FROM person WHERE car_make IS NULL)
SELECT * FROM person EXCEPT
(SELECT * FROM no_email UNION ALL
SELECT * FROM no_car);

บรรทัดแรกกำหนด CTE `no_email` ซึ่งเลือกบันทึกทั้งหมดจากตาราง 'person' โดยที่คอลัมน์ 'email' เป็นค่าว่าง คั่นด้วยตัวดำเนินการลูกน้ำคือ CTE `no_car` ตัวที่สอง ซึ่งจะเลือกบันทึกทั้งหมดจากตาราง `person` โดยที่คอลัมน์ `car_make` เป็นโมฆะ
หลังจากคำจำกัดความของ CTE จะเป็น `SELECT ภายนอก ` ข้อความค้นหาที่กรองแถวจากตาราง `บุคคล` ยกเว้นแถวใน `no_email` และ `no_car` CTEs
ตารางผลลัพธ์;

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | [email protected]    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | [email protected]     | Female | United Kingdom  | Plymouth    |
| 6  | Maria        | Iddon       | [email protected]     | Female | Philippines     | Land Rover  |

การเปรียบเทียบ CTE และตารางชั่วคราว

คุณอาจสงสัยว่า ฟังดูเหมือนโต๊ะชั่วคราว ทำไมไม่ใช้โต๊ะชั่วคราวล่ะ? มีความแตกต่างที่สำคัญบางประการระหว่าง CTE และตารางชั่วคราว
1. CTE ถูกกำหนดโดยใช้ส่วนคำสั่ง `WITH`
ตารางชั่วคราวถูกสร้างขึ้นโดยใช้คำสั่ง `CREATE TEMPORARY TABLE`
2 CTE จะไม่ถูกจัดเก็บเป็นวัตถุทางกายภาพในฐานข้อมูลซึ่งหมายความว่าไม่ได้จัดเก็บไว้บนดิสก์
ตารางชั่วคราวคือตารางทางกายภาพที่มีอยู่ชั่วคราวบนเซิร์ฟเวอร์ฐานข้อมูล มันใช้พื้นที่ดิสก์
3. CTE คือชุดผลลัพธ์ชั่วคราวที่มีอยู่เฉพาะในช่วงระยะเวลาของการสืบค้นเดียวที่อ้างอิงถึงมัน
มีตารางชั่วคราวในช่วงระยะเวลาของเซสชัน
5. CTE ไม่ได้รับประกันประสิทธิภาพรันไทม์ที่ดีขึ้นเสมอไป อย่างไรก็ตาม ช่วยจัดระเบียบการสืบค้นของคุณ
ตารางชั่วคราวสามารถปรับปรุงประสิทธิภาพรันไทม์ได้ โดยเฉพาะอย่างยิ่งเมื่อเราจำเป็นต้องอ้างอิงข้อมูลเดียวกันหลายครั้ง
6. CTE เองก็ไม่สามารถมีดัชนีได้โดยตรง อย่างไรก็ตาม คุณสามารถสร้างดัชนีบนตารางที่ CTE อ้างอิงได้
คุณสามารถสร้างดัชนีบนคอลัมน์ของตารางชั่วคราวได้ เช่นเดียวกับที่คุณสร้างกับตารางทั่วไป

บทสรุป

CTE สามารถทำให้การสืบค้นที่ซับซ้อนอ่านง่ายขึ้นโดยการแบ่งออกเป็นส่วนย่อยๆ ที่มีชื่อ ซึ่งทำให้เข้าใจตรรกะของการสืบค้นได้ง่ายขึ้น นอกจากนี้ยังส่งเสริมการจัดระเบียบรหัสที่ดีขึ้น
CTE มอบโซลูชันที่ไม่เกี่ยวข้องกับการจัดเก็บข้อมูลทางกายภาพบนเซิร์ฟเวอร์ฐานข้อมูล ด้วยการใช้ประโยชน์จาก CTE นักพัฒนาสามารถเขียนโค้ดที่เข้าใจได้ซึ่งสามารถปรับให้เหมาะสมได้ดีขึ้นเมื่อจำเป็น

ขอบคุณที่อ่านจนจบ โปรดพิจารณาติดตามผู้เขียนและสิ่งพิมพ์นี้ ไปที่ Stackademic เพื่อดูข้อมูลเพิ่มเติมว่าเราทำให้การศึกษาด้านการเขียนโปรแกรมฟรีทั่วโลกเป็นประชาธิปไตยได้อย่างไร