Re: [GENERAL] null and =

Slavica Stefic wrote:

> Hello,
>
> is null = null true or also null ?

NULL = NULL is also NULL (or more explicity, UNKNOWN, implemented as NULL).
Since NULL means "unknown/not applicable" we don't know whether two
"unknowns" are, in fact, equal, and that is what the standard says - at
least according to Joe Celko...

>
> more precisely I have this kind of situation in a mission critical
> project and I'm,sadly, not an expert in SQL.
> But until now I used null values with a specific meaning in my database,
> and I didn't knew that
> I would come in this situation:
>
> =>create table dummy (a int, b int);
> insert into dummy values (1);
> insert into dummy values (2);
> insert into dummy values (3);
>
> --- this work as expected
> =>select * from dummy where a = 1 and a in (select a from dummy where a
> != 3 );
> a|b
> -+-
> 1|
> (1 row)
>
> --- this one also
> => select a from dummy where a = 1 intersect select a from dummy where a
> != 3 ;
> a
> -
> 1
> (1 row)
>
> ---- !!!!!!!!
> => select a,b from dummy where a = 1 intersect select a,b from dummy
> where a != 3 ;
> a|b
> -+-
> (0 rows)

I would avoid using the INTERSECT/EXCEPT code since the query rewriter
rewrites these to IN clauses which cannot use indexes. As soon as the tables
grow beyond more than a couple hundred rows, the statment becomes unusable.
Instead, I would use a correlated subquery with an EXISTS/NOT EXISTS test
against the criteria for which you are searching:

SELECT t1.a, t1.b FROM dummy t1
WHERE EXISTS (SELECT t2.a FROM dummy t2 WHERE t1.a = t2.a)
....

then, if you need a comparison of the entire row in the correlated subquery,
you could use a clause such as

SELECT t1.a, t1.b FROM dummy t1
WHERE EXISTS (SELECT t2.a FROM dummy t2 WHERE t1.a = t2.a AND
t1.b IS NULL and t2.b IS NULL);

Hope that helps,

Mike