我假设您知道它们的作用，因此使用方式不同，所以我将把您的问题理解为:什么时候重写SQL以使用IN而不是EXISTS是一个好主意，或者反之亦然。

这个假设合理吗?

编辑:我问的原因是，在许多情况下，你可以重写基于in的SQL来使用EXISTS，反之亦然，对于一些数据库引擎，查询优化器将区别对待这两者。

例如:

SELECT *
FROM Customers
WHERE EXISTS (
SELECT *
FROM Orders
WHERE Orders.CustomerID = Customers.ID
)

可以改写为:

SELECT *
FROM Customers
WHERE ID IN (
SELECT CustomerID
FROM Orders
)

或者用join:

SELECT Customers.*
FROM Customers
INNER JOIN Orders ON Customers.ID = Orders.CustomerID

所以我的问题仍然存在，原来的海报想知道什么是IN和EXISTS，因此如何使用它，或者他问是否重写SQL使用IN使用EXISTS，反之亦然，将是一个好主意?

EXISTS将告诉你查询是否返回任何结果。例如:

SELECT *
FROM Orders o
WHERE EXISTS (
SELECT *
FROM Products p
WHERE p.ProductNumber = o.ProductNumber)

IN用于将一个值与多个值进行比较，并且可以使用文字值，如下所示:

SELECT *
FROM Orders
WHERE ProductNumber IN (1, 10, 100)

你也可以用IN子句来使用查询结果，就像这样:

SELECT *
FROM Orders
WHERE ProductNumber IN (
SELECT ProductNumber
FROM Products
WHERE ProductInventoryQuantity > 0)

我认为,

• EXISTS是当你需要将查询结果与另一个子查询匹配时。 查询#1的结果需要在SubQuery结果匹配的地方检索。有点像Join.. 例如，选择表1中也下过表2订单的客户

• IN用于检索特定列的值是否位于IN list (1,2,3,4,5) 例如，选择位于以下邮政编码的客户，即zip_code值位于(....)列表。

什么时候使用一种而不是另一种……当你觉得读起来合适的时候(更好地传达意图)。

exists关键字可以这样使用，但实际上它是一种避免计数的方法:

--this statement needs to check the entire table
select count(*) from [table] where ...


--this statement is true as soon as one match is found
exists ( select * from [table] where ... )

当你有if条件语句时，这是最有用的，因为exists可以比count快得多。

in最适合用于需要传递静态列表的地方:

 select * from [table]
where [field] in (1, 2, 3)

当你在in语句中有一个表时，使用join更有意义，但大多数情况下这并不重要。无论哪种方式，查询优化器都应该返回相同的计划。在某些实现中(大多数是较旧的，如Microsoft SQL Server 2000) in查询总是得到嵌套连接计划，而join查询将使用嵌套的、适当的合并或哈希。更现代的实现更智能，即使在使用in时也可以调整计划。

基于规则优化器:

• 当子查询结果非常大时，EXISTS比IN快得多。
• 当子查询结果很小时，IN比EXISTS快。

基于成本优化器:

• 没有区别。

据我所知，当子查询返回NULL值时，整个语句就变成NULL。在这种情况下，我们使用EXITS关键字。如果要比较子查询中的特定值，则使用IN关键字。

SELECT * FROM Temp1 where exists(select 1 from Temp2 where conditions...)

但是IN效率较低，所以Exists更快。

如果使用IN操作符，SQL引擎将扫描从内部查询中获取的所有记录。另一方面，如果我们使用EXISTS, SQL引擎将在找到匹配项后立即停止扫描过程。

1. 当子查询结果非常大时，EXISTS比IN快得多 当子查询结果很小时，IN比EXISTS快

   CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT)
   GO
   CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20))
   GO
   
   
   INSERT INTO t1
   SELECT 1, 'title 1', 5 UNION ALL
   SELECT 2, 'title 2', 5 UNION ALL
   SELECT 3, 'title 3', 5 UNION ALL
   SELECT 4, 'title 4', 5 UNION ALL
   SELECT null, 'title 5', 5 UNION ALL
   SELECT null, 'title 6', 5
   
   
   INSERT INTO t2
   SELECT 1, 1, 'data 1' UNION ALL
   SELECT 2, 1, 'data 2' UNION ALL
   SELECT 3, 2, 'data 3' UNION ALL
   SELECT 4, 3, 'data 4' UNION ALL
   SELECT 5, 3, 'data 5' UNION ALL
   SELECT 6, 3, 'data 6' UNION ALL
   SELECT 7, 4, 'data 7' UNION ALL
   SELECT 8, null, 'data 8' UNION ALL
   SELECT 9, 6, 'data 9' UNION ALL
   SELECT 10, 6, 'data 10' UNION ALL
   SELECT 11, 8, 'data 11'
   

