Peter Brawley wrote:
> Giuseppe,
>
> >mysql> select 2 not in (1,null,3);
> >+---------------------+
> >| 2 not in (1,null,3) |
> >+---------------------+
> >| NULL |
> >+---------------------+
> >1 row in set (0.00 sec)
>
> ># NOT OK
>
> Isn't that standard SQL behaviour?
Yes, it is. As I said before, I was too quick using this example, while
the problem arises only during a subquery.
Now, others have pointed out that even with subqueries this should be
considered standard behaviour, even though severa people in my workplace
agree that it does not look intuitive.
> NULL is not a value. NOT IN compares
> the values using '=' and correctly returns NULL if any value is NULL ie
> missing, eg for Oracle see http://builder.com.com/5100-6388_14-5319615.html
I see now that this mechanism is intentional.
Thanks for your link.
Giuseppe
--
Giuseppe Maxia
CTO
http://www.StarData.it
MySQL Certified Professional
__ __ __
___ / /____ ________/ /__ _/ /____ _
(_-</ __/ _ `/ __/ _ / _ `/ __/ _ `/
/___/\__/\_,_/_/ \_,_/\_,_/\__/\_,_/
Database is our business
