Menu
I am using SQL Server 2008 R2
I just want to test if something exists in a table
IF EXISTS (SELECT * FROM ta WHERE ca = 'abc') PRINT 'YES'
IF EXISTS (SELECT ca FROM ta WHERE ca = 'abc') PRINT 'YES'
IF EXISTS (SELECT 1 FROM ta WHERE ca = 'abc') PRINT 'YES'
IF EXISTS (SELECT (1) FROM ta WHERE ca = 'abc') PRINT 'YES'
IF EXISTS (SELECT TOP 1 1 FROM ta WHERE ca = 'abc') PRINT 'YES'
Do they have any differences in result/side effect/performance (no matter how tiny)?
Thank you
727
Key differences between IN and EXISTS OperatorThe IN clause scan all records fetched from the given subquery column, whereas EXISTS clause evaluates true or false, and the SQL engine quits the scanning process as soon as it found a match.
IN works faster than the EXISTS Operator when If the sub-query result is small. If the sub-query result is larger, then EXISTS works faster than the IN Operator. 3. In the IN-condition SQL Engine compares all the values in the IN Clause.
The most important thing to note about NOT EXISTS and NOT IN is that, unlike EXISTS and IN, they are not equivalent in all cases. Specifically, when NULLs are involved they will return different results. To be totally specific, when the subquery returns even one null, NOT IN will not match any rows.
Absolutely no difference - the IF EXISTS(...) will only check for existence of rows based on the WHERE clause in your statement.
Everything else in the statement is irrelevant - doesn't make any difference whether you use SELECT * or SELECT 1 or or SELECT TOP 1 *. Even using SELECT * .... does NOT select all columns from the table - it again just checks for existence of the data based on the WHERE clause.
All five queries have exactly the same execution plan
138
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
