注册

SQL 中的 IN 与 EXISTS:了解性能和用法

mysql 中的 in 与 exists:实例和描述

在 mysql 中,in 和 exists 都用于查询中,以根据子查询中是否存在行来过滤数据。然而,它们的工作方式不同,在它们之间进行选择会影响查询性能。让我们通过解释和实践示例来分解它们的差异。


1. in 子句

  • 描述
    in 子句用于根据列的值是否与列表或子查询中的任何值匹配来过滤行。它检查内部查询中的匹配值,并将它们与外部查询进行比较。

  • 性能
    当子查询返回少量记录时,in 子句通常很有效。但是,如果子查询返回较大的数据集,in 可能会变慢。

  • 语法:

  select columns 
  from table 
  where column in (subquery);

2. exists 子句

  • 描述
    exists 子句检查子查询返回的行是否存在。如果子查询返回任何行,则 exists 的计算结果为 true,并且外部查询将继续进行。它不关心行的内容,只关心行是否存在。

  • 性能
    对于大型数据集,exists 通常速度更快,因为一旦找到匹配项,它就会停止处理。这使得在处理返回多行的子查询时变得高效。

  • 语法:

  select columns 
  from table 
  where exists (subquery);

实践示例

让我们考虑两个表:客户和订单。

客户表

customer_id customer_name
1 john doe
2 jane smith
3 alice brown

订单表

order_id customer_id order_total
1 1 200
2 1 150
3 2 300

我们希望找到所有至少下过一笔订单的客户。


使用 in 子句

select customer_name 
from customers 
where customer_id in (select customer_id from orders);

说明

  • 子查询 (select customer_id from orders) 返回订单表中出现的所有客户 id。
  • 外部查询选择 customer_id 在该结果集中的客户。

结果
|客户名称 |
|--------------|
|约翰·多伊 |
|简·史密斯 |


使用 exists 子句

select customer_name 
from customers c
where exists (select 1 from orders o where o.customer_id = c.customer_id);

说明

  • 子查询 select 1 fromorders o where o.customer_id = c.customer_id 检查orders表中的任何行是否与customers表中当前行的customer_id匹配。
  • 如果找到任何匹配项,exists 将返回 true,并且该客户将包含在结果中。

结果
|客户名称 |
|--------------|
|约翰·多伊 |
|简·史密斯 |


主要差异

  1. 返回值:

    • in:将列的值与子查询的结果集进行比较。
    • exists:根据子查询是否返回任何行,返回 true 或 false。
  2. 效率

    • in 对于较小的数据集更有效。
    • 对于大型数据集,exists 速度更快,尤其是当子查询返回许多行时。
  3. 用例

    • 将列的值与一小部分可能值进行比较时使用 in。
    • 当您检查子查询中是否存在行时(例如,当外部查询和内部查询之间存在相关性时),请使用 exists。

性能示例

假设我们有:

  • 10,000 名客户
  • 100,000 份订单

使用 in 查询:

select customer_name 
from customers 
where customer_id in (select customer_id from orders);
  • 执行:mysql将从子查询中检索整个结果集,并将其与外部查询中的每一行进行比较。

使用 exists 查询:

select customer_name 
from customers c
where exists (select 1 from orders o where o.customer_id = c.customer_id);
  • 执行:mysql 将检查外部查询中的每一行,并在子查询中找到匹配的行后停止,从而使大型数据集的速度更快。

结论

  • 当您有一个简单的列表要比较或一个小的子查询结果时,请使用 in
  • 当您处理大型数据集或需要检查子查询中是否存在相关数据时,请使用 exists

以上就是SQL 中的 IN 与 EXISTS:了解性能和用法的详细内容,更多请关注CTO智库其它相关文章!