Re: Hmmm...Perplexed by SQL statements...

In article <1995Feb17.073354.631_at_cho006>, little_c_at_cho006.cho.ge.com (Chris Little) writes:
|> Two SQL questions:
|> ------------------
|>
|> Why do the following SQL statements return different results?
|> The first query returned "no rows selected" while the second
|> one returned many. I would have thought they'd return the
|> same results.
|>
|> select id from a where id not is (select id from b);
|>
|> select id from a where id not is (select id from b where id is not null);
|>
|>
|>
|> Here's another one:
|>
|> update table a set col='UPDATED' where id in (select id from b);
|>
|> If table "b" is empty, every row in table "a" is updated. I was expecting
|> no row in "a" to be updated if table "b" was empty.
|>
|>
|> Thanks in advance.
|> ________________________________________________________________________________
|> Chris Little, Oracle DBA GE Fanuc Automation North America, Inc.
|> P.O. Box 8106, Mail Drop D-18A
|> Charlottesville, VA 22906
|> E_mail: little_c_at_cho000.dnet.ge.com Phone: (804) 978-5945 Fax: 978-5620

It's because of NULL. NULL is 'unknown', hence it is not equal to anything. It is also not not equal to anything. It's a bit tricky, but it does make sense if you think about it. For example:

NULL = ? == NULL
NULL != ? == NULL
NULL > ? == NULL

and so on. Received on Mon Feb 20 1995 - 18:12:19 CET