Re: Coding multiple EXCEPTs

On 19-09-16 11:00, Luuk wrote:
> On 19-09-16 08:56, bit-naughty_at_hotmail.com wrote:
>> OK, so I found out from this group that MySQL doesn't have "EXCEPT",
>> you have to
>> use "NOT IN" - ie. "SELECT a EXCEPT b" is "SELECT b NOT IN a". But if
>> I want MULTIPLE exceptions, ie. SELECT a EXCEPT b AND c AND d, ie. the
>> result set of a minus each of the "b" and "c" and "d" - is this:
>> SELECT b AND c AND d NOT IN a ?
>>
>> Aren't there some brackets needed somewhere or something? (like "(b
>> AND c AND d)")?
>>
>> Thanks.
>>
>>
>>
>
> [root_at_test]> select i from abc;
> +------+
> | i |
> +------+
> | 1 |
> | 2 |
> | 3 |
> | 4 |
> +------+
> 4 rows in set (0.00 sec)
>
> [root_at_test]> select i from abc where i not in (select i from abc where
> i>3) and i not in (select i from abc where i<2);
> +------+
> | i |
> +------+
> | 2 |
> | 3 |
> +------+
> 2 rows in set (0.00 sec)
>

[root_at_test]> select i from abc where i not in (select i from abc where i>3 union select i from abc where i<2);
+------+
| i |
+------+
| 2 |
| 3 |
+------+
2 rows in set (0.00 sec) Received on Mon Sep 19 2016 - 11:21:48 CEST