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

In <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);

                               in



>select id from a where id not is (select id from b where id is not null);

                               in

The NOT IN operator is equivalent to "!= ALL", i.e. "id != ALL rows returned by the subquery". If one row returns a NULL, then "id" cannot be != to it as NULLs fail all comparision tests (except IS 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.

I can't help you there - that seems wrong...

>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

--
Mark Bluemel	Unix/Oracle Trainer and Consultant
		My opinions are my own, but I'll share them
		All solutions to problems are offered "as is"
		and without warranty - you have been warned :-)