[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