SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    The opposite of UNION

    Code:
     say2
    (n)  title
    
    (1)   Mom
    (2)   WTD
    (3)   SUN
    (4)   Earth
    (5)   Asia
    (6)   Europe
    (7)   Venus
    (8)   Korea
    Let's suppose I have my table named "say2" like the above.

    The code1 below produces the result1 below.
    Code:
    code1
    SELECT n, title
    FROM say2
    WHERE n=1 or n =3 or n =5
    
    result1
    (1) Mom
    (3) SUN
    (5) Asia
    And the code2 below produces the result2 below.
    Code:
    code2
    SELECT n, title
    FROM say2
    WHERE n=5 or n =7 or n =8
    
    result2
    (5) Asia
    (7) Venus
    (8) Korea
    Let's UNION them like the below code3 and its result3.
    Code:
    code3
    SELECT n, title
    FROM say2
    WHERE n=1 or n =3 or n =5
    UNION
    SELECT n, title
    FROM say2
    WHERE n=5 or n =7 or n =8
    
    result3
    (1) Mom
    (3) SUN
    (5) Asia
    (7) Venus
    (8) Korea
    Now I think It would be better if there is the opposite of UNION in mySQL.
    UNION means result1 + result2.
    I mean that the opposite of UNION means result1 - result2.

    The following would-be code4 doesn't work correctly, but I hope it shows what I want.
    Code:
    would-be code4
    SELECT n, title
    FROM say2
    WHERE n=1 or n =3 or n =5
    the opposite of UNION 
    SELECT n, title
    FROM say2
    WHERE n=5 
    
    target result4
    (1) Mom
    (3) SUN
    
    or 
    
    would-be code5
    SELECT n, title
    FROM say2
    WHERE n=1 or n =3 or n =5
    the opposite of UNION 
    SELECT n, title
    FROM say2
    WHERE n=5 or n =7 or n =8
    
    target result5
    (1) Mom
    (3) SUN

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    In Oracle there is MINUS, this does not exist in MySQL but there are alternatives in achieving the same thing:


    SELECT a.n,
    a.title
    FROM say2 a
    LEFT JOIN say2 b
    ON ( a.n = b.n
    AND a.title = b.title
    AND b.n IN ( 5, 7, 8 ) )
    WHERE a.n IN ( 1, 3, 5 )
    AND b.n IS NULL;

    Here is the output tested:


    mysql> SELECT n, title
    -> FROM say2
    -> WHERE n=1 or n =3 or n =5
    -> ;
    +------+-------+
    | n | title |
    +------+-------+
    | 1 | Mom |
    | 3 | SUN |
    | 5 | Asia |
    +------+-------+
    3 rows in set (0.00 sec)

    mysql> SELECT n, title
    -> FROM say2
    -> WHERE n=5 or n =7 or n =8
    -> ;
    +------+-------+
    | n | title |
    +------+-------+
    | 5 | Asia |
    | 7 | Venus |
    | 8 | Korea |
    +------+-------+
    3 rows in set (0.00 sec)

    mysql> SELECT n, title
    -> FROM say2
    -> WHERE n=1 or n =3 or n =5
    -> UNION
    -> SELECT n, title
    -> FROM say2
    -> WHERE n=5 or n =7 or n =8
    -> ;
    +------+-------+
    | n | title |
    +------+-------+
    | 1 | Mom |
    | 3 | SUN |
    | 5 | Asia |
    | 7 | Venus |
    | 8 | Korea |
    +------+-------+
    5 rows in set (0.00 sec)


    mysql> SELECT a.n,
    -> a.title
    -> FROM say2 a
    -> LEFT JOIN say2 b
    -> ON ( a.n = b.n
    -> AND a.title = b.title
    -> AND b.n IN ( 5, 7, 8 ) )
    -> WHERE a.n IN ( 1, 3, 5 )
    -> AND b.n IS NULL;
    +------+-------+
    | n | title |
    +------+-------+
    | 1 | Mom |
    | 3 | SUN |
    +------+-------+
    2 rows in set (0.00 sec)

  3. #3
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    What about something like this?
    Code:
    SELECT n, title
    FROM say2
    WHERE n=1 or n =3 AND n != 5

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kduv View Post
    What about something like this?
    Code:
    SELECT n, title
    FROM say2
    WHERE n=1 or n =3 AND n != 5
    nope

    the way mixed ANDs and ORs are evaluated, ANDs take precedence over ORs, so this --
    Code:
    WHERE n=1 or n =3 AND n != 5
    will be evaluated like this --
    Code:
    WHERE ( n=1 ) or ( n =3 AND n != 5 )
    this --
    Code:
     n =3 AND n != 5
    will be reduced to this --
    Code:
    n = 3
    and anyway, what happened to 7 and 8?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    I guess I'm a little confused as to why someone would need a "negative" union when you can either use parenthesis or just specify what you want ... as opposed to specifying what you want then a negative?

    I think a practical use-case may help me wrap my mind around it some.

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,255
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    In this particular case, a combination of IN and NOT IN would get the desired results.

    Code:
    SELECT n
             , title
      FROM say2
     WHERE n IN (1, 3, 5)
       AND n NOT IN (5,7,8)
    or if you have more complex criteria, you can do something similar but use sub queries

    Code:
    SELECT n
         , title
      FROM say2
     WHERE n IN (SELECT n FROM say2 WHERE sky = 'blue')
       AND n NOT IN (SELECT n FROM say2 WHERE grass = 'brown')
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    In this particular case, a combination of IN and NOT IN would get the desired results.
    which is what you have to do, since mysql doesn't support the EXCEPT operator

    1,3,5 UNION 5,7,8 produces 1,3,5,7,8

    1,3,5 EXCEPT 5,7,8 produces 1,3

    but reading between the lines, i think joon wants

    1,3,5 SOMETHING 5,7,8 produces 1,3,7,8

    i think it's time for him to respond to the half dozen replies he's already got


    as for those other examples, joon's tables don't contain sky or grass columns

    seriously, you're just gonna confuse the guy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •