# The opposite of UNION

• Jun 14, 2012, 14:18
dotJoon
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```
• Jun 14, 2012, 23:58
rcashell
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)
• Jun 18, 2012, 16:14
kduv
Code:

```SELECT n, title FROM say2 WHERE n=1 or n =3 AND n != 5```
• Jun 18, 2012, 17:03
r937
Quote:

Originally Posted by kduv
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?
• Jun 18, 2012, 17:21
kduv
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.
• Jun 19, 2012, 09:05
DaveMaxwell
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')```
• Jun 19, 2012, 09:28
r937
Quote:

Originally Posted by DaveMaxwell
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