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.
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.
The result of EXISTS is a boolean value True or False. It can be used in a SELECT, UPDATE, INSERT or DELETE statement.
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.
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.
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.
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 *
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With