The opposite of UNION

 [B]say2
(n)  title[/B]

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

[B]code1[/B]
SELECT n, title
FROM say2
WHERE n=1 or n =3 or n =5

[B]result1[/B]
(1) Mom
(3) SUN
(5) Asia

And the code2 below produces the result2 below.

[b]code2[/b]
SELECT n, title
FROM say2
WHERE n=5 or n =7 or n =8

[b]result2[/b]
(5) Asia
(7) Venus
(8) Korea

Let’s UNION them like the below code3 and its result3.

[b]code3[/b]
SELECT n, title
FROM say2
WHERE n=1 or n =3 or n =5
[COLOR="#FF0000"]UNION[/COLOR]
SELECT n, title
FROM say2
WHERE n=5 or n =7 or n =8

[b]result3[/b]
(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.

[b]would-be code4[/b]
SELECT n, title
FROM say2
WHERE n=1 or n =3 or n =5
[COLOR="#FF0000"]the opposite of UNION[/COLOR]
SELECT n, title
FROM say2
WHERE n=5

[b]target result4[/b]
(1) Mom
(3) SUN

or

[b]would-be code5[/b]
SELECT n, title
FROM say2
WHERE n=1 or n =3 or n =5
[COLOR="#FF0000"]the opposite of UNION[/COLOR]
SELECT n, title
FROM say2
WHERE n=5 [COLOR="#FF0000"]or n =7 or n =8[/COLOR]

[b]target result5[/b]
(1) Mom
(3) SUN


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)

What about something like this?


SELECT n, title
FROM say2
WHERE n=1 or n =3 AND n != 5 

nope :slight_smile:

the way mixed ANDs and ORs are evaluated, ANDs take precedence over ORs, so this –

WHERE n=1 or n =3 AND n != 5 

will be evaluated like this –

WHERE ( n=1 ) or ( n =3 AND n != 5 ) 

this –

 n =3 AND n != 5 

will be reduced to this –

n = 3

and anyway, what happened to 7 and 8?

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.

In this particular case, a combination of IN and NOT IN would get the desired results.


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


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')

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