เทคนิคการเขียน Subqueries ง่ายๆใน SQLite

Meet Subquery

Subquery คือการเขียน query ซ้อน query โดยตัวคิวรี่ที่อยู่ข้างในจะเรียกว่า “inner/ sub query” และคิวรี่ที่อยู่ด้านนอกจะเรียกว่า “outer query” วิธีสังเกตง่ายๆว่าคิวรี่ไหนเป็น subquery ให้มองหา select ที่อยู่ในวงเล็บแบบนี้

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

Subquery ช่วยให้เราเขียน query ที่มีความซับซ้อนขึ้น (ความคิดสร้างสรรค์กระฉูด 555+) โดยรวมหลายๆคิวรี่เข้าด้วยกันและรันทีเดียว ลำดับการรันจะเริ่มจาก inner most query ชั้นในสุดก่อนและค่อยๆไล่ออกมาชั้นนอก

👩‍💻 ถ้าใครยังไม่เคยเขียน SQL ลองอ่านบทความสอนพื้นฐานของเราได้ที่นี่ ดาวน์โหลดไฟล์ตัวอย่าง chinook.db

Simple Example

มาลองดูตัวอย่างแรกกันก่อน สมมติเราอยากจะรู้ชื่อเพลงที่มีขนาดใหญ่ที่สุด maximum bytes จากตาราง tracks เราต้องเขียนสอง queries โดยคิวรี่แรกใช้หา max(bytes) และนำผลลัพธ์ที่ได้ไปใส่ในคิวรี่ที่สองใน where clause

-- first query
SELECT MAX(bytes) FROM tracks; --1059546140

-- second query
SELECT * FROM tracks WHERE bytes = 1059546140;

เราสามารถรวมสองคิวรี่ด้านบนด้วยเทคนิค subquery ได้เลย (query ซ้อน query) ตามตัวอย่างด้านล่าง สังเกตว่า select ตัวที่สองจะอยู่ในเครื่องหมาย () หรือพูดง่ายๆวิธีการดูว่า query นั้นเป็น subquery หรือเปล่า ให้ดูว่ามีการเขียน select อยู่ในวงเล็บหรือเปล่า ง่ายกว่าที่คิด!

SELECT * FROM tracks
WHERE bytes = (SELECT MAX(bytes) FROM tracks);

🎬 ตัวอย่างการเขียน subquery ง่ายๆใน where clause เพื่อดึงข้อมูลออกมาจากตาราง tracks

Subquery in Where Clause

👩‍💻 ดึงชื่อและนามสกุลลูกค้าที่อยู่ในประเทศที่ชื่อขึ้นต้นด้วยตัว “U”

มาลองดูอีกหนึ่งตัวอย่างใน where clause สมมติเราต้องการดึงชื่อลูกค้าที่อยู่ในประเทศที่ชื่อขึ้นต้นด้วยตัว “U” ผลลัพธ์ของ subquery จะออกมาสองประเทศคือ USA และ United Kingdom

SELECT firstname, lastname, country FROM customers 
WHERE country IN (
  SELECT DISTINCT country FROM customers
  WHERE country LIKE 'U%');

เราสามารถเขียนทีละ query ตามลำดับนี้เพื่อให้ได้ผลลัพธ์เดียวกัน นำ result ที่ได้จาก query แรกไปใส่ใน where clause ของ query ที่สอง

-- inner query returns 'USA' and 'United Kingdom'
SELECT DISTINCT country FROM customers
WHERE country LIKE 'U%';

-- outer query returns customers living in these countries
SELECT firstname, lastname, country FROM customers
WHERE country IN ('USA', 'United Kingdom');

🎬 ตัวอย่างการเขียน subquery ใน where clause แบบใช้ in operator (subquery ดึงออกมามากกว่าหนึ่งค่า)

