SQL 中 EXISTS 和 IN 的区别？

Question

提问by Krantz

What is the difference between the EXISTSand INclause in SQL?

SQL 中的EXISTSandIN子句有什么区别？

When should we use EXISTS, and when should we use IN?

什么时候用EXISTS，什么时候用IN？

Answer 1

回答by Keith

The existskeyword can be used in that way, but really it's intended as a way to avoid counting:

该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 ... )

This is most useful where you have ifconditional statements, as existscan be a lot quicker than count.

这在您if有条件语句的情况下最有用，因为exists它比count.

The inis best used where you have a static list to pass:

该in是最好的使用，你有一个静态列表通过：

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

When you have a table in an instatement it makes more sense to use a join, but mostly it shouldn't matter. The query optimiser should return the same plan either way. In some implementations (mostly older, such as Microsoft SQL Server 2000) inqueries will always get a nested joinplan, while joinqueries will use nested, mergeor hashas appropriate. More modern implementations are smarter and can adjust the plan even when inis used.

当您在in语句中有一个表时，使用 a 更有意义join，但大多数情况下应该无关紧要。无论哪种方式，查询优化器都应该返回相同的计划。在某些实现中（大多数是较旧的，例如 Microsoft SQL Server 2000），in查询将始终获得嵌套连接计划，而join查询将根据需要使用嵌套、合并或散列。更现代的实现更智能，即使在in使用时也可以调整计划。

Answer 2

回答by Matt Hamilton

EXISTSwill tell you whether a query returned any results. e.g.:

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

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

INis used to compare one value to several, and can use literal values, like this:

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

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

You can also use query results with the INclause, like this:

您还可以将查询结果与IN子句一起使用，如下所示：

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

Answer 3

回答by Hymanson

Based on rule optimizer:

根据规则优化：

EXISTSis much faster than IN, when the sub-query results is very large.
INis faster than EXISTS, when the sub-query results is very small.

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

Based on cost optimizer:

基于成本优化器：

There is no difference.

没有区别。

Answer 4

回答by Lasse V. Karlsen

I'm assuming you know what they do, and thus are used differently, so I'm going to understand your question as: When would it be a good idea to rewrite the SQL to use IN instead of EXISTS, or vice versa.

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

Is that a fair assumption?

这是一个公平的假设吗？

Edit: The reason I'm asking is that in many cases you can rewrite an SQL based on IN to use an EXISTS instead, and vice versa, and for some database engines, the query optimizer will treat the two differently.

编辑：我问的原因是，在许多情况下，您可以重写基于 IN 的 SQL 以使用 EXISTS，反之亦然，对于某些数据库引擎，查询优化器会以不同的方式处理这两者。

For instance:

例如：

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

can be rewritten to:

可以改写为：

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

or with a join:

或加入：

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

So my question still stands, is the original poster wondering about what IN and EXISTS does, and thus how to use it, or does he ask wether rewriting an SQL using IN to use EXISTS instead, or vice versa, will be a good idea?

所以我的问题仍然存在，原发帖者是否想知道 IN 和 EXISTS 的作用，以及如何使用它，或者他是否问是否使用 IN 重写 SQL 以使用 EXISTS，反之亦然，这将是一个好主意？

Answer 5

回答by Alireza Masali

EXISTSis much faster than INwhen the subquery results is very large.
INis faster than EXISTSwhen the subquery results is very small.

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'

Query 1
```
SELECT
FROM    t1 
WHERE   not  EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id)
```
Query 2
```
SELECT t1.* 
FROM   t1 
WHERE  t1.id not in (SELECT  t2.t1id FROM t2 )
```
If in t1your id has null value then Query 1 will find them, but Query 2 cant find null parameters.
I mean INcan't compare anything with null, so it has no result for null, but EXISTScan compare everything with null.

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

Answer 6

回答by If you are using the IN operat

If you are using the INoperator, the SQL engine will scan all records fetched from the inner query. On the other hand if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match.

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

Answer 7

回答by David ???? Markovitz

INsupports only equality relations (or inequality when preceded by NOT).
It is a synonym to =any/ =some, e.g

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

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

EXISTSsupports variant types of relations, that cannot be expressed using IN, e.g. -

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

And on a different note -

换个说法——

The allegedly performance and technical differences between EXISTSand INmay result from specific vendor's implementations/limitations/bugs, but many times they are nothing but myths created due to lack of understanding of the databases internals.

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

The tables' definition, statistics' accuracy, database configuration and optimizer's version have all impact on the execution plan and therefore on the performance metrics.

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

Answer 8

回答by Arulraj.M

The Existskeyword evaluates true or false, but INkeyword compare all value in the corresponding sub query column. Another one Select 1can be use with Existscommand. Example:

该Exists关键字计算真或假，但IN关键字比较相应的子查询列的所有值。另一个Select 1可以与Exists命令一起使用。例子：

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

But INis less efficient so Existsfaster.

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

Answer 9

回答by Gishu

I think,

我认为，

EXISTSis when you need to match the results of query with another subquery. Query#1 results need to be retrieved where SubQuery results match. Kind of a Join.. E.g. select customers table#1 who have placed orders table#2 too
IN is to retrieve if the value of a specific column lies INa list (1,2,3,4,5) E.g. Select customers who lie in the following zipcodes i.e. zip_code values lies in (....) list.

EXISTS是当您需要将查询结果与另一个子查询进行匹配时。Query#1 结果需要在 SubQuery 结果匹配的地方检索。一种联接.. 例如，选择也已下订单的客户表#1 表#2
IN 是检索特定列的值是否位于IN列表 (1,2,3,4,5) 例如，选择位于以下邮政编码中的客户，即 zip_code 值位于 (....) 列表中。

When to use one over the other... when you feel it reads appropriately (Communicates intent better).

什么时候用一个而不是另一个……当你觉得它读起来合适时（更好地传达意图）。

Answer 10

回答by rogue lad

Difference lies here:

不同之处在于：

select * 
from abcTable
where exists (select null)

Above query will return all the records while below one would return empty.

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

select *
from abcTable
where abcTable_ID in (select null)

Give it a try and observe the output.

试一试并观察输出。

SQL 中 EXISTS 和 IN 的区别？

提问by Krantz

回答by Keith

回答by Matt Hamilton

回答by Hymanson

回答by Lasse V. Karlsen

回答by Alireza Masali

回答by If you are using the IN operat

回答by David ???? Markovitz

And on a different note -

换个说法——

回答by Arulraj.M

回答by Gishu

回答by rogue lad

相关推荐

最近更新

标签

SQL 中 EXISTS 和 IN 的区别？

提问by Krantz

回答by Keith

回答by Matt Hamilton

回答by Hymanson

回答by Lasse V. Karlsen

回答by Alireza Masali

回答by If you are using the IN operat

回答by David ???? Markovitz

And on a different note -

换个说法——

回答by Arulraj.M

回答by Gishu

回答by rogue lad

相关推荐

按月比较产品销售额的 SQL 查询

SQL 具有多列的 oracle 汇总功能

使用 T-SQL/MS-SQL 将字符串附加到现有表格单元格的最简单方法是什么？

SQL/查询工具？

相关推荐

最近更新

标签