ตัดแถวที่มีค่าซ้ำในคอลัมน์หนึ่งเมื่อคอลัมน์อีกคอลัมน์มีค่าเฉพาะใน MySQL

ปัญหา

มีตารางที่ StudentId ซ้ำได้ แต่ต้องการ: หาก StudentId นั้นมีแถวที่ StudentMarks = 89 ให้ ตัดทิ้งทั้งหมด ของ StudentId นั้น (รวมแถวอื่นของ Id เดียวกัน)

วิธีแก้: ใช้ Subquery กับ NOT IN

SELECT * FROM DemoTable1427
WHERE StudentId NOT IN (
    SELECT DISTINCT StudentId 
    FROM DemoTable1427 
    WHERE StudentMarks = 89
);

ตัวอย่างสมบูรณ์

1. สร้างตารางและข้อมูล

CREATE TABLE DemoTable1427 (
    StudentId INT,
    StudentMarks INT
);

INSERT INTO DemoTable1427 VALUES (201, 89);
INSERT INTO DemoTable1427 VALUES (201, 99);  -- Id เดียวกัน Marks ต่าง
INSERT INTO DemoTable1427 VALUES (210, 98);

2. ข้อมูลทั้งหมด

SELECT * FROM DemoTable1427;
+-----------+--------------+
| StudentId | StudentMarks |
+-----------+--------------+
| 201       | 89           |
| 201       | 99           |
| 210       | 98           |
+-----------+--------------+

3. Query: ตัด Id ที่มี Marks = 89 ทั้งหมด

SELECT * FROM DemoTable1427
WHERE StudentId NOT IN (
    SELECT DISTINCT StudentId 
    FROM DemoTable1427 
    WHERE StudentMarks = 89
);

4. ผลลัพธ์ (เหลือแค่ Id 210)

+-----------+--------------+
| StudentId | StudentMarks |
+-----------+--------------+
| 210       | 98           |
+-----------+--------------+

การทำงานของ Subquery

1. Subquery: หา DISTINCT StudentId ที่มี StudentMarks = 89 → ได้ {201}
2. Main Query: เลือกแถวที่ StudentId NOT IN (201) → เหลือ Id 210
3. แถว (201, 99) ถูกตัดทิ้ง เพราะ Id 201 อยู่ใน Blacklist

วิธีอื่น: LEFT JOIN / NOT EXISTS (ประสิทธิภาพดีกว่า)

LEFT JOIN ... IS NULL

SELECT t1.* 
FROM DemoTable1427 t1
LEFT JOIN (
    SELECT DISTINCT StudentId 
    FROM DemoTable1427 
    WHERE StudentMarks = 89
) t2 ON t1.StudentId = t2.StudentId
WHERE t2.StudentId IS NULL;

NOT EXISTS (แนะนำ สำหรับ NULL Safety)

SELECT * FROM DemoTable1427 t1
WHERE NOT EXISTS (
    SELECT 1 FROM DemoTable1427 t2
    WHERE t2.StudentId = t1.StudentId
      AND t2.StudentMarks = 89
);

ข้อควรระวัง NOT IN

NOT IN จะคืนค่า Empty Set หาก Subquery มีค่า NULL ใช้ NOT EXISTS แทนปลอดภัยกว่า

สรุป

ใช้ Subquery หา Id ที่มีค่าเฉพาะ (89) แล้ว NOT IN หรือ NOT EXISTS เพื่อตัดแถวทั้งหมดของ Id นั้นออก