MYSQL IN 和 NOT IN 优化

常规的用法

IN 用法

WITH table1 AS (SELECT "A" c1 UNION SELECT "B" c1 
UNION SELECT "C" c1 UNION SELECT "D" c1)
SELECT *
FROM table1 t1
WHERE t1.c1 IN ("A","B","C")

NOT IN 用法

WITH table1 AS (SELECT "A" c1 UNION SELECT "B" c1 
UNION SELECT "C" c1 UNION SELECT "D" c1)
SELECT *
FROM table1 t1
WHERE t1.c1 NOT IN ("A","B","C")

但是数据量过大时这么做会有问题，这时候需要EXISTS函数

WITH table1 AS (SELECT "A" c1 UNION SELECT "B" c1 
UNION SELECT "C" c1 UNION SELECT "D" c1),
table2 AS (SELECT "A" c1 UNION SELECT "B")
SELECT *
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 WHERE c1=t1.c1)