22

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)

Or are they both same?

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

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.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
anonxen
  • 794
  • 1
  • 8
  • 24

4 Answers4

24

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.

According to MSDN, exists:

Specifies a subquery to test for the existence of rows.

And Oracle:

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

Maybe the MySQL documentation is even more explaining:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
6

I know this is old,but want to add few points i observed recently..

Even though exists checks for only existence ,when we write "select *" all ,columns will be expanded,other than this slight overhead ,there are no differences.

Source:
http://www.sqlskills.com/blogs/conor/exists-subqueries-select-1-vs-select/

Update:
Article i referred seems to be not valid.Even though when we write,select 1 ,SQLServer will expand all the columns ..

please refer to below link for in depth analysis and performance statistics,when using various approaches..

Subquery using Exists 1 or Exists *

Community
  • 1
  • 1
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • 2
    Conor's article there is actually wrong. He suggests that `SELECT *` will expand all column metadata and `SELECT 1` won't. However they both in fact do so. You can see this by denying permissions on a column and running `SELECT 1 WHERE EXISTS (SELECT 1 FROM T);` which can unexpectedly fail with `The SELECT permission was denied on the column 'Foo'` or by simply timing the effect of adding more columns with both or looking in a debugger http://stackoverflow.com/a/6140367/73226 – Martin Smith Sep 11 '16 at 12:32
  • @MartinSmith:Thanks a lot,i read your answer long back,but some how missed few things.Thanks again for debunking the myth – TheGameiswar Sep 11 '16 at 13:15
3

The expression in the subquery's column list matters absolutely nothing, it will not even be executed:

select * from dual t1
where exists (
    select 1/0 from dual t2
         --^^^ division by 0  
    where t2.dummy = t2.dummy)
/

DUMMY
--------
X
0xdb
  • 3,539
  • 1
  • 21
  • 37
1

The only thing to watch out for in my experience between using "EXISTS(SELECT * ..." and "EXISTS(SELECT 1 ..." is that "*" is not allowed in schema-bound objects -- it will throw:

Syntax '*' is not allowed in schema-bound objects.

Matthew Sidorick
  • 41
  • 3