[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)
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.