เทคนิคการเขียน 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 ในหัวข้อนี้

inner query – select ข้อมูลจากตาราง invoices โดยฟิลเตอร์เฉพาะปี 2012 เท่านั้น
outer query – select และ join ทั้งสองตารางเข้าด้วยกันชื่อย่อ as {cust, inv} ตามลำดับ
หาผลรวมของคอลัมน์ 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)

DataRockie