MySQL “NOT IN” query not working

问题:

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?

回答1:

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
)

回答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.