# Thread: The opposite of UNION

1. ## 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. 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. What about something like this?
Code:
```SELECT n, title
FROM say2
WHERE n=1 or n =3 AND n != 5```

4. 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?

5. 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. 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')```

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

#### Posting Permissions

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