Tag: Common Table Expression

🤨 Subquery คืออะไร?

Query ใน SQL (Structured Query Language) คือ คำสั่งที่ส่งไปยังฐานข้อมูล (database) เพื่อเรียกดูหรือวิเคราะห์ข้อมูล

ตัวอย่าง query:

SELECT ...
FROM ...

Subquery คือ query ที่ซ้อนอยู่ใน query อื่น

ตัวอย่าง subquery:

SELECT ...
FROM (SELECT ... FROM ...)

• SELECT ... FROM คือ query หลัก
• (SELECT ... FROM ...) คือ subquery

ถ้าเราเห็น SELECT ในวงเล็บเมื่อไร แสดงว่า เรากำลังใช้ subquery อยู่

Note:

• Query หลัก มักเรียกว่า outer query
• Subquery บางทีเรียกว่า inner query หรือ nested query

🤔 Subquery ใช้ทำอะไร?

Subquery ใช้วิเคราะห์ข้อมูล (data transformation) ให้กับ query หลัก ซึ่งทำให้:

• วิเคราะห์ข้อมูลได้ยืดหยุ่นมากขึ้น (dynamic aggregation)
• กรองข้อมูลได้ยืดหยุ่นมากขึ้น (dynamic filtering)
• เชื่อม tables ได้ง่ายขึ้น

เพราะแทนที่เราจะต้องหาค่าต่าง ๆ ก่อนใส่ลงใน query เราสามารถคำนวณค่าเหล่านี้ใน query ได้โดยตรง

🧑‍💻 ตัวอย่างการใช้ Subquery

Subquery ใช้ได้หลายที่ เช่น:

1. SELECT
2. WHERE
3. JOIN

ไปดูตัวอย่างการใช้งาน subquery กับ chinook database ซึ่งมีข้อมูลของร้านมีเดีย (media) ออนไลน์ (เช่น ข้อมูลลูกค้า ข้อมูลเพลง) กัน

.

💻 การใช้ Subquery กับ SELECT

เราจะใช้ subquery กับ SELECT เพื่อวิเคราะห์ข้อมูลให้กับแต่ละ row ใน table

ตัวอย่าง:

หาค่าเฉลี่ย (average) ของ invoice:

-- Subquery in SELECT
SELECT
    InvoiceId,
    Total,
    (
        SELECT AVG(Total)
        FROM Invoice -- Subquery
    ) AS Mean
FROM Invoice;

ผลลัพธ์:

.

💻 การใช้ Subquery กับ WHERE

เราจะใช้ subquery กับ WHERE เพื่อช่วยกรองข้อมูล

ตัวอย่าง:

หา invoice ที่มียอดสูงกว่าค่าเฉลี่ย:

-- Subquery in WHERE
SELECT
    InvoiceId,
    Total
FROM Invoice
WHERE Total > (
    SELECT AVG(Total)
    FROM Invoice -- Subquery
);

ผลลัพธ์:

.

💻 การใช้ Subquery กับ JOIN

เราจะใช้ subquery กับ JOIN เพื่อสร้าง table ชั่วคราวขึ้นมาเชื่อมกับ table อื่น

ตัวอย่าง:

หาว่า ลูกค้าแต่ละคนใช้จ่ายเท่าไร:

-- Subquery in JOIN
SELECT
    c.FirstName,
    c.LastName,
    s.TotalSpent
FROM Customer AS c
JOIN (
    SELECT
        CustomerId,
        SUM(Total) AS TotalSpent
    FROM Invoice
    GROUP BY CustomerId -- Subquery
) AS s
    ON c.CustomerId = s.CustomerId;

ผลลัพธ์:

🚀 แนะนำ Subquery ขั้นสูง

Subquery ขั้นสูงที่ควรรู้จัก มี 2 ประเภท ได้แก่:

1. Correlated subquery
2. Nested subquery

.

💿 Correlated Subquery

Correlated subquery คือ subquery ที่อ้างอิงถึง column จาก query หลัก

หมายความว่า การวิเคราะห์ของ correlated subquery ขึ้นอยู่กับ query หลัก

ตัวอย่าง:

หาเพลงที่มีความยาวมากกว่าค่าเฉลี่ยของเพลงประเภทเดียวกัน:

-- Correlated subquery
SELECT
    t.Name,
    t.Milliseconds,
    t.GenreId
FROM Track AS t
WHERE t.Milliseconds > (
    SELECT AVG(t2.Milliseconds)
    FROM Track AS t2
    WHERE t2.GenreId = t.GenreId -- Refer to column in the main query
);

สังเกตว่า subquery อ้างอิง t.GenreId จาก query หลัก

ผลลัพธ์:

.

🪹 Nested Subquery

Nested subquery คือ subquery ที่อยู่ในอีก subquery หนึ่ง

Nested subquery มักใช้เพื่อแบ่งการวิเคราะห์ข้อมูลออกเป็นขั้นย่อย ๆ

ตัวอย่าง:

หาเพลงที่ยาวกว่าความยาวเฉลี่ยของเพลงร็อก:

-- Nested subquery
SELECT
    Name,
    Milliseconds
FROM Track
WHERE Milliseconds > ( -- Subquery
    SELECT AVG(Milliseconds)
    FROM Track
    WHERE GenreId = ( -- Nested subquery
        SELECT GenreId
        FROM Genre
        WHERE Name = 'Rock'
    )
);

ผลลัพธ์:

💡 เทคนิคการเขียน Subquery ให้อ่านง่าย: WITH

การใช้ subquery อาจทำให้ query ของเราอ่านยากได้

ตัวอย่าง:

หาลูกค้าที่ใช้จ่ายสูงกว่าค่าเฉลี่ย:

SELECT
    CustomerId,
    SUM(Total) AS total_spent
FROM Invoice
GROUP BY CustomerId
HAVING SUM(Total) > (
    SELECT AVG(customer_total)
    FROM (
        SELECT
            CustomerId,
            SUM(Total) AS customer_total
        FROM Invoice
        GROUP BY CustomerId
    ) AS customer_spending
);

เพื่อแก้ปัญหานี้ เราสามารถใช้ WITH มาช่วยได้

WITH เป็นคำสั่งสำหรับสร้างผลลัพธ์ชั่วคราว (Common Table Expression; CTE) ซึ่งเราใช้อ้างอิงใน query ได้

WITH ทำให้เราใช้ subquery ได้โดยไม่ต้องใส่ subquery ลงใน query หลัก

ตัวอย่าง:

-- Use WITH to create CTEs
WITH customer_spending AS (
    SELECT
        CustomerId,
        SUM(Total) AS total_spent
    FROM Invoice
    GROUP BY CustomerId
),
average_spending AS (
    SELECT AVG(total_spent) AS avg_spent
    FROM customer_spending
)
-- SELECT with CTEs
SELECT
    CustomerId,
    total_spent
FROM customer_spending -- CTE
WHERE total_spent > (
    SELECT avg_spent
    FROM average_spending -- CTE
);

จะเห็นว่า WITH ทำให้ query ของเราอ่านง่ายขึ้นมาก

💪 บทสรุป

• Subquery เป็น query ที่ซ้อนอยู่ใน query อื่น
• ใช้วิเคราะห์ข้อมูลเพื่อส่งไปใช้ใน query หลัก
• ใช้ร่วมกับคำสั่งอื่นได้ เช่น SELECT, WHERE, JOIN
• Correlated subquery คือ subquery ที่อ้างอิง column ใน query หลัก
• Nested subquery คือ subquery ที่อยู่ใน subquery อื่น
• ใช้ WITH เพื่อทำให้ query ที่มี subquery อ่านง่ายขึ้น

🫵 หลังจบบทความนี้

หลังจบบทความนี้แล้ว มาลองใช้ subquery กันนะครับ

• ดู code และ database ในบทความนี้บน GitHub
• ฝึกใช้ SQL และ subquery ฟรีได้ที่ sqliteonline.com

📄 อ้างอิง

• Subquery เทคนิคง่ายๆ ช่วย query สบายกว่าที่เคย
• เทคนิคการเขียน Subqueries ง่ายๆใน SQLite
• SQL Subquery
• SQL Subquery – How to Sub Query in SELECT Statement
• SQL Subquery: A Comprehensive Guide
• Data Manipulation in SQL