Basic SQL Part 5 - Sub query

Programming, SQL

Sub query

ตั๋วรถไฟตอนที่ไปสิงคโปร์ครั้งแรก

ผมเขียนเกี่ยวกับ SQL ไว้ 6 ตอน คุณสามารถกด Link ด้านล่างเพื่ออ่านที่เกี่ยวกับ SQL ตอนต่างๆได้เลย

Sub query จริงๆมันก็ไม่มีอะไรมากครับ มันก็แค่ Query ใน Query แล้วก็การใช้ SUb query เนี่ยก็ไม่ค่อยนิยมเท่าไหร่เพราะมันเริ่มซับซ้อน และอาจเริ่มมีผลกับ Performance ซึ่งก็ไม่ใช่ทุกกรณีที่ใช้ Sub query แล้วจะมีผลต่อ Performance

เหมือนคนอ่านมาถึงตรงนี้แล้วอาจจะเริ่มรู้สึกว่า เฮ้ย ตกลงมันควรใช้ไหม อันนี้ขึ้นอยู่กับว่าใช้ที่ไหน ใช้ยังไงมากกว่า ถ้าไปใช้กับงาน Real time แล้วไป Sub query ท่ายาก ก็อาจจะทำให้เกิดปัญหาเรื่อง Performance ได้ แต่ถ้าใช้กับ Database ที่ถูกแยกออกมาเพื่อออก Report โดยเฉพาะการใช้ Sub query ก็อาจไม่มีผลกับ Performance บอกข้อเสีย (สุ่มเสี่ยงว่าจะมีทำให้เกิดปัญหาแล้ว) มาพูดถึงข้อดีของการใช้ Sub query กันบ้าง ข้อดีของมันสะดวกสบายแบบใช้ SQL แล้วจบเลยไม่ต้องไปเขียน Code ข้างนอกเพิ่ม เดี๋ยวในตอนนี้จะเห็นเองว่ามันมีโจทย์แบบนี้อยู่

ก่อนเริ่มให้ Download sql แล้ว copy คำสั่งไปใส่ในเว็บ sqliteonline.com

ใช้ Sub query แบบง่ายๆกันก่อน

ถ้าเราอยากรู้ว่าประธานาธิบดีคนไหนไม่ได้แต่งงาน เราจะเขียน SQL ยังไง ถ้าด้วยความรู้จากการ Join เราสามารถเขียนได้ด้วยวิธีแบบนี้

1
2
3
4
5
SELECT PRESIDENT.*
FROM PRESIDENT
LEFT JOIN PRES_MARRIAGE
ON PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME
WHERE PRES_MARRIAGE.PRES_NAME IS NULL

แนวคิดอันนี้คือเราเอาข้อมูลมา LEFT JOIN แล้ว ROW ไหน JOIN ไม่ติดค่าฝั่ง PRES_MARIAGE จะเป็นค่า NULL เราก็แค่ WHERE ด้วย Column ใน PRES_MARIAGE ว่ามีค่าเท่ากับ NULL ไหมเราก็จะได้คำตอบ

แต่มีอีกแนวคิดนึงในการค้นหาก็คือ ถ้าเรารู้ชื่อมีประธานาธิบดีที่แต่งงาน เราก็เอามาใช้ตรง where ใน not in ก็จะสามารถหาคำตอบได้เช่นกัน

1
2
3
4
5
-- Query 1

SELECT *
FROM PRESIDENT
WHERE PRESIDENT.PRES_NAME not in ( [ List ชื่อของประธานาธิบดีที่แต่งงานแล้ว  ] )

คราวนี้ถ้าเราจะหาชื่อของประธานาธิบดีทั้งหมดที่แต่งงานแล้วเราจะหาด้วย Query แบบนี้เลยใช่มะ

1
2
3
4
-- Query 2

SELECT PRES_NAME
FROM PRES_MARRIAGE

ถ้าสมมุติเราสามารถเอา Query 2 ไปใส่ใน Query 1 ตรง List ได้ เราก็จะสามารถตอบคำถามได้แล้วใช่ไหมล่ะ แล้วก็เป็นที่น่ายินดีว่า SQL อนุญาตให้เราทำแบบนั้นครับ ซึ่งเราจะรวม Query ออกมาเป็นแบบนี้

1
2
3
4
5
6
7
8
-- รวม 2 Query เป็น Query เดียว

SELECT *
FROM PRESIDENT
WHERE PRESIDENT.PRES_NAME not in (  
    SELECT PRES_NAME
    FROM PRES_MARRIAGE 
)

