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?
Answer
Yes it stops execution so this is generally preferable to HAVING COUNT(*) > 0 which often won't.
With 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.