Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Exists / not exists: 'select 1' vs 'select field'

Tags:

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.

like image 608
anonxen Avatar asked Oct 20 '14 08:10

anonxen


People also ask

What is SQL Select 1?

The statement 'select 1' from any table name means that it returns only 1. For example, If any table has 4 records then it will return 1 four times.

Can I use exists in select statement?

The result of EXISTS is a boolean value True or False. It can be used in a SELECT, UPDATE, INSERT or DELETE statement.

Why count 1 is faster than count (*)?

The simple answer is no – there is no difference at all. The COUNT(*) function counts the total rows in the table, including the NULL values. The semantics for COUNT(1) differ slightly; we'll discuss them later. However, the results for COUNT(*) and COUNT(1) are identical.

Which is better not in or not exists?

execution planner time may be same but execution results can differ so there is a difference. NOT IN will produce unexpected results if you have NULL in your dataset (see buckley's answer). Best to use NOT EXISTS as a default.


2 Answers

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.

like image 141
Patrick Hofman Avatar answered Oct 01 '22 14:10

Patrick Hofman


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 *

like image 28
TheGameiswar Avatar answered Oct 01 '22 13:10

TheGameiswar