CODEWITHSUNDEEP
mysqlsql
Arthur
Arthur

Reputation: 3473

MySQL - Select from with not in statement

I has very strange situation with MySQL. I have 2 tables. First table has column ID (primary key) and has ~560 000 rows. Second table has column IDBook (rows from first table from ID column) and has ~259 000 rows.

If I try to try search from first table

SELECT * FROM testovoe_zadanie_poisk_knig.book
where id=66764

then one row returned.

If I try to try search from second table

SELECT * FROM testovoe_zadanie_poisk_knig.autors
where IDBook=66764

then 0 rows returned.

It's OK.

But. If I execute SQL script

SELECT count(b1.id) FROM testovoe_zadanie_poisk_knig.book b1
where b1.id not in (
select IDBook 
from `testovoe_zadanie_poisk_knig`.`autors`

)

then returned 0.

Why?

Upvotes: 1

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

This problem is usually cause by NULL values in autors.IDBook. You can fix it in two easy ways. The first is:

SELECT count(b1.id)
FROM testovoe_zadanie_poisk_knig.book b1
WHERE b1.id not in (select a.IDBook 
                    from `testovoe_zadanie_poisk_knig`.`autors` a
                    where a.IDBook is not NULL
                   );

The second is to use `NOT EXISTS:

SELECT count(b1.id)
FROM testovoe_zadanie_poisk_knig.book b1
WHERE NOT EXISTS (select a.IDBook 
                  from `testovoe_zadanie_poisk_knig`.`autors` a
                  where b1.id = a.IDBook
                 );

The semantics of NOT IN with NULL values is a bit counter-intuitive. If any of the values in the subquery are NULL, then the query says "I don't know if a given value is in the list.". So, it returns NULL, which is treated as false. This is ANSI standard behavior, and not specific to MySQL.

The NOT EXISTS form has the semantics you expect. If a value is NULL in the subquery, then it is ignored. For this reason, I (and others) usually recommend NOT IN for this type of comparison.

Upvotes: 1

Kaf
Kaf

Reputation: 33839

Does your IDBook column have null values?

Try this:

SELECT COUNT(b1.id) 
FROM testovoe_zadanie_poisk_knig.book b1
WHERE b1.id NOT IN (
  SELECT IDBook 
  FROM `testovoe_zadanie_poisk_knig`.`autors`
  WHERE IDBook IS NOT NULL 
)

Upvotes: 3

Related Questions