MySQL "NOT IN" query not working

Question

I have a table with three columns: taxon_id, scientific_name_element_id, and parent_id. I want to find the elements that are children and not parents, so the termini of the structure.

I found some sources that suggested that I use

select taxon_id 
       from  taxon_name_element
       where taxon_id not in 
                          (select parent_id from taxon_name_element)

But this does not work, I get an empty set when I can actually browse the entries and see that there is, for example, a taxon_id=1, and NO parent_id=1

Conversely when I see what taxon_id's are in parent_id's I get a nonempty result set

What am I doing wrong? How can I fix this?

possible duplicate of [SQL NOT IN constraint and NULL values](http://stackoverflow.com/questions/129077/sql-not-in-constraint-and-null-values) — ruakh, Feb 18 '12 at 18:20
thanks ruakh, I added an is not null requirement and it worked. Am I understanding this correctly?: if taxon_id is compared to NULL, it returns unknown and unknown added to any number will yield unknown so the result is no rows? Thanks for the linkk — SimaPro, Feb 18 '12 at 18:50
Yes, exactly. Something like `3 <> NULL` is indeterminate, because the `NULL` might be a three; so, too, is `3 NOT IN (1, 2, NULL)`. MySQL will only return rows that are *known* to satisfy the `WHERE` clause. — ruakh, Feb 18 '12 at 18:53

score 11 · Accepted Answer · answered Feb 18 '12 at 18:48

Are there any NULLs in taxon_name_element.parent_id?

The query...

select taxon_id 
from taxon_name_element
where taxon_id not in (
    select parent_id
    from taxon_name_element
)

...is equivalent to...

select taxon_id 
from taxon_name_element
where
    taxon_id <> parent_id_1
    AND taxon_id <> parent_id_2
    ...
    AND taxon_id <> parent_id_N

...where parent_id_X are actual values that are currently in the parent_id column. If even one of them is NULL, the corresponding taxon_id <> parent_id_X expressions will "collapse" into NULL, dragging the whole WHERE expression with it.

Filter-out NULLs to get what you want:

select taxon_id 
from taxon_name_element
where taxon_id not in (
    select parent_id
    from taxon_name_element
    where parent_id is not null
)

score 2 · Answer 2 · answered Feb 18 '12 at 18:22

2

Assuming the parent_id column is NULL (meaning no value is set)

To select all scientific_name_element_id that have no value for parent_id (meaning parent_id is NULL)

You do this:

SELECT scientific_name_element_id
FROM YOUR_TABLE
WHERE parent_id IS NULL

This will get you a list of scientific_name_element_id that have no parents.

answered Feb 18 '12 at 18:22

Mohamed Ibrahim

190
4

I think you must have misunderstood the question. Your query will find records that have no *parent*. The OP wants to find records that have no *children*. (By the way, the name of the table is `taxon_name_element`.) – ruakh Feb 18 '12 at 18:34

MySQL "NOT IN" query not working

2 Answers2

Linked