simple t sql question what does ALL do in select ALL * from aaab

Question

Post reply

simple t sql question what does ALL do in select ALL * from aaab

olibbhq

SSCarpal Tunnel

Points: 4034
More actions
August 2, 2010 at 11:55 am

#223958

Hi,
I have come across code that includes the ALL definition e.g.
select all * from aaabb
can you please tell me what the ALL I couldnt find anything on BOL
Many Thanks, yes I am stupid.
Oliver

Viewing 6 posts - 1 through 5 (of 5 total)

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

never heard of that except when used with UNION

The probability of survival is inversely proportional to the angle of arrival.

jvanderberg SSCertifiable Points: 6762 More actions · Answer 2

Commonly, "SELECT *" is read as "SELECT ALL". Perhaps you've confused that?

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 3

SELECT ALL * and SELECT DISTINCT * are on the same order. DISTINCT excludes duplicates from a result set while ALL (the implied default if DISTINCT is not specified) is the same as SELECT *.

To my knowledge no one ever specifies the ALL option because it is not required.

diegocroitoru SSC Enthusiast Points: 131 More actions · Answer 4

Hi,

It has to do with how SQL manages defaults.

For UNION queries, DISTINCT is the default. So if you really want to preserve duplicates you'll have to explicitly put ALL, but you'll almost never use UNION DISTINCT because DISTINCT is the default.

The same goes with SELECT ALL. The default is to bring all duplicates, so it's the same to write Select ALL or SELECT.

However you'll need to enter DISTINCT keyword if you'd like to eliminate duplicates.

Hope I brought some clarity.

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 5

Thank you very much for your replies I am much clearer now on this. Really appreciate it.

Obviously the code writer was being extra thorough when writing the script.

Many Thanks,

Oliver