LEFT JOIN และ NULL: การค้นหาสิ่งที่ขาดหายไป

เก็บทุกแถวของตารางไว้ด้วย LEFT JOIN ค้นหาสิ่งที่ขาดหายด้วย IS NULL และแทนที่มันด้วย COALESCE

8 นาที · 3 คำถาม

INNER JOIN จากบทเรียนที่แล้วซ่อนลูกค้าที่ไม่มีคำสั่งซื้อไว้ แต่คำถามทางธุรกิจที่พบบ่อยที่สุดกลับเป็น: ใครที่ขาดหายไป? ลูกค้าที่ไม่เคลื่อนไหว สินค้าที่ไม่เคยขายได้ ใบแจ้งหนี้ที่ไม่มีการชำระเงิน... รันสคริปต์นี้ใน sqliteonline.com หรือ sqlite3 มันจะเพิ่มลูกค้าคนที่สี่ที่ไม่มีคำสั่งซื้อ

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Alice', 'Lyon'),
  (2, 'Karim', 'Paris'),
  (3, 'Mina', 'Nantes'),
  (4, 'Paulo', 'Lille');

INSERT INTO orders (id, customer_id, amount)
VALUES
  (1, 1, 49.90),
  (2, 1, 15.00),
  (3, 2, 120.50);

LEFT JOIN เก็บ ทุก แถวของตารางฝั่งซ้าย (ตารางที่ระบุไว้ก่อนคำสงวน) ไว้ แม้จะไม่มีคู่ที่ตรงกันทางฝั่งขวา เมื่อไม่มีคู่ที่ตรงกัน คอลัมน์ของตารางฝั่งขวาจะถูกเติมด้วย NULL

SELECT c.name, o.id AS order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id;

ในการแยกสิ่งที่ขาดหายออกมา ให้กรองด้วยค่า NULL เหล่านี้ นี่คือรูปแบบ anti-join ซึ่งเป็นปฏิกิริยาที่จำเป็นในการวิเคราะห์ข้อมูล คอลัมน์ที่ใช้ทดสอบต้องมาจากตารางฝั่งขวาและต้องไม่มีทางเป็น NULL โดยธรรมชาติ จึงเลือกคีย์หลัก o.id ของมัน

SELECT c.name, c.city
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.id IS NULL;

เครื่องมือสุดท้าย: COALESCE(a, b) จะคืนค่า a ถ้ามันไม่เป็น NULL มิฉะนั้นจะคืน b เมื่อใช้ร่วมกับ LEFT JOIN และการรวมกลุ่ม (aggregation) มันจะเปลี่ยนสิ่งที่ขาดหายให้กลายเป็นเลขศูนย์ที่ใช้งานได้ในรายงานหรือใบแจ้งหนี้

SELECT c.name,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
GROUP BY c.id
ORDER BY total_spent DESC;

ทดสอบความรู้

ตรวจสอบว่าคุณจำประเด็นสำคัญของบทเรียนนี้ได้ครบถ้วน

1. การผสมผสานแบบใดที่แสดงรายชื่อลูกค้าที่ไม่เคยสั่งซื้อ?
   • INNER JOIN แล้วตามด้วย WHERE o.id IS NULL
   • LEFT JOIN แล้วตามด้วย WHERE o.id IS NULL
   • LEFT JOIN แล้วตามด้วย WHERE o.id = NULL
2. เงื่อนไข amount = NULL คืนค่าอะไร?
   • จริงสำหรับแถวที่ amount เป็น NULL
   • ไม่เป็นจริงเลย การเปรียบเทียบยังคงไม่นิยาม
   • ข้อผิดพลาดทางไวยากรณ์
3. COALESCE(SUM(o.amount), 0) คืนค่าอะไรสำหรับลูกค้าที่ไม่มีคำสั่งซื้อ?
   • 0
   • NULL
   • ลูกค้าถูกตัดออกจากผลลัพธ์
   • ข้อผิดพลาดของการรวมกลุ่ม