เมื่อคำสั่ง UNION คืนค่าผลลัพท์ไม่ครบ

วันนี้เจอเคสแปลกๆของคำสั่ง UNION มาเล่าให้ฟัง ด้านบนคือข้อมูลในฐานข้อมูลที่มีปัญหานะครับ ด้านล่างคือคำสั่ง SQL ที่ผมใช้แต่แรก

(

  SELECT `wallet`,`status`,`income`,`expense`,`owner_id` 

  FROM `omsin_ierecord` 

  WHERE `owner_id` = 187

) UNION (

  SELECT `transfer_to` AS `wallet`,`status`,`expense` AS `income`,0 AS `expense`,`owner_id` 

  FROM `omsin_ierecord` 

  WHERE `owner_id` = 187 AND `status` = 'TRANSFER'

)

คำสั่งของ UNION ด้านบนจะทำการ Query เอาผลลัพท์ที่ owner_id ทั้งหมด ออกมา ส่วนคำสั่งด้านล่างของ UNION เป็นการ Query ข้อมูลเดียวกันแต่เลือกเอาเฉพาะข้อมูลที่ status เท่ากับ TRANSFER ออกมาโดยมีการจัดรูปแบบผลลัพท์ใหม่นิดหน่อย แล้วรวมผลลัพท์เข้ากับด้านบน ได้ผลลัพท์ตามนี้ ถ้าสังเกตุจะเห็นว่าผลลัพท์ที่ได้ ไม่ถูกต้อง ถ้าดูจากข้อมูลจะเห็นว่าใน Query ชุดแรกเราควรได้ TRANSFER จำนวน 3 รายการ และควรได้ข้อมูลสำหรับ Query ด้านล่างที่เอาเฉพาะ TRANSFER ออกมาอีก 3 รายการ รวมเป็น 6 รายการ แต่ผลลัพท์ที่ได้กลับได้ TRANSFER ออกมารวมเพียง 4 รายการ

ผมทดสอบโดยการ Query ทีละส่วน ซึ่งผลลัพท์ของแต่ละส่วนก็ได้ตามที่กำหนดทุกประการ ดังนั้นสรุปว่ามันมีปัญหาที่ตอน UNION นี่แหละ รูปด้านบนนี้คือผลลัพท์ที่ถูกต้อง (หากการ Query ถูกต้อง)

อธิบายก่อนว่าคำสั่ง UNION จะทำการคัดเลือกเอาข้อมูลแถวที่ซ้ำกันออก โดยจะคงไว้แค่แถวที่เจอก่อน ยกตัวอย่างข้อมูล id=7 ด้านบน จะมีข้อมูล wallete,status,income,expense,owner_id ซ้ำกับแถว id=1 (เช่นเดียวกับข้อมูลอีกชุดของ UNION) ซึ่งมีผลให้ข้อมูลหายไป 2 แถวนั่นเอง

การแก้ปัญหา ผมได้กำหนดให้ Query ทำการเลือกคอลัมน์ที่เป็น Index ออกมาด้วย เพื่อจะได้มั่นใจว่าข้อมูลที่ได้จะไม่ซ้ำกันแน่ๆ (Index ย่อมไม่ซ้ำกันอยู่แล้ว) และออกมาครบ ซึ่งในที่นี้ก็คือคอลัมน์ id นั่นเอง

(

  SELECT `id`,`wallet`,`status`,`income`,`expense`,`owner_id` 

  FROM `omsin_ierecord` 

  WHERE `owner_id` = 187

) UNION (

  SELECT `id`,`transfer_to` AS `wallet`,`status`,`expense` AS `income`,0 AS `expense`,`owner_id` 

  FROM `omsin_ierecord` 

  WHERE `owner_id` = 187 AND `status` = 'TRANSFER'

)

ผลปรากฏว่า ผลลัพท์ที่ได้ครบถ้วนแล้ว

สรุปอีกทีละกัน UNION จะทำการตัดผลลัพท์ของข้อมูลที่ซ้ำกันออก ดังนั้นเพื่อให้ได้ผลลัพท์ที่ต้องการอย่างครบถ้วน จึงควร Query เอาคอลัมน์ที่เป็น Index ออกมาด้วย (ถึงจะไม่ได้ใช้งาน) เพื่อป้องกันปัญหาการตัดข้อมูลที่ซ้ำกันโดยไม่ได้ตั้งใจออก (ในกรณีที่ Index ซ้ำกันมันควรถูกตัดออกอยู่แล้ว)

มีเพื่อนสมาชิกให้ความเห็นเพิ่มเติมมาว่าในเคสข้างต้นสามารถใช้ UNION ALL แทนได้ เนื่องจาก UNION ALL จะคืนค่าผลลัพท์ทั้งหมดที่พบ แต่ในการใช้งานจริงให้พิจารณาเอาจากข้อมูลที่มีอยู่ให้รอบคอบว่าจะเลือกวิธีไหนในการใช้งานนะครับ เพื่อให้ได้ผลลัพท์ที่ถูกต้อง

(

  SELECT `wallet`,`status`,`income`,`expense`,`owner_id` 

  FROM `omsin_ierecord` 

  WHERE `owner_id` = 187

) UNION ALL (

  SELECT `transfer_to` AS `wallet`,`status`,`expense` AS `income`,0 AS `expense`,`owner_id` 

  FROM `omsin_ierecord` 

  WHERE `owner_id` = 187 AND `status` = 'TRANSFER'

)