在相关子查询上使用运算符EXISTS的说明 [英] Explanation of using the operator EXISTS on a correlated subqueries

问题描述

以下查询背后的机制有什么解释?

What is an explanation of the mechanics behind the following Query?

这似乎是对表进行动态过滤的强大方法.

It looks like a powerful method of doing dynamic filtering on a table.

CREATE TABLE tbl (ID INT, amt INT)
INSERT tbl VALUES
(1,1),  
(1,1),  
(1,2),
(1,3),
(2,3),  
(2,400),
(3,400),
(3,400)

SELECT *
FROM tbl T1
WHERE EXISTS
  (
    SELECT *
    FROM tbl T2
    WHERE 
       T1.ID = T2.ID AND
       T1.amt < T2.amt
  )

在SQL Fiddle上对其进行实时测试

推荐答案

您通常可以使用显式联接将相关的子查询转换为等效的表达式.这是一种方法:

You can usually convert correlated subqueries into an equivalent expression using explicit joins. Here is one way:

SELECT distinct t1.*
FROM tbl T1 left outer join
     tbl t2
     on t1.id = t2.id and
        t1.amt < t2.amt
where t2.id is null

马丁·史密斯(Martin Smith)显示了另一种方式.

Martin Smith shows another way.

关于它们是否是进行动态过滤的强大方法"的问题是正确的，但(通常)不重要.您可以使用其他SQL构造进行相同的过滤.

The question of whether they are a "powerful way of doing dynamic filtering" is true, but (usually) unimportant. You can do the same filtering using other SQL constructs.

为什么要使用相关子查询?有几个积极的方面和几个消极的方面，这是一个重要的原因.从积极的方面来说，您不必担心行的乘法"，就像上面的查询一样.另外，当您具有其他过滤条件时，相关子查询通常会更有效.而且，有时使用删除或更新，这似乎是表达查询的唯一方法.

Why use correlated subqueries? There are several positives and several negatives, and one important reason that is both. On the positive side, you do not have to worry about "multiplication" of rows, as happens in the above query. Also, when you have other filtering conditions, the correlated subquery is often more efficient. And, sometimes using delete or update, it seems to be the only way to express a query.

一个致命弱点是，许多SQL优化器在嵌套循环连接中实现了相关的子查询(即使不必这样做).因此，它们有时可能效率很低.但是，您拥有的特定存在"构造通常非常有效.

The Achilles heel is that many SQL optimizers implement correlated subqueries as nested loop joins (even though do not have to). So, they can be highly inefficient at times. However, the particular "exists" construct that you have is often quite efficient.

此外，表之间连接的性质可能会在嵌套子查询中丢失，这会使where子句中的条件变得复杂.很难理解在更复杂的情况下发生了什么.

In addition, the nature of the joins between the tables can get lost in nested subqueries, which complicated conditions in where clauses. It can get hard to understand what is going on in more complicated cases.

我的推荐.如果要在大型表上使用它们，请了解数据库中的SQL执行计划.关联的子查询可以带来SQL性能的最佳或最差.

My recommendation. If you are going to use them on large tables, learn about SQL execution plans in your database. Correlated subqueries can bring out the best or the worst in SQL performance.

可能的编辑.这更等效于OP中的脚本:

Possible Edit. This is more equivalent to the script in the OP:

SELECT distinct t1.*
FROM tbl T1 inner join
     tbl t2
     on t1.id = t2.id and
        t1.amt < t2.amt

这篇关于在相关子查询上使用运算符EXISTS的说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！