MySQL“NOT IN”查询不工作 [英] MySQL "NOT IN" query not working

问题描述

我有一个包含三个列的表： taxon_id ， scientific_name_element_id 和 parent_id 。我想找到的是孩子而不是父母的元素，所以结构的终点。

我发现一些来源建议我使用

  select taxon_id 
 from taxon_name_element 
其中taxon_id不在
（select parent_id from taxon_name_element）
  

但是这不行，我得到一个空集，当我可以实际浏览条目，并看到，例如，有一个 taxon_id = 1 和NO parent_id = 1

相反，当我在parent_id中看到taxon_id的时候，我得到一个非空的结果集

我做错了什么？ 在中有任何NULL taxon_name_element.parent_id / code>？

查询...

  select taxon_id 
 from taxon_name_element 
其中taxon_id不在（
 select parent_id 
 from taxon_name_element 
）
  

...等效于...

  select taxon_id 
 from taxon_name_element 
其中
 taxon_id<> parent_id_1 
 AND taxon_id<> parent_id_2 
 ... 
 AND taxon_id<> parent_id_N 
  

...其中 parent_id_X 列中的当前值 parent_id 。如果即使其中一个为NULL，则相应的 taxon_id<> parent_id_X 表达式将折叠为NULL，拖动整个WHERE表达式。

过滤掉NULL，得到你想要的： / p>

  select taxon_id 
 from taxon_name_element 
其中taxon_id不在（
 select parent_id 
 from taxon_name_element 
其中parent_id不为null 
）
  

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?

解决方案

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
)

这篇关于MySQL“NOT IN”查询不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！