SQL Server equivalent of Oracle''s "MINUS" operator?

  • Greg J

    SSCarpal Tunnel

    Points: 4288

    In Oracle, I was able to use a MINUS operator on result sets.  It fits in the same place as a UNION would, since it operates on result sets of different SELECT queries.  It returns the rows that are in the first query that AREN'T in the second.  Prett sure the result set columns had to be identical.  Not sure.

    This operator could come in handy for me right now.  Does anyone know how to effect this in SQL Server 2000?

    Eg:

    Tbl1 (a, b, c) contains --

    1, 'uno', 'one'

    2, 'dos', 'two'

    3, 'tres','three'

     

    Tbl2 (a, b, c) contains --

    2, 'dos', 'two'

     

    SELECT a, b, c FROM Tbl1

    MINUS

    SELECT a, b, c FROM Tbl2

    would yield the following resultset:

    1, 'uno', 'one'

    3, 'tres','three'

     

    Seems like this could also be effected by using some combinations of joins, but I'm not coming up with it.  OUTERs don't give me what I want, CARTESIANs I've probably used 3 times in my career, and INNERs do exactly the opposite of what I want.

    Thanks in advance.  SQLServerCentral.com rocks the Casbah.

    Greg

  • David McFarland

    SSChampion

    Points: 11815

    Untested, but this should work for you:

    SELECT

     Spanish

     ,English

    FROM

     Tbl1

    WHERE

     NOT EXISTS (

        SELECT

         1

        FROM

         Tbl2

        WHERE

         Tbl1.Spanish = Tbl2.Spanish

         AND Tbl1.English = Tbl2.English)

        

  • Greg J

    SSCarpal Tunnel

    Points: 4288

    Thanks David, but I think a "NOT EXISTS" subquery for millions of rows and 30+ columns will take too long for my sitch.  I remember MINUS was pretty quick for identical record sets and heavy row counts.

     

     

  • Lowell

    SSC Guru

    Points: 323444

    isn't this just a full outer join where the right table is null?

    SELECT

     Spanish

     ,English

    FROM

     Tbl1

    FULL OUTER JOIN TBL2

    ON Tbl1.Spanish = Tbl2.Spanish

         AND Tbl1.English = Tbl2.English

    WHERE Tbl2.English IS NULL

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • David McFarland

    SSChampion

    Points: 11815

    Is there any single column that would work? While I realize the above was just sample data, if you have a single unique column that would work between the two tables, such as the first column above, you can use a single column version of my code above, and as long as that column was indexed on your tables, it would be fairly snappy even with the NOT EXISTS.

    I'm trying to think of workarounds, and there probably are some that work on a case by case basis, but NOT EXISTS is the typical method of replacing the MINUS operator in moves from Oracle to SQL Server. If it makes any difference, many of us would love to see a MINUS operator (and a FIRST, but that's a different story) in SQL Server, so perhaps it will one day be part of the language.

  • stax68

    SSChampion

    Points: 11711

    declare @Tbl1 table(a int, b varchar(10), c varchar(10))

    declare

    @Tbl2 table(a int, b varchar(10), c varchar(10))

    insert

    @tbl1

    select

    1, 'uno', 'one' union all

    select

    2, 'dos', 'two' union all

    select

    3, 'tres','three'

    insert

    @tbl2

    select

    2, 'dos', 'two' union all

    select

    4, 'quatro', 'four'

    ---------

    select null 'MINUS/EXCEPT: like a left anti-join - A&~B' where 1=2

    --setwise operator(v9 only):

    SELECT

    a, b, c FROM @Tbl1 except SELECT a, b, c FROM @Tbl2

    --join

    SELECT

    DISTINCT t.a, t.b, t.c

    FROM

    @Tbl1 t LEFT JOIN @Tbl2 t2 on t.a = t2.a and t.b = t2.b and t.c = t2.c

    WHERE

    t2.a is null

    ---------

    select

    null 'INTERSECT: like an inner join - A&B' where 1=2

    --setwise operator(v9 only):

    SELECT

    a, b, c FROM @Tbl1 INTERSECT SELECT a, b, c FROM @Tbl2

    --join

    SELECT

    distinct t.a, t.b, t.c

    FROM

    @Tbl1 t JOIN @Tbl2 t2 on t.a = t2.a and t.b = t2.b and t.c = t2.c

    ---------

    select

    null 'UNION: like a full outer join - A|B' where 1=2

    --setwise operator:

    SELECT

    a, b, c FROM @Tbl1 union SELECT a, b, c FROM @Tbl2

    --join:

    SELECT

    distinct coalesce(t.a,t2.a) a, coalesce(t.b,t2.b) b, coalesce(t.c,t2.c) c

    FROM

    @Tbl1 t FULL OUTER JOIN @Tbl2 t2 on t.a = t2.a and t.b = t2.b and t.c = t2.c

    ---------

    select

    null '"PENUMBRA": like a full outer anti-join - A^B' where 1=2

    --join only

    SELECT

    distinct coalesce(t.a,t2.a) a, coalesce(t.b,t2.b) b, coalesce(t.c,t2.c) c

    FROM

    @Tbl1 t full outer join @Tbl2 t2 on t.a = t2.a and t.b = t2.b and t.c = t2.c

    where

    t.a is null

    or t2.a is null

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • David le Quesne

    SSCertifiable

    Points: 5229

    The MSDN website  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag03/html/Set-OperationAlternatives.asp gives the following syntax for simulating the ANSI SQL   EXCEPT (MINUS) operator in T-SQL

    SELECT col1, col2

    FROM ( SELECT DISTINCT 'U' AS setname, col1, col2 FROM U

                UNION ALL

              SELECT DISTINCT NULL, col1, col2 FROM V)

          AS D1

    GROUP BY col1, col2

    HAVING COUNT(*) = 1

    AND MAX(setname) = 'U'

    with the following explanation...

    "The derived table D1 contains distinct rows from each input and a pseudo column called setname, which contains the literal 'U' for U's rows and NULL for V's rows. The code groups the rows from D1 by col1, col2. The COUNT(*) = 1 expression in the HAVING clause ensures that the query returns only the rows that appear in one of the inputs, and MAX(setname) = 'U' ensures that it returns only the rows that appear in U."

    David

    If it ain't broke, don't fix it...

  • Greg J

    SSCarpal Tunnel

    Points: 4288

    ABOVE AND BEYOND.  Thanks a heap you guys.  I'll have something in place today and I'll update this string with deets.

     

    Thanks again fellas.

    Greg

  • Rob Sanguin

    SSChasing Mays

    Points: 610

    Response to stax - some nice code there!

    Just for completeness I've got a set version of the penumbra, although it seems to be a bit more expensive to execute:-

    (SELECT a, b, c FROM @Tbl1 except SELECT a, b, c FROM @Tbl2)

    union

    (SELECT a, b, c FROM @Tbl2 except SELECT a, b, c FROM @Tbl1).

    Rob.

  • stax68

    SSChampion

    Points: 11711

    Oh and by the way (clears throat) if there might be nulls SET ANSI_NULLS OFF for the joins...just a detail...

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • stax68

    SSChampion

    Points: 11711

    You also need to identify a non-nullable column on which to do your NULL checks to implement anti-joins... hmm, need to stop posting the first thought that comes into my head.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

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

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