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

Scott Urman (surman_at_oracle.com) wrote:
: 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.

Try this:

select a.id from a where a.id not in (select b.id from b);

I think it's getting confused as to which "id" column you are talking about in the inner query.

Also, for performance reasons, try using "not exists":

select a.id from a where not exists (select 1 from b where b.id = a.id);

-- 
  +---------------------------------------------------------------------+
  |  Phillip Huber   Analyst/Programmer   Michigan Technological Univ.  |
  |  Internet: phil_at_mtu.edu    Phone:(906)487-2223    Fax:(906)487-2521 |
  +---------------------------------------------------------------------+