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?

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
– SimaPro, Commented Feb 18, 2012 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
– ruakh, Commented Feb 18, 2012 at 18:53

Branko Dimitrijevic · Accepted Answer · 2012-02-18 18:48:32Z

11

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
)

answered Feb 18, 2012 at 18:48

Branko Dimitrijevic

52.3k12 gold badges103 silver badges174 bronze badges

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

Comments

Mohamed Ibrahim · Accepted Answer · 2012-02-18 18:22:01Z

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, 2012 at 18:22

Mohamed Ibrahim

1904 bronze badges

1 Comment

ruakh Over a year ago

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.)

Collectives™ on Stack Overflow

MySQL "NOT IN" query not working

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related