MySQL SELECT แสดงเฉพาะแถวที่ตรงเงื่อนไข (Subquery NOT IN)

Filter แถวด้วย Subquery และ NOT IN

ใช้ Subquery ใน WHERE Clause เพื่อดึงเฉพาะแถวที่ไม่ตรงเงื่อนไขย่อย

ตัวอย่าง: นักเรียนที่คะแนน >= 50

CREATE TABLE DemoTable625 (
    StudentId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StudentFirstName VARCHAR(100),
    StudentScore INT
);

INSERT INTO DemoTable625(StudentFirstName, StudentScore) VALUES
('John', 98), ('Chris', 39), ('Bob', 41), ('David', 40), ('Robert', 52);

Query: Select นักเรียนที่ Score ไม่อยู่ในกลุ่ม < 50

SELECT * FROM DemoTable625 
WHERE StudentFirstName NOT IN (
    SELECT StudentFirstName 
    FROM DemoTable625 
    WHERE StudentScore < 50
);

ผลลัพธ์: John (98), Robert (52)

+-----------+------------------+--------------+
| StudentId | StudentFirstName | StudentScore |
+-----------+------------------+--------------+
| 1         | John             | 98           |
| 5         | Robert           | 52           |
+-----------+------------------+--------------+

วิธีที่ดีกว่า: NOT EXISTS (จัดการ NULL ได้ถูกต้อง)

SELECT * FROM DemoTable625 d1
WHERE NOT EXISTS (
    SELECT 1 FROM DemoTable625 d2
    WHERE d2.StudentFirstName = d1.StudentFirstName
      AND d2.StudentScore < 50
);

NOT IN vs NOT EXISTS: NULL Problem

-- NOT IN: ถ้า Subquery คืนค่า NULL ทั้งหมด -> ผลลัพธ์ว่างเปล่า (เพราะ NULL comparison = UNKNOWN)
SELECT * FROM t1 WHERE col NOT IN (SELECT col FROM t2 WHERE ...);  -- อันตราย!

-- NOT EXISTS: จัดการ NULL ได้ถูกต้อง
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.col);

วิธีที่ทันสมัย: LEFT JOIN / IS NULL (Anti-Join)

SELECT d1.* 
FROM DemoTable625 d1
LEFT JOIN (
    SELECT StudentFirstName FROM DemoTable625 WHERE StudentScore < 50
) d2 ON d1.StudentFirstName = d2.StudentFirstName
WHERE d2.StudentFirstName IS NULL;

Performance Comparison

MySQL 8.0.31+: EXCEPT Operator

SELECT * FROM DemoTable625
EXCEPT
SELECT * FROM DemoTable625 WHERE StudentScore < 50;

Best Practice

1. หลีกเลี่ยง NOT IN กับ Subquery ที่อาจคืน NULL
2. ใช้ NOT EXISTS หรือ LEFT JOIN IS NULL แทน
3. Index บน Column ที่ Join/Compare
4. EXPLAIN Query ก่อน Deploy Production