SELECT * versus SELECT 1 when using EXISTS

Question

SELECT * versus SELECT 1 when using EXISTS

mikedotnet2

Old Hand

Points: 315
More actions
September 11, 2009 at 12:51 pm

#209859

When using EXISTS is there a difference when using EXISTS (SELECT 1...) OR EXISTS(SELECT *...). I've seen differing opinions on this.
For example this was an argument for using "SELECT 1" or a constant: http://books.google.com/books?id=lBdTTXpVc3wC&pg=PA247&lpg=PA247&dq=SQL+SERVER+EXISTS+SELECT+*+VERSUS+SELECT+1&source=bl&ots=sbwNZKcDTX&sig=9e8iCswWhkIb9WFOaqcXJtDcXfY&hl=en&ei=L5qqSvOQL9CYlAeT6KncBg&sa=X&oi=book_result&ct=result&resnum=4#v=onepage&q=&f=false
While I've seen posts on other forums where people have said that it doesn't matter,
because the SELECT list is ignore when coupled with EXISTS.
My apologies. Found a similar post that argues this very topic on the forum.
http://www.sqlservercentral.com/Forums/Topic453737-338-1.aspx?Highlight=EXISTS

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

I've seen arguments both ways, but I always recommend trying both solutions yourself, checking query plans, and deciding which is better in your situation. I suspect both will produce the same plan, but it's always best to test it out.

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 2

in case of exists/no exsits - it is the only place where you can use star without loosing performance and maitainability

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

I agree. I also agree with very specifically with what Adam said. Even if the pros say something, you should always test it yourself. The reason is that a lot of folks also believe in certain myths and end up posting what they've heard rather than what they've tested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 4

Jeff - so in your opinion using star in exists/no exists queries is bad? (or something more then a bad habit?)

Garadin One Orange Chip Points: 29613 More actions · Answer 5

I think he was just advocating testing things yourself and not always taking other people's word for it, not taking a side on this issue in particular. IIRC, 1 and * do exactly the same thing in exists because exists doesn't actually return data, it merely verifies the existence of a row matching criteria, so your select is more or less ignored.

Guess I should test it myself to verify that though :hehe:.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 6

Marcin Gol [SQL Server MVP] (9/11/2009)
in case of exists/no exsits - it is the only place where you can use star without loosing performance and maitainability

...COUNT(*) versus COUNT(1) is the other example.

Star versus constant or NULL - it makes no difference in modern SQL Engines. DB2 and Oracle had a preference for indexes columns and constants respectively way way way back in the day, but no longer.

I don't expect anyone to take my word for it though. * shrug *

Some people prefer not to use SELECT * since it makes searching for dumb uses of the construct harder.

Some people prefer SELECT * because it mirrors the COUNT(*) idea.

Some people prefer SELECT * because SELECT [constant] is so arbitrary.

I like star.

I find COUNT(1) particularly amusing since the XML query plan still shows the operation as "count_star" 😀

...EXISTS (SELECT NULL seems confused to me.

...EXISTS (SELECT 42.9853 seems peculiar, but equivalent to using 1 or 0 or whatever.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Garadin (9/12/2009)
I think he was just advocating testing things yourself and not always taking other people's word for it, not taking a side on this issue in particular. IIRC, 1 and * do exactly the same thing in exists because exists doesn't actually return data, it merely verifies the existence of a row matching criteria, so your select is more or less ignored.
Guess I should test it myself to verify that though :hehe:.

Correct... not advocating either method (mostly because it doesn't make a difference in performance) but am advocating that people actually test for themselves and find out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mikedotnet2 Old Hand Points: 315 More actions · Answer 8

Thanks for the replys. I agree that checking it out myself is always the best route, and I generally don't take someones word (especially on the internet) at face value without verifying it myself.

That being said, I had no idea how to verify this statement from the link above: "However, from an 'internal' standpoint, SELECT * causes the SQL engine to check the data dictionary unnecessarily" and for this reason it seems the author was advocating the use of a constant instead of SELECT *.

How would you test this? Would something like this show in an execution plan?

Paul White SSC Guru Points: 150467 More actions · Answer 9

Dude, it's a myth. Metadata is never checked for COUNT(*) or EXISTS(SELECT *...a quick Google will provide you with testimony to that fact from Itsik Ben-Gan, Erland Sommarskog, Joe Celko...anyone you happen to believe in 🙂

There is no difference between the star syntax and the constant-value syntax. None, aside from the spelling!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mikedotnet2 Old Hand Points: 315 More actions · Answer 10

Relax. My reply was more towards those that said "try it and find out". I realize that I'm not going to get paged in the middle of the night because someone used "SELECT *" instead of "SELECT 1". I just wasn't sure how to truly disprove or prove it.

I'll leave the dead horse alone.

Paul White SSC Guru Points: 150467 More actions · Answer 11

mikedotnet2 (9/14/2009)
Relax. My reply was more towards those that said "try it and find out". I realize that I'm not going to get paged in the middle of the night because someone used "SELECT *" instead of "SELECT 1". I just wasn't sure how to truly disprove or prove it.
I'll leave the dead horse alone.

You misread. It was a light-hearted reply, at least in intent.

As far as testing it is concerned - you can't directly see what the algebrizer and optimizer do internally of course, but you can get an idea from looking at the XML show plan for COUNT(*) versus COUNT(1) for example:

SELECT COUNT(1) produces:

See how the plan converts COUNT(constant) to the "countstar" aggregate (the scalar string changes too!)

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mikedotnet2 Old Hand Points: 315 More actions · Answer 12

I apologize. Hard to read tone sometimes and I took your reply as you were frustrated by the question.

Thanks for the info. I never checked out COUNT(1), but I have the same habit with that.

I just recommended to someone the other day to use EXISTS (SELECT 1.... and got on a kick to find out if it truly mattered. Started researching and kept running across stuff like this: http://www.sqlservercentral.com/blogs/andy_warren/archive/2007/10/30/if-exists-select-1-vs-if-exists-select.aspx which is why I got in the habit to begin with.

Anyway, thanks again. I'll just chalk it up to much ado about nothing and move on. 🙂

Mauve SSChampion Points: 11316 More actions · Answer 13

I prefer to use SELECT NULL vs. SELECT * or SELECT 'x' or SELECT 1 for my existance tests. Selecting a NULL is a bit more indicative that nothing is being selected. As in:

IF EXISTS (SELECT NULL FROM SomeTable WHERE SomeCondition)

I discovered the SELECT NULL syntax by observing the internal SQL statements issued by Oracle when it is checking FK constraints. So if it was good syntax for the engine, then it must be good for us programmers.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 14

JohnG (9/15/2009)
I prefer to use SELECT NULL vs. SELECT * or SELECT 'x' or SELECT 1 for my existance tests. Selecting a NULL is a bit more indicative that nothing is being selected.

It makes no difference in SQL Server so I guess we are free to indulge whatever works.

Personally I find the combination of EXISTS and NULL somewhat muddled...if NULL exists?! :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi