IF EXISTS in T-SQL
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.