ตัวอย่างนี้แอดเขียนให้ดูง่ายๆโดยดึงข้อมูลจาก customers แค่ตารางเดียว เวลาทำงานจริงเรานิยมเขียน subquery เพื่อดึงข้อมูลจากหลายๆตารางพร้อมกัน ซึ่งการเขียนจะซับซ้อนขึ้นนิดหน่อย ลองดูหัวข้อถัดไป

A (Little) More Complicated Example

👩‍💻 จงหาไอดีและชื่อลูกค้าที่มี sum invoices สูงสุด 10 อันดับแรกในปี 2012

โจทย์นี้เราจะลองเขียน inner query ใน join clause โดยฟิลเตอร์ข้อมูลในตาราง invoices มาเฉพาะปี 2012 เท่านั้น เราใช้ฟังก์ชัน strftime เพื่อปรับ format คอลัมน์ invoicedate ให้แสดงเฉพาะปี %Y

เสร็จแล้วเราก็ join ข้อมูลในตาราง customers กับ invoices (ที่ฟิลเตอร์มาแล้ว) เข้าด้วยกัน โดยผลลัพธ์มีทั้งหมด 3 คอลัมน์คือ ไอดีลูกค้า ชื่อจริงลูกค้า และผลรวมของ invoices ทั้งหมดในปี 2012 เรียงข้อมูลจากมากไปน้อยแบบ descending order และแสดงผลเฉพาะลูกค้า 10 คนแรกที่มี sum invoices สูงที่สุดในปีนั้น

SELECT 
  cust.customerid, 
  cust.firstname, 
  sum(inv.total) sum_inv 
FROM customers cust
JOIN (SELECT * FROM invoices 
      WHERE STRFTIME("%Y", invoicedate) = '2012') inv
ON cust.customerid = inv.customerid
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10;

สรุปขั้นตอนการทำงานของ subqueries ในหัวข้อนี้

1. inner query – select ข้อมูลจากตาราง invoices โดยฟิลเตอร์เฉพาะปี 2012 เท่านั้น
2. outer query – select และ join ทั้งสองตารางเข้าด้วยกันชื่อย่อ as {cust, inv} ตามลำดับ
3. หาผลรวมของคอลัมน์ total จับกลุ่มตามไอดีและชื่อจริงลูกค้า เรียงคอลัมน์นี้จากมากไปน้อย desc และแสดงผลเฉพาะลูกค้า 10 คนแรกที่มี total invoices สูงที่สุดในปี 2012

[su_spoiler title=”อีกหนึ่ง solution สำหรับโจทย์ข้อนี้”]

เราสามารถ join สองตารางและเขียน where เพื่อฟิลเตอร์เฉพาะข้อมูลในปี 2012 เท่านั้น โดยไม่ต้องเขียน subquery ก็ได้ ทั้งสองคิวรี่ที่เราเรียนในหัวข้อนี้ได้ผลลัพธ์เหมือนกัน 100%

SELECT 
  cust.customerid, 
  cust.firstname, 
  sum(inv.total) sum_inv 
FROM customers cust
JOIN invoices inv ON cust.customerid = inv.customerid
WHERE strftime('%Y', inv.invoicedate) = '2012'
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10;

[/su_spoiler]

Key Takeaway

• Subquery คือการเขียนคิวรี่ซ้อนคิวรี่ (inner + outer queries)
• วิธีดูว่าคิวรี่ไหนเป็น subquery บ้าง ให้มองหา select ที่เขียนอยู่ในวงเล็บ
• เราสามารถเขียน subquery ได้หลายตำแหน่งตั้งแต่ where from join และ select clauses
• SQL เป็นภาษาที่ค่อนข้างยืดหยุ่น เวลาเราเขียน join ดึงข้อมูลมากกว่าหนึ่งตาราง subquery ช่วยเพิ่มลูกเล่นและทางเลือกใหม่ๆในการดึงข้อมูล (อ่านเรื่อง performance ของ join vs. subquery ได้ที่โพสต์ stackoverflow)