มาลองอีกตัวอย่าง ถ้าอยากรู้ข้อมูลประธานาธิบดีคนไหนที่อายุตอนตายมากที่สุดเราจะเขียน SQL ยังไง วิธีง่ายๆก็คือ SELECT แล้ว ORDER ด้วยอายุตอนตายแบบ DESC

1
2
3
SELECT *
FROM PRESIDENT
ORDER BY DEATH_AGE DESC

แต่เราสามารถเขียนอีกแบบได้โดยใช้ Sub query คือ ถ้าเรารู้ว่า Death age มากที่สุดคือเท่าไหร่ เราก็สามารถ Where ด้วย Death age นั้น ซึ่งเราจะได้ sql แบบนี้

1
2
3
4
5
6
SELECT *
FROM PRESIDENT
WHERE DEATH_AGE = (
    SELECT MAX(DEATH_AGE)
    FROM PRESIDENT
)

จากทั้งสองตัวอย่างจะเห็นว่าเราสามารถเอา Sub query มาประยุกต์เขียนเป็น query หาคำตอบได้แล้ว โดยหากสังเกตดีๆ Sub query จะเป็น Sub query ที่จบในตัวเองไม่เกี่ยวข้องกับ Query อื่น ว่าง่ายๆเราสามารถดึง Sub query ไปทำแล้วเอาค่ามาแปะได้เลย เอา sub query ไป run ก่อนได้

Sub query แบบยาก (Correlated subquery)

จริงๆ Query แบบนี้มีชื่อทางการว่า Correlated subquery แต่ผมเรียกมันว่า Sub query แบบยากละกัน ว่าแต่มันเป็นยังไงผมก็อธิบายไม่ถูกผมว่ามาดูตัวอย่างกันเลยดีกว่า ถ้ามีคำถามถามว่า จงแสดงข้อมูลของประธานาธิบดีที่แต่งงานและภรรยาคนล่าสุด ไม่ต้องแสดงข้อมูลภรรยาคนก่อน อ่ะ ด้วยคำถามนี้เราจะทำยังไงดีล่ะ ใช้ JOIN แล้ว GROUP ได้ไหม เราลองมาพยายามกันดู

1
2
3
4
5
6
7
8
-- Query นี้ไม่ถูกแค่ลองพยายามเขียนว่ามันจะเขียนยังไง

SELECT PRESIDENT.PRES_NAME, -- จะเอาชื่อภรรยามาแสดงไม่ได้เพราะ GROUP BY ด้วย PRES_NAME แต่ถ้า 
-- จะ GROUP BY ด้วย SPOUSE_NAME ก็ไม่ได้เพราะพอทำแล้วการจัดกลุ่มจะไม่เป็นเฉพาะประธานาธิบดีคนนั้น
FROM PRESIDENT
INNER JOIN PRES_MARRIAGE
ON PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME
GROUP BY PRESIDENT.PRES_NAME

จะเห็นว่าทำยังไงเราก็ไม่สามารถหาคำตอบได้โดยใช้ Query ธรรมดา ถ้าผมคิดง่ายๆผมจะเขียนโปรแกรมดึง Query นี้ก่อน แล้วใช้โปรแกรมข้างนอกไป For loop ดูว่าประธานาธิบดีคนนี้แต่งงานมากกว่า 1 ไหม ถ้ามากกว่าก็หาข้อมูลมากสุด

แต่ถ้าเราลองคิดดีๆ ถ้าเรารู้ว่าประธานาธิบดีคนนั้นแต่งงานตอน MAR_YEAR ที่มากสุด เราจะได้ข้อมูลภรรยาคนที่แต่งงานล่าสุดมาด้วยใช่รึเปล่า

1
2
3
4
SELECT *
FROM PRESIDENT
INNER JOIN PRES_MARRIAGE
WHERE PRES_MARRIAGE.MAR_YEAR = (MAR_YEAR มากสุดของประธานาธิบดีคนนั้น)

ตามภาพถ้าเราสามารถส่ง PRES_NAME เข้าไปใน Sub query ได้ เราก็จะสามารถหาคำตอบได้ ซึ่งก็โชคดีครับเพราะ SQL ทำได้ครับ โดย Query จะออกมาแบบนี้

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM PRESIDENT p1
INNER JOIN PRES_MARRIAGE p2
ON p1.PRES_NAME = p2.PRES_NAME
WHERE p2.MAR_YEAR = (
    SELECT MAX(MAR_YEAR)
    FROM PRES_MARRIAGE p3
    WHERE p2.PRES_NAME = p3.PRES_NAME
)
-- ตรง p1, p2, p3 นั้นใช้เป็นตัวแทนตารางนั้น เพราะตารางมันชื่อซ้ำกันเลยต้องกำหนดชื่อให้มัน

