Except

  • Pulivarthi Sasidhar

    SSCertifiable

    Points: 6706

    Comments posted to this topic are about the item Except

  • Ed Wagner

    SSC Guru

    Points: 286959

    Nice and simple, but illustrate a set operator, which is usually a very efficient approach. Thanks.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Haven't used the EXCEPT operator much but am familiar how it would work.

  • Ken Wymore

    SSCoach

    Points: 16415

    Isn't this exactly the same as NOT EXISTS? Is there a reason to use one method over the other?

    I rewrote the query using not exists and got the same results and same execution plan. "Not Exists" version below.

    SELECT

    *

    FROM

    ( SELECT

    1 AS Id

    UNION ALL

    SELECT

    2

    UNION ALL

    SELECT

    3

    UNION ALL

    SELECT

    4

    UNION ALL

    SELECT

    3

    ) TblA

    WHERE NOT EXISTS

    (SELECT *

    FROM(SELECT

    1 AS Id

    UNION

    SELECT

    2

    UNION

    SELECT

    3

    UNION

    SELECT

    4

    UNION

    SELECT

    3

    ) TblB

    WHERE TblB.Id=TblA.Id);

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    I selected "3 rows", then just as I was about to submit my answer, I noticed the scrollbar. After scrolling down, I quickly changed my answer.

    Apparently, I've had just enough coffee to function today. I'd better get another cup, just to be sure.

  • This was removed by the editor as SPAM

  • sqlnaive

    SSCoach

    Points: 17435

    Very good question..

  • drew.allen

    SSC Guru

    Points: 76658

    Ken Wymore (5/18/2016)


    Isn't this exactly the same as NOT EXISTS? Is there a reason to use one method over the other?

    I rewrote the query using not exists and got the same results and same execution plan. "Not Exists" version below.

    SELECT

    *

    FROM

    ( SELECT

    1 AS Id

    UNION ALL

    SELECT

    2

    UNION ALL

    SELECT

    3

    UNION ALL

    SELECT

    4

    UNION ALL

    SELECT

    3

    ) TblA

    WHERE NOT EXISTS

    (SELECT *

    FROM(SELECT

    1 AS Id

    UNION

    SELECT

    2

    UNION

    SELECT

    3

    UNION

    SELECT

    4

    UNION

    SELECT

    3

    ) TblB

    WHERE TblB.Id=TblA.Id);

    In this simple example, they are equivalent, but there are two main conditions where EXCEPT is simpler.

  • When you are dealing with NULLs.

    The WHERE clause in the NOT EXISTS version uses three-value logic (true, false, unknown) whereas the EXCEPT clause uses two-value logic (true, false). You need to handle logic to the NOT EXISTS version to determine whether the corresponding fields in both results are both NULL.

  • When you are comparing multiple fields, particularly as the number of fields grow.

    With multiple fields, your WHERE clause in the NOT EXISTS version quickly becomes more complex, particularly if you have to handle NULL values.

  • Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • paul s-306273

    SSChampion

    Points: 10602

    92% right!

    Pretty good for QOTD.

  • Viewing 9 posts - 1 through 9 (of 9 total)

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