SQL 中 IN 和 NOT IN 用法的那些事

好记忆不如烂笔头，能记下点东西，就记下点，有时间拿出来看看，也会发觉不一样的感受.

老是说面试，没啥意思，今天说点技术的东西，

一、SQL中的IN 和 NOT IN 

（一）基本概念

• IN ：用于指定一个标量值的列表，用于匹配查询结果中是否包含列表中的值。例如：SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...)。

• NOT IN ：用于指定一个标量值的列表，用于匹配查询结果中是否不包含列表中的值。例如：SELECT column_name(s) FROM table_name WHERE column_name NOT IN (value1, value2, ...)。

（二）潜在问题

• 效率低 ：在某些情况下，尤其是数据量较大时，IN 和 NOT IN 的查询效率可能较低。例如，当子查询返回大量数据时，使用 IN 的查询可能需要进行全表扫描，导致查询速度变慢。

• 容易出错 ：在使用 IN 时，如果不小心在子查询中写错了列名，可能会导致查询结果不准确，而且不会报错。对于 NOT IN，如果子查询中包含 NULL 值，那么整个查询结果可能会为空，这可能与预期不符。

二、它们在不同数据库中的使用限制和区别

（一）MySQL

• 使用限制 ：在 MySQL 中，IN 和 NOT IN 的使用基本遵循标准 SQL 的规则。对于子查询返回的结果集，需要注意其返回的值类型应与主查询中的列类型一致。如果子查询返回的结果集中包含 NULL 值，那么使用 NOT IN 时可能会导致查询结果为空。

• 性能优化 ：在某些情况下，MySQL 对 IN 和 NOT IN 的查询可能会进行自动优化。但为了提高查询效率，建议尽量避免在子查询中使用复杂的查询逻辑，或者在子查询中包含大量数据。可以考虑使用 EXISTS 或 JOIN 代替。

（二）Oracle

• 使用限制 ：Oracle 对 IN 和 NOT IN 的使用也有类似的标准 SQL 规则。同样需要注意子查询返回的结果集类型与主查询列类型的一致性。对于 NOT IN，若子查询结果中存在 NULL 值，查询结果可能不会符合预期。

• 性能优化 ：Oracle 通常会根据查询语句的执行计划和表的统计信息来优化查询。在使用 IN 和 NOT IN 时，如果子查询的结果集较大，可能会导致查询效率降低。可以通过调整查询语句的写法，如使用 EXISTS 或 JOIN，来提高查询性能。
  记住：oracle 有in 1000 的限制哦

（三）SQL Server

• 使用限制 ：在 SQL Server 中，IN 和 NOT IN 的使用遵循标准 SQL 规范。需要注意子查询返回值与主查询列的类型匹配问题。对于 NOT IN，当子查询包含 NULL 值时，查询结果可能出现异常。

• 性能优化 ：SQL Server 会根据查询语句的执行计划和索引等来优化查询性能。在某些情况下，使用 IN 和 NOT IN 可能会导致查询效率低下。可以尝试使用 EXISTS 或 JOIN 等其他查询方式来代替，以提高查询效率。

（四）DB2

• 使用限制 ：DB2 对 IN 和 NOT IN 的使用规则与标准 SQL 相符。在使用过程中，要注意子查询返回结果的类型与主查询列的类型一致。当使用 NOT IN 且子查询结果中存在 NULL 值时，查询结果可能不符合预期。

• 性能优化 ：DB2 会根据查询语句的执行计划和表的结构等来优化查询。如果使用 IN 和 NOT IN 的查询性能不理想，可以考虑使用其他查询方式，如 EXISTS 或 JOIN，来提高查询效率。

• 相知不迷路，来者皆是兄弟，搜索公众号 ：“codingba” or “码出精彩” 交朋友，有更多资源相送。
  

三、替代方案

（一）使用 EXISTS 或 NOT EXISTS 代替

• 语法示例 ：

  • 使用 EXISTS：SELECT * FROM test1 WHERE EXISTS (SELECT * FROM test2 WHERE id2 = id1)

  • 使用 NOT EXISTS：SELECT * FROM test1 WHERE NOT EXISTS (SELECT * FROM test2 WHERE id2 = id1)

• 优点 ：EXISTS 和 NOT EXISTS 的查询效率通常比 IN 和 NOT IN 更高，特别是在子查询返回的结果集较大时。而且不存在因子查询包含 NULL 值而导致 NOT IN 查询结果为空的问题。

（二）使用 JOIN 代替

• 语法示例 ：

  • 使用 INNER JOIN：SELECT id1 FROM test1 INNER JOIN test2 ON id2 = id1

  • 使用 LEFT JOIN：SELECT id1 FROM test1 LEFT JOIN test2 ON id2 = id1 WHERE id2 IS NULL

• 优点 ：通过 JOIN 查询可以将多个表的数据进行关联查询，查询效率通常较高，而且可以避免因子查询出错而导致的结果不准确问题。

综上所述，在使用 SQL 中的 IN 和 NOT IN 时，需要注意其潜在的问题，如效率低和容易出错等。在不同数据库（MySQL、Oracle、SQL Server、DB2）中，它们的使用规则基本一致，但在性能优化方面可能存在差异。为了提高查询效率和准确性，可以考虑使用 EXISTS、NOT EXISTS 或 JOIN 等方式代替 IN 和 NOT IN。在实际应用中，应根据具体的需求和数据库的特点来选择合适的查询方式。