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

  • 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

  • never heard of that except when used with UNION

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

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

  • 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.

  • 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.

  • 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

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

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