MySQL“NOT IN”查询不起作用

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.

我有一个包含三列的表:taxon_id,scientific_name_element_id和parent_id。我想找到儿童而不是父母的元素,所以结构的终点。

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

但这不起作用,当我可以实际浏览条目并看到有例如taxon_id = 1和NO parent_id = 1时,我得到一个空集

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

相反,当我看到parent_id中的taxon_id是什么时,我得到一个非空的结果集

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

我究竟做错了什么?我怎样才能解决这个问题?

2 个解决方案

#1

Are there any NULLs in taxon_name_element.parent_id?

taxon_name_element.parent_id中是否有NULL?

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.

...其中parent_id_X是当前在parent_id列中的实际值。如果其中一个为NULL,则相应的taxon_id <> parent_id_X表达式将“折叠”为NULL,并使用它拖动整个WHERE表达式。

Filter-out NULLs to get what you want:

过滤掉NULL以获得您想要的内容:

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)

假设parent_id列为NULL(表示没有设置值)

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

选择所有没有parent_id值的scientific_name_element_id(意味着parent_id为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.

这将为您提供没有父母的scientific_name_element_id列表。

#1