Menu
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?
295
The SQL EXISTS Operator The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
The OBJECT_ID() returns the database object identification number of a schema-scoped object. If the object exists, it will not return NULL, and therefore, we can drop the table. If the object doesn't exist, or if you don't have access to it, the function returns NULL, and the DROP TABLE statement will not run.
How do you check if a table contains any data in SQL? Using EXISTS clause in the IF statement to check the existence of a record. Using EXISTS clause in the CASE statement to check the existence of a record. Using EXISTS clause in the WHERE clause to check the existence of a record.
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.
155
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