จาก Query เราจะเห็นว่าตรง Sub query “p2.PRES_NAME = p3.PRES_NAME” เราส่ง p2.PRES_NAME เข้าไปใน Sub query ดังนั้นเราสามารถคิดโดยคร่าวๆได้ว่าเอาข้อมูลมา JOIN กันก่อนพอได้ผลลัพธ์การ JOIN ก็เอาผลลัพธ์ส่งเข้าใน Sub query พอได้ผลลัพธ์ของ Sub query ก็เอามา WHERE กับ Query หลัก

เราจะเห็นความแตกต่างระหว่าง Sub query แบบยากกับแบบง่ายแล้วนะครับ แบบง่ายคือ Query มันจบในตัวเองไม่ต้องส่งข้อมูลจาก Query หลักเข้าไปใน Sub query แต่ถ้าเป็น Query แบบยาก (Correlated subquery) คือจะมีการส่งข้อมูลจาก Query หลักไปใน Sub query

ซึ่งถ้ามองดีๆเราจะเห็นว่าการใช้ Correlated subquery นั่นสุ่มเสี่ยงว่าจะมีผลกับ Performance เพราะเหมือนจะมีการ Query เพิ่มอีก เช่น จากตัวอย่างผลลัพธ์การ JOIN มีทั้งหมดมีทั้งหมด 44 ROW ดังนั้นต้องไปทำ Sub query อีก 44 Sub query นี่จึงเป็นเหตุผลที่การใช้ Sub query แบบ Correlated subquery นั้นสุ่มเสี่ยงที่จะมีผลกับ Performance แต่ๆๆๆๆๆๆๆ อยากให้คิดเสมอว่ามันคือวิธีที่แย่ที่สุดครับในการทำครับ คนที่เขาเขียน Database เขาอาจจะเขียนวิธี Optimize การทำอะไรแบบนี้ทำให้มันเร็วก็ได้ครับ

1
2
3
4
SELECT count(*) -- 44
FROM PRESIDENT
INNER JOIN PRES_MARRIAGE
ON PRESIDENT.PRES_NAME =  PRES_MARRIAGE.PRES_NAME

จบละ

สำหรับตอนนี้เราก็เรียนรู้เกี่ยวกับ Sub query กันแล้วนะครับ ทั้ง Sub query แบบง่าย Sub query แบบยาก (Correlated subquery) ซึ่งทำให้เราสามารถสร้าง Query ที่ตอบคำถามที่ไม่จบด้วย Query เดียวได้แล้ว สำหรับใครที่อ่านมาตั้งแต่ตอนแรกมาถึงตอนนี้ ผมขอบอกเลยว่าตอนนี้คุณสามารถเขียน Query หาคำตอบได้เกือบทุกรูปแบบแล้ว จะขาดแต่เรื่อง UNION ซึ่งตั้งแต่ผมทำงานมาไม่เคยใช้เลย แล้วก็ LIMIT ซึ่งแต่ละ Database ใช้ไม่เหมือนกันผมเลยไม่พูดถึง ส่วนใครไม่เชื่อเดี๋ยวตอนหน้าเรามาลองตะลุยโจทย์ SQL เกี่ยวกับประธานาธิบดีกัน โจทย์นี้อาจารย์ที่เคยสอนผมบอกว่าเป็นโจทย์ที่ใช้สอนคนที่หัดใช้ Database IBM ซึ่งอาจารย์เคยบอกว่าถ้าทำโจทย์พวกนี้ได้โจทย์ งานจริงก็แทบจะทำได้หมดแล้ว

เพลงประกอบการเขียน Code

ซ้ำเติม เพลงนี้เคยฟังครั้งแรกตอนทำงานคือเปิด List เพลงเก่าไปเรื่อยๆจนได้มาเจอเพลงนี้ คือแบบ เฮ้ย โดนทั้งทำนอง ทั้งเนื้อร้อง แล้วตอนฟังนี่อยู่ในช่วงโดนเทแล้วเหตุการณ์ตรงกับเพลงเลย เพลงนี้ก็เลยอยู่ใน List ที่เปิดฟังบ่อยๆตอนทำงานแล้วก็เขียน Blog