MySQL - Select from with not in statement

Question

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?

What happens if you remove the apices? testovoe_zadanie_poisk_knig.autors — Federico Ponzi
– Federico Ponzi, Commented Jul 14, 2014 at 14:02

Indi · Accepted Answer · 2014-07-14 14:03:48Z

3

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 
)

answered Jul 14, 2014 at 14:03

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-07-14 14:04:41Z

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.

Collectives™ on Stack Overflow

MySQL - Select from with not in statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related