What's wrong with this simple query using NOT IN?

Question

Schema:

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

Queries:

select *
    from radio_bin

72 rows.

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

50 rows.

(And FWIW:)

select distinct radio_bin
    from radio

51 rows, including a null.

That's all good. Now:

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

0 rows.

Why? Shouldn't I get the 22 radio_bin.id numbers that don't have a radio pointing to them?

Answer 1

change

in

to

Exists

Does this work?

See here for an explanation

Answer 2

Try this, you have a null value and null is not equal to anything not even another null

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 you how to use either LEFT JOIN or NOT EXISTS

Answer 3

Is it possible that radio_bin.id is null?

If it is the NOT IN does not evaluate to true.

This is because x NOT IN y is equivalent to NOT (x IN y). If x is null then (x IN y) will returns null because nothing is equal to null, not even null.

Answer 4

Because NULLs really muck things up. The following was run on Oracle but I think this behaviour is standard ANSI SQL behaviour...

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

Avoid using NOT IN, if you plan to SCALE up this query substantially. If you are talking about a small application it shouldn't be a problem, but if you are talking about millions of rows (even potentially) avoid this approach!

Negative operations, such as <> or NOT LIKE, are also very difficult to resolve efficiently. Try to rewrite them in another way if you can. If you are only checking for existence, use the IF EXISTS or the IF NOT EXISTS construct instead. You can use an index. If you use a scan, you can stop the scan at the first occurrence. http://msdn.microsoft.com/en-us/library/ms998577.aspx