Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Years ago, it was common to write

where exists (Select * from some_table where some_condition)

Last year I noticed that many t-sql scripts switched to using the number 1 instead of the star

where exists (Select 1 from some_table where some_condition)

just on SO I saw this Oracle example

WHERE EXISTS (SELECT NULL FROM ...

Is this a common pattern with Oracle? And which are the performance arguments to use something like this.

share|improve this question
2  
SO has a discussion of this. stackoverflow.com/questions/424212/… –  Leigh Riffel Jan 7 '11 at 19:14

2 Answers 2

up vote 5 down vote accepted

Short answer: I would hope not!

Even the SELECT 1 idiom may have outlived its usefulness, as database engines have advanced enough to recognize that you don't really want the rows, you just want to know if the rows exist. I've looked at execution plans in SQL 2005, and have generally seen these re-written as joins, even where SELECT * is used.

share|improve this answer

In Oracle it doesn't matter performance wise. You can even put 1/0 there and the statement won't return a divide by zero error because the expression isn't evaluated.

share|improve this answer
1  
Does this need to be closed in favor of relying on the SO question? Is this one of those "migrate the SO to dba.SE"? –  jcolebrand Jan 6 '11 at 5:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.