SQL中EXISTS和IN的区别是什么？

Question

EXISTS和IN在SQL中有何区别？

何时应使用EXISTS，何时应使用IN？

- Krantz

Answer 1

"

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查询将始终获得nested join计划，而join查询将适当地使用嵌套、merge或hash。更现代的实现更加智能，即使使用in也可以调整计划。

- Keith

---

可以简述为：“当你在in语句中使用表格时，最好使用join代替in，但这并不重要。查询优化器无论哪种方式都会返回相同的计划。”其中重点是指可以用JOIN替换IN语句。 - farthVader

“select * from [table] where [field] in (select [field] from [table2])” 返回的结果（以及查询计划）与 “select * from [table] join [table2] on [table2].[field] = [table].[field]” 相同。 - user4570983

@Sander，它并不是这样的：第一个查询返回table中的所有列，而第二个查询返回table和table2中的所有内容。在一些（主要是旧的）SQL数据库中，in查询将被实现为嵌套连接，而join查询可以被嵌套、合并、哈希等等 - 以最快的方式实现。 - Keith

好的，我应该在选择子句中指定列，但你应该更新你的答案，因为它明确表示查询“无论哪种方式都将返回相同的计划”。 - user4570983

exists can be used within a case statement, so they can be handy that way also i.e. select case when exists (select 1 from emp where salary > 1000) then 1 else 0 end as sal_over_1000 - smooth_smoothie

投票系统的好处在于，当原问题提问者不愿意勾选他们发现最有用的答案时，这种好处尤其明显。 - sanepete

Answer 2

EXISTS 可以告诉你一个查询是否返回任何结果。例如：

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

IN用于将一个值与多个值进行比较，可以使用文字值，例如：

IN是一种在SQL中使用的运算符，用于指定要匹配的多个值。它可以与列名或常量值一起使用，以检索满足条件的行。

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

您也可以使用查询结果与 IN 子句一起使用，就像这样：

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

- Matt Hamilton

---

由于子查询没有返回任何结果，最后一个查询可能会失败。'in'子句至少需要1个参数... - user2054927

@user2054927 如果子查询没有返回任何行，最后的查询将会正确地返回零行 - 这一点并不危险！ - Tony Andrews

我有点困惑。最后一个查询假设Orders表中有ProductNumber这一列？如果没有，就会报错说该列不存在。 - undefined

Answer 3

基于规则优化器：

• EXISTS在子查询结果非常大的情况下比IN更快。
• IN在子查询结果非常小的情况下比EXISTS更快。

基于成本优化器：

• 没有区别。

- jackson

---

你的论点有证据吗？我认为IN从来都不会比EXISTS更快！ - Nawaz

@Nawaz 你能否给出证明，为什么IN比EXISTS慢？ - ceving

糟糕的查询优化器？我曾经见过类似的情况发生在某个关系型数据库管理系统中（尽管不是完全相同的情况）。 - Haroldo_OK

这个为什么会得到这么多赞？这个基于假设的陈述没有普遍真实性可言，没有任何理由支持它的普适性。 - Lukas Eder

@LukasEder 人们喜欢直截了当的答案，即使这会损害正确性。比较“茶有益健康”和“研究人员声称，如果每天饮用三到四杯茶，可以将患上2型糖尿病的几率降低25%。然而，研究发现，每天饮用超过四杯茶会增加女性患类风湿性关节炎的风险。” - NoName

Answer 4

我假设你已经知道它们的作用并且使用方法不同，所以我理解你的问题是：何时重写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的作用及如何使用，还是询问是否将使用IN的SQL重写为使用EXISTS，或者反过来，是否是个好主意？

- lassevk

---

我不知道OP，但我想知道这个问题的答案！在子查询返回ID时，什么情况下应该使用EXISTS而不是IN？ - Roy Tinker

在JOIN中，你需要使用DISTINCT。 - Jaider

很棒的演示，但基本上没有回答这个问题。 - Junchen Liu

@RoyTinker，答案是一种主观的混合体，既要考虑“使用X比Y更容易理解，有利于维护开发人员”，又要考虑“使用X可以使其比Y更快/资源占用更少，从而解决性能问题”。工程就是一个妥协。 - Caius Jard

@CaiusJard 没错，我同意。我在2010年构建的系统是一个内部的JSON <=> SQL ORM，因此性能是首要考虑因素，而不是生成的查询语句有多“可读”。 - Roy Tinker

你能记得11年前的事情真是太棒了，值得赞扬！我甚至都不记得上周写的代码为什么要这样写了！ - Caius Jard

Answer 5

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'

查询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进行比较。

- Alireza Masali

---

这个答案是对Tom Kite观点的合理概括（http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:953229842074）。 - Jeromy French

我认为这个答案是基于直觉的，这是可以接受的。但它并不普遍适用。例如，对于 Ingres 来说几乎肯定不正确，它会将等效的 SQL 查询解析为相同的 QUEL 查询，而 QUEL 在写同样的内容时缺乏 SQL 的“丰富性”。 - onedaywhen

只有当t2.id被定义为“NOT NULL”时，这两个查询才在逻辑上等价。为了保证在表定义中没有依赖关系的等价性，第二个查询应该是“SELECT t1.* FROM t1 WHERE t1.id not in (SELECT t2.id FROM t2 where t2.id is not null)”。 - David דודו Markovitz

Answer 6

IN 仅支持等式关系（或在 NOT 前面时支持不等式）。
它是 =any / =some 的同义词，例如：

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

EXISTS支持不能用IN表达的多种关联类型，例如：

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 || '℅'
                  )
;

---

另外一个话题 -

EXISTS 和 IN 之间据称的性能和技术差异可能是由于特定供应商的实现/限制/错误，但很多情况下它们只是由于对数据库内部机制缺乏理解而产生的谬论。

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

- David דודו Markovitz

---

对于你在性能方面的评论点赞：不应该专注于特定的DBMS，我们应该假设优化器会找出最佳的解决方案。 - Manngo

Answer 7

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

- If you are using the IN operat

Answer 8

Exists关键字的作用是判断是否存在符合条件的行，返回true或false。而IN关键字将对应子查询列中的所有值进行比较。 另外，Select 1可与Exists命令一起使用。例如：

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

但是IN的效率较低，因此Exists更快。

- Arulraj.M

Answer 9

我认为，

• EXISTS 是当你需要将查询结果与另一个子查询进行匹配时使用的。 需要检索出 SubQuery 结果与之匹配的 Query#1 结果。类似于 Join.. 例如：选择那些在 table#2 中也下单过的 customers table#1。

• IN 是用来检索特定列的值是否位于列表中（1、2、3、4、5）IN。 例如：选择那些邮编在（...）列表中的客户，即 zip_code 值位于（...）列表中。

何时使用其中之一... 当您觉得它的表达恰当（更能传达意图）。

- Gishu

Answer 10

区别在于：

select * 
from abcTable
where exists (select null)

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

select *
from abcTable
where abcTable_ID in (select null)

尝试一下并观察输出结果。

- rogue lad

---

嗯... 错误：[SQL0104] Token ) 无效。在这两种情况下都是如此。你假设了特定的RDBMS吗？ - jmarkmurphy