IN子查询与JOIN的区别理解
日常开发中，经常会使用 IN 的SQL查询，除了 IN （固定值）之外，也会有用到这样的子查询（子查询不加条件）：
SELECT name FROM table1 WHERE name IN (SELECT name FROM table2);
而他看起来的结果是不是又和内连接超级像：
SELECT table1.name
FROM table1
INNER JOIN table2 ON table1.name = table2.name;
光看的话可能会以为这两者是完全等价的，但其实并不总是等价。
等价的场景
两种写法都试图找出在 table2 中也存在的 name，结果往往类似。在没有NULL、无重复值时，这两个查询通常返回相同结果。
比如：
– table1
| id | name |
|---|---|
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
– table2
| id | name |
|---|---|
| 1 | 张三 |
| 2 | 王五 |
用上面两种写法的结果都是：
| name |
|---|
| 张三 |
| 王五 |
所以有时候确实WHERE IN = JOIN，但不会一直是这样，来看反例。
不等价的场景
① IN的子查询结果中包含NULL
比如说 table2 其实是这样的：
– table2
| id | name |
|---|---|
| 1 | 张三 |
| 2 | NULL |
对于IN：
那上面的子查询(SELECT name FROM table2)的结果就是(张三，NULL)，因为 IN (张三, NULL) 中包含NULL，整个表达式的结果会变成：未知（UNKNOWN），按照 SQL 三值逻辑（TRUE / FALSE / UNKNOWN），会导致查询结果为空或不确定。
对于JOIN：
JOIN会自动跳过NULL（因为name=NULL永远为FALSE），仍然能返回 张三。
📝结论：IN查询可能因为NULL而误杀本该命中的数据。
② IN的子查询结果中包含重复值
比如说 table2 其实是这样的：
– table2
| id | name |
|---|---|
| 1 | 张三 |
| 2 | 张三 |
对于IN：
子查询就是IN (张三，张三)，最后只会返回一行张三，因为IN语义是集合判断（去重后对比）。
对于JOIN：
会返回2行张三，因为是笛卡尔匹配，table1的张三出现1次，table2的张三 出现2次，JOIN 会产生2条结果。
📝结论：JOIN可能产生重复行，而IN自动去重。
性能比较
IN性能：
①子查询返回结果越大，性能越差；
②如果子查询无法被优化成半连接（semi-join），可能导致子查询N次执行；
就是说，如果MySQL能优化为semi-join，它会先把子查询执行一次得到结果集合，例如 {张三, 李四, 666}；
然后查table1表时就像查WHERE user_id IN (1001, 1002, 1003)一样，效率比较高。
但如果 MySQL 无法优化成 semi-join：
它就会对table1表的每一行都执行一次子查询，再判断这一行的name是否在结果中。也就是执行了N次子查询，这就是所谓的“子查询N次执行”。
MySQL从5.6开始引入semi-join优化，但他自己是否能成功应用，要看SQL结构和字段特征。
③适用于小结果集过滤、大表中筛选少量数据；
JOIN性能：
①可以利用索引、HASH JOIN、MERGE JOIN 等优化策略；
②更适合复杂关联、联合提取字段；
③更可控：你可以配合 EXPLAIN 分析执行计划并优化索引；
📝实测中，对于子查询量较大、或多表字段提取场景，JOIN 更具优势。
EXISTS
还有一种经常被忽视但语义明确、性能好的写法：
SELECT name
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t1.name = t2.name
);
EXISTS不管你有没有重复值，也不关心是什么值，他只关心在这里面有没有，有就返回true。
意思就是从table1 中选出那些name值，是在table2 中至少出现过一次的。
这样不受 NULL 干扰、不会重复行，还可用于更复杂的逻辑（比如 NOT EXISTS）
补充一下：关于NULL干扰
假设table2是
| id | name |
|---|---|
| 1 | 张三 |
| 2 | NULL |
要查的是
SELECT name FROM table1
WHERE name IN (SELECT name FROM table2);
你以为：只要 table1 中的 name 在 table2 中存在，就能查出来。
但现在 table2 里有 张三 和 NULL，这时候：
对于张三，张三 IN (‘张三’, NULL) → ✅ 命中，返回；
对于李四，李四 IN (‘张三’, NULL)：
和张三比较是 false
和NULL比较是 UNKNOWN
所以整体是：FALSE OR UNKNOWN → UNKNOWN → ❌ 不返回
这就让人误以为 李四明明不在table2 中，为什么不能明确判断是false？
这就是所说的：
当 IN 列表中存在 NULL，任何不命中的值也可能返回 UNKNOWN，而不是明确的 false，从而引发你预期之外的不返回。