SQL“EXISTS"的性能用法变体 [英] Performance of SQL "EXISTS" usage variants

问题描述

下面三个SQL语句的性能有什么不同吗?

Is there any difference in the performance of the following three SQL statements?

SELECT * FROM tableA WHERE EXISTS (SELECT * FROM tableB WHERE tableA.x = tableB.y)

SELECT * FROM tableA WHERE EXISTS (SELECT y FROM tableB WHERE tableA.x = tableB.y)

SELECT * FROM tableA WHERE EXISTS (SELECT 1 FROM tableB WHERE tableA.x = tableB.y)

它们都应该工作并返回相同的结果集.但是内部 SELECT 选择 tableB 的所有字段、一个字段还是只选择一个常量有关系吗?

They all should work and return the same result set. But does it matter if the inner SELECT selects all fields of tableB, one field, or just a constant?

当所有语句的行为都相同时，是否有最佳实践?

Is there any best practice when all statements behave equal?

推荐答案

关于 EXISTS 子句的真相是 SELECT 子句不在 EXISTS 子句中计算 - 您可以尝试:

The truth about the EXISTS clause is that the SELECT clause is not evaluated in an EXISTS clause - you could try:

SELECT * 
  FROM tableA 
 WHERE EXISTS (SELECT 1/0 
                 FROM tableB 
                WHERE tableA.x = tableB.y)

...并且应该期望除以零错误，但您不会，因为它没有被评估.这就是为什么我的习惯是在 EXISTS 中指定 NULL 来证明 SELECT 可以被忽略:

...and should expect a divide by zero error, but you won't because it's not evaluated. This is why my habit is to specify NULL in an EXISTS to demonstrate that the SELECT can be ignored:

SELECT * 
  FROM tableA 
 WHERE EXISTS (SELECT NULL
                 FROM tableB 
                WHERE tableA.x = tableB.y)

在 EXISTS 子句中重要的是 FROM 和 Beyond 子句 - WHERE、GROUP BY、HAVING 等.

All that matters in an EXISTS clause is the FROM and beyond clauses - WHERE, GROUP BY, HAVING, etc.

这个问题没有考虑到数据库，应该是因为供应商处理事情的方式不同——所以测试，并检查解释/执行计划以确认.版本之间的行为可能会发生变化......

This question wasn't marked with a database in mind, and it should be because vendors handle things differently -- so test, and check the explain/execution plans to confirm. It is possible that behavior changes between versions...

这篇关于SQL“EXISTS"的性能用法变体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！