IF EXISTS in T-SQL

Question

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

User · Answer

There s no need for  else  in this case   IF EXISTS SELECT    FROM  table1  WHERE Name  John    return 1 return 0

User · Answer

Yes it stops execution so this is generally preferable to HAVING COUNT     gt  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

[sql-server] IF EXISTS in T-SQL

Examples related to sql-server

Examples related to tsql

Examples related to exists