2. Query 1

   SELECT
   FROM    t1
   WHERE   not  EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id)
   

   查询2

   SELECT t1.*
   FROM   t1
   WHERE  t1.id not in (SELECT  t2.t1id FROM t2 )
   

   如果在t1中你的id有空值，那么查询1将找到它们，但查询2不能找到空参数。

   我的意思是IN不能将任何东西与null进行比较，所以它没有null的结果，但是EXISTS可以将所有东西与null进行比较

如果子查询返回多个值，则可能需要执行外层查询—如果条件中指定的列中的值与子查询结果集中的任何值匹配。要执行此任务，需要使用in关键字。

您可以使用子查询来检查一组记录是否存在。为此，你需要使用带有子查询的exists子句。exists关键字总是返回true或false值。

EXISTS的性能比in快。 如果大多数过滤条件在子查询中，则最好使用in;如果大多数过滤条件在主查询中，则最好使用EXISTS

哪个更快取决于内部查询获取的查询数量:

• 当您的内部查询获取数千行，那么EXIST将是更好的选择
• 当您的内部查询获取少量行时，那么IN将更快

EXIST对true或false进行评估，但在比较多个值。当你不知道记录是否存在时，你应该选择exist

如果使用IN操作符，SQL引擎将扫描从内部查询中获取的所有记录。另一方面，如果我们使用EXISTS, SQL引擎将在找到匹配后立即停止扫描过程。

不同之处在于:

select *
from abcTable
where exists (select null)

上面的查询将返回所有记录，而下面的查询将返回空。

select *
from abcTable
where abcTable_ID in (select null)

尝试一下并观察输出。

原因是EXISTS操作符基于“至少找到”原则。它返回true，一旦找到至少一个匹配的行，就停止扫描表。

另一方面，当IN操作符与子查询结合时，MySQL必须先处理子查询，然后使用子查询的结果来处理整个查询。

一般的经验法则是，如果子查询包含一个大的 对于大量的数据，EXISTS操作符提供了更好的性能 但是，如果使用IN操作符，查询将执行得更快 子查询返回的结果集非常小

我的理解是，只要我们不处理NULL值，两者都应该是相同的。

在只支持相等关系(或前面有不的不相等关系)。
它是=任何 / =一些的同义词，例如

select    *
from      t1
where     x in (select x from t2)
;

存在支持不同类型的关系，不能用在表示，例如-

select    *
from      t1
where     exists (select    null
from      t2
where     t2.x=t1.x
and t2.y>t1.y
and t2.z like '℅' || t1.z || '℅'
)
;

还有另一种说法——

所谓的存在和在之间的性能和技术差异可能是由于特定供应商的实现/限制/错误造成的，但很多时候它们只是由于缺乏对数据库内部结构的理解而产生的神话。

表的定义、统计数据的准确性、数据库配置和优化器的版本都会影响执行计划，因此也会影响性能指标。

我相信这个问题有一个直截了当的答案。你为什么不从那些在他们的系统中开发了这个功能的人那里检查一下呢?

如果你是一个MS SQL开发人员，下面是来自微软的直接答案。

IN:

确定指定的值是否与子查询或列表中的任何值匹配。

EXISTS:

指定子查询以测试是否存在行。

In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

https://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm#i28403

如果你可以使用where in代替where exists，那么where in可能更快。

使用where in或where exists 将遍历父结果的所有结果。这里的区别是where exists将导致大量依赖子查询。如果你可以防止依赖子查询，那么where in将是更好的选择

例子

假设我们有10,000家公司，每家公司有10个用户(因此我们的用户表有100,000个条目)。现在假设您希望通过用户名或公司名查找用户。

下面使用were exists的查询执行时间为141ms:

select * from `users`
where `first_name` ='gates'
or exists
(
select * from `companies`
where `users`.`company_id` = `companies`.`id`
and `name` = 'gates'
)

然而，如果我们避免exists查询并使用:

select * from `users`
where `first_name` ='gates'
or users.company_id in
(
select id from `companies`
where  `name` = 'gates'
)

然后避免依赖子查询，查询将在0,012毫秒内运行