存在/不存在:“选择1"与“选择字段" [英] Exists / not exists: 'select 1' vs 'select field'

问题描述

这两者中的哪一个会表现更好(最近，我被指控不小心使用我的代码，因为我在Oracle中使用了后者)

Which one of the two would perform better(I was recently accused of not being careful with my code because I used the later in Oracle):

Select * 
from Tab1
Where (not) exists(Select 1 From Tab2 Where Tab1.id = Tab2.id)


Select * 
from Tab1
Where (not) exists(Select Field1 From Tab2 Where Tab1.id = Tab2.id)

还是两个都一样?

请同时从SQL Server和Oracle角度回答.

Please answer both from SQL Server perspective as well as Oracle perspective.

我搜索了Google邮件(主要是从sql-server端)，尽管我目前的观点/假设是两个RDMBS的优化器都足够成熟，以至于我理解从RDMBS所需要的所有信息，但仍对此进行了很多辩论.子查询是一个布尔值.

I have googled (mostly from sql-server side) and found that there is still a lot of debate over this although my present opinion/assumption is the optimiser in both the RDMBS are mature enough to understand that all that is required from the subquery is a Boolean value.

推荐答案

是的，它们是相同的. exists检查子查询中是否至少有一行.如果是这样，它将求值为true.子查询中的列没有任何关系.

Yes, they are the same. exists checks if there is at least one row in the sub query. If so, it evaluates to true. The columns in the sub query don't matter in any way.

根据 MSDN ，exists:

指定子查询以测试是否存在行.

Specifies a subquery to test for the existence of rows.

和 Oracle :

EXISTS条件测试子查询中是否存在行.

An EXISTS condition tests for existence of rows in a subquery.

也许 MySQL文档更具解释性:

传统上，EXISTS子查询以SELECT *开头，但可以以SELECT 5或SELECT column1或任何其他形式开头. MySQL会忽略此类子查询中的SELECT列表，因此没有区别.

这篇关于存在/不存在:“选择1"与“选择字段"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！