[ic] some issues with 5.0 and oracle
Jon Jensen
jon at endpoint.com
Wed Jan 21 17:54:44 EST 2004
On Wed, 21 Jan 2004, Stefan Hornburg wrote:
> > Are you sure it's not failing because of the != comparison? SQL's
> > standard inequality operator is <> and not != so that could be it.
>
> At least not with MySQL:
>
> alter table products add column foo int(1);
>
> mysql> select distinct foo from products;
> +------+
> | foo |
> +------+
> | NULL |
> +------+
> 1 row in set (0.05 sec)
>
> mysql> select count(*) from products where foo <> 1;
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.05 sec)
Equality and inequality operators do not operate on NULLs. So if your
table contains only null "foo" fields, it is correct to say the count() of
rows where foo <> (or != if you prefer) 1 is 0. This is how SQL NULLs work
-- they are not values; they're the absence of values. See:
http://www.mysql.com/doc/en/Working_with_NULL.html
Incidentally both PostgreSQL and MySQL accept != as a synonym for <> but
it is <> that is defined in SQL, and I do not believe Oracle accepts !=.
Jon
More information about the interchange-users
mailing list