If we have a
SELECT statement inside an
IF EXISTS, does the execution stop as soon as it finds a record in the table? For example:
IF EXISTS(SELECT * FROM table1 WHERE Name='John' ) return 1 else return 0
If a row exists in the table with the name = John, does it stops execution and returns 1 or does it traverses through the entire table looking for more matches?
This question is tagged with
~ Asked on 2011-09-07 00:36:31
Yes it stops execution so this is generally preferable to
HAVING COUNT(*) > 0 which often won't.
EXISTS if you look at the execution plan you will see that the actual number of rows coming out of
table1 will not be more than 1 irrespective of number of matching records.
In some circumstances SQL Server can convert the tree for the
COUNT query to the same as the one for
EXISTS during the simplification phase (with a semi join and no aggregate operator in sight) an example of that is discussed in the comments here.
For more complicated sub trees than shown in the question you may occasionally find the
COUNT performs better than
EXISTS however. Because the semi join needs only retrieve one row from the sub tree this can encourage a plan with nested loops for that part of the tree - which may not work out optimal in practice.
~ Answered on 2011-09-07 00:38:45
There's no need for "else" in this case:
IF EXISTS(SELECT * FROM table1 WHERE Name='John' ) return 1 return 0
~ Answered on 2016-01-20 17:05:44