Query ค่าที่ไม่อยู่ในตาราง MySQL ด้วย UNION ALL และ NOT EXISTS

สามารถสร้างชุดค่าที่ต้องการตรวจสอบ (Virtual Table) ด้วย UNION ALL แล้วกรองเฉพาะค่าที่ ไม่อยู่ ในตารางจริงได้โดยใช้ NOT EXISTS หรือ NOT IN

ตัวอย่างการใช้งาน

1. สร้างตารางและเพิ่มข้อมูล (มีค่า 1-5)

mysql> create table DemoTable1918 ( Value int NOT NULL AUTO_INCREMENT PRIMARY KEY ); Query OK, 0 rows affected (0.00 sec)

mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec)

ข้อมูลในตาราง:

mysql> select * from DemoTable1918;

+-------+
| Value |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
+-------+
5 rows in set (0.00 sec)

2. Query ค่า 6,7,8 ที่ไม่อยู่ในตาราง

mysql> select tbl.Value 
    -> from ( 
    ->   select 6 as Value union all 
    ->   select 7 union all 
    ->   select 8 
    -> ) tbl 
    -> where not exists ( 
    ->   select 1 from DemoTable1918 tbl1 
    ->   where tbl1.Value = tbl.Value
    -> );

ผลลัพธ์:

+-------+
| Value |
+-------+
|     6 |
|     7 |
|     8 |
+-------+
3 rows in set (0.00 sec)

อธิบายการทำงาน

1. (select 6 as Value union all select 7 union all select 8) - สร้าง Derived Table ชื่อ tbl มีค่า 6,7,8
2. NOT EXISTS (SELECT 1 FROM DemoTable1918 WHERE Value = tbl.Value) - ตรวจสอบว่าไม่มีค่านี้ในตารางจริง
3. ค่า 6,7,8 ไม่อยู่ในตาราง (ซึ่งมีแค่ 1-5) → ผ่านเงื่อนไข → แสดงผล

วิธีอื่น: ใช้ NOT IN

SELECT * FROM (
    SELECT 6 AS Value UNION ALL SELECT 7 UNION ALL SELECT 8
) AS t
WHERE Value NOT IN (SELECT Value FROM DemoTable1918);

การประยุกต์: หา Missing Numbers (Sequence Gaps)

-- หาตัวเลขที่ขาดหายใน Sequence 1-10
SELECT seq.n FROM (
    SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) seq
WHERE seq.n NOT IN (SELECT Value FROM DemoTable1918);

สรุป: ใช้ UNION ALL สร้าง Virtual Table ของค่าที่ต้องการตรวจสอบ แล้ว NOT EXISTS กรองเฉพาะค่าที่ไม่อยู่ในตารางจริง วิธีนี้ปลอดภัยกับ NULL และประสิทธิ์ดี