August 30, 2005 at 7:10 am
Hello,
just having a query regarding best practise for IF Exists
Stmt1 - IF EXISTS (SELECT 1 FROM TABLE1 WHERE col1 = 7383)
Stmt2 - IF EXISTS (SELECT * FROM TABLE1 WHERE col1 = 7383)
Which one is better? Is there any advantage of one over the other?
Thanks
Renga
August 30, 2005 at 7:36 am
Same difference. The server doesn't fetch any data in an exists clause, so you don't need to worry about * or 1 (unless you have a group by clause).
August 30, 2005 at 8:46 am
hmmm interesting. I thought "select 1" might cost less resources for engine to validate the boolean experssion (even if data is not returned)
do you know how this internally works? - just for my understanding. Not able to locate a resource for this.
August 30, 2005 at 8:48 am
It just returns true/false. As soons as the engine finds a hit, the query stops with true. I can't tell you much more than that.
August 30, 2005 at 9:53 am
Renga - this is from BOL - but doesn't really add anything to what remi has already said:
"The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are simply testing for the existence of rows that meet the conditions specified in the subquery."
**ASCII stupid question, get a stupid ANSI !!!**
August 31, 2005 at 6:27 am
Thanks rgR'us / Sushila.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply