EXCEPT - 1

  • Toreador

    SSChampion

    Points: 11259

    Easy enough if you know how EXCEPT and related options work.

    But don't quite understand the statement "The key to this is of course the use of DISTINCT".

    There is no use of DISTINCT, so how can it be the key to anything?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Toreador (5/28/2013)


    But don't quite understand the statement "The key to this is of course the use of DISTINCT".

    There is no use of DISTINCT, so how can it be the key to anything?

    First sentence of the explanation, emphasis mine:

    "EXCEPT returns any distinct values from the left query that are not also found on the right query"


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Toreador

    SSChampion

    Points: 11259

    I guess you're right. I'd assumed it meant the keyword DISTINCT, given that it was in uppercase 🙂

  • marlon.seton

    SSCrazy

    Points: 2623

    skanker (5/20/2013)


    Thanks for the question. It might be easy for others, however I learnt something on this. I did not do my research first and thought it would be three.

    Same here.

  • jfgoude

    SSCrazy

    Points: 2586

    Ignored completely the DISTINCT usage

    leearned something today

  • okbangas

    SSChampion

    Points: 11773

    Easy one. This is according to the SQL standard. Unfortunately, SQL Server does not support except all and intersect all, which both is a part of the SQL Standard. There are times where I really missed them, especially except all.



    Ole Kristian VelstadbrĂĄten BangĂĄs - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Dscheypie

    SSCommitted

    Points: 1545

    Also nice piece of information regarding INTERSECT and EXCEPT: NULL values are regarded as the same. A behaviour as if SET ANSI_NULLS were OFF.

    ________________________________________________________
    If you set out to do something, something else must be done first.

Viewing 7 posts - 16 through 22 (of 22 total)

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