What is wrong with this simple query using NOT IN?

Question

Scheme:

radio_bin.id
radio.id
radio.radio_bin -> radio_bin.id

Inquiries

select *
    from radio_bin

72 lines.

select *
    from radio_bin
    where id in (select radio_bin from radio)

50 lines.

(And FWIW :)

select distinct radio_bin
    from radio

51 lines, including zero.

This is all good. Now:

select *
    from radio_bin
    where id not in (select radio_bin from radio)

0 lines.

Why? Should I get 22 radio_bins. ID numbers that don't have radios pointing to them?

Answer 1

Try this, you have a null value, and a null value is not equal to anything other than a null value

select *
    from radio_bin
    where id not in (select radio_bin from radio where radio_bin  is not null)

See also NOT IN and NULLS , which shows how to use LEFT JOIN or NOT EXISTS

Answer 2

change

in

to

Exists

It works?

See explanation here

Answer 3

, radio_bin.id null?

NOT IN true.

, x NOT IN y NOT (x IN y). x null, (x IN y) null, null, null.

Answer 4

NULL . Oracle, , ANSI SQL...

SQL> select * from t23
  2  where id in ( select id from t42)
  3  /

TXT                                                ID
------------------------------------------ ----------
SAM-I-AM                                            1
KNOX                                                2
FOX                                                 3

SQL> select * from t23
  2  where id not in ( select id from t42)
  3  /

no rows selected

SQL> update t42 set id = 8 where id is null
  2  /

1 row updated.

SQL> select * from t23
  2  where id not in ( select id from t42)
  3  /

TXT                                                ID
------------------------------------------ ----------
LORAX                                               9

SQL>

Answer 5

NOT IN, . , , ( ), !

Negative operations such as <> or NOT LIKE are also very difficult to solve effectively. Try rewriting them differently if possible. if you only check for existence, use IF EXISTS or IF NOT EXISTS to build instead. You can use the index. If you use scanning, you can stop scanning the first time you enter. http://msdn.microsoft.com/en-us/library/ms998577.aspx