mySQL 值不在数据中且在数据中 - 嗯？

Question

使用 MySQL。尝试理解一个查询，该查询查找不在其他查询结果中的值。似乎我应该返回 74 的值，因为该值不在其他查询的结果中，但相反，我返回了 0 行......

表名称 = phpbb3x_forums。
表中的某一行的 forum_id = 74。表格可在此处查看:http://launchpad-001.com/_scoreboard/_symposium_merge/stackhelp/db_viewer/sql.php?db=stackhelp&table=phpbb3x_forums#PMAURL-0:sql.php?db=stackhelp&table=phpbb3x_forums您还可以在该链接上运行这些查询。

Forum_id 74 不在以下 3 个查询的结果中:

select l0.forum_id
FROM step2_02_for.phpbb3x_forums l0
left join  phpbb3x_forums l1 on l0.forum_id = l1.parent_id
left join  phpbb3x_forums l2 on l1.forum_id = l2.parent_id
left join  phpbb3x_forums l3 on l2.forum_id = l3.parent_id
where l0.parent_id = 0

select l1.forum_id
FROM step2_02_for.phpbb3x_forums l0
left join  phpbb3x_forums l1 on l0.forum_id = l1.parent_id
left join  phpbb3x_forums l2 on l1.forum_id = l2.parent_id
left join  phpbb3x_forums l3 on l2.forum_id = l3.parent_id
where l0.parent_id = 0

select l2.forum_id
FROM step2_02_for.phpbb3x_forums l0
left join  phpbb3x_forums l1 on l0.forum_id = l1.parent_id
left join  phpbb3x_forums l2 on l1.forum_id = l2.parent_id
left join  phpbb3x_forums l3 on l2.forum_id = l3.parent_id

where l0.parent_id = 0

因此，我希望在运行以下命令时返回forum_id=74

select forum_id from step2_02_for.phpbb3x_forums 
where forum_id not in 

(select l0.forum_id
FROM step2_02_for.phpbb3x_forums l0
left join  step2_02_for.phpbb3x_forums l1 on l0.forum_id = l1.parent_id
left join  step2_02_for.phpbb3x_forums l2 on l1.forum_id = l2.parent_id
left join  step2_02_for.phpbb3x_forums l3 on l2.forum_id = l3.parent_id
where l0.parent_id = 0 )

and forum_id not in 
(select l1.forum_id
FROM step2_02_for.phpbb3x_forums l0
left join  step2_02_for.phpbb3x_forums l1 on l0.forum_id = l1.parent_id
left join  step2_02_for.phpbb3x_forums l2 on l1.forum_id = l2.parent_id
left join  step2_02_for.phpbb3x_forums l3 on l2.forum_id = l3.parent_id
where l0.parent_id = 0)


and forum_id not in 
(select l2.forum_id
FROM step2_02_for.phpbb3x_forums l0
left join  step2_02_for.phpbb3x_forums l1 on l0.forum_id = l1.parent_id
left join  step2_02_for.phpbb3x_forums l2 on l1.forum_id = l2.parent_id
left join  step2_02_for.phpbb3x_forums l3 on l2.forum_id = l3.parent_id
where l0.parent_id = 0)

相反，我没有收到任何返回的行。

运行以下命令时，它按预期工作:

select forum_id from step2_02_for.phpbb3x_forums 
where forum_id not in 

(select l0.forum_id
FROM step2_02_for.phpbb3x_forums l0
left join  step2_02_for.phpbb3x_forums l1 on l0.forum_id = l1.parent_id
left join  step2_02_for.phpbb3x_forums l2 on l1.forum_id = l2.parent_id
left join  step2_02_for.phpbb3x_forums l3 on l2.forum_id = l3.parent_id
where l0.parent_id = 0 )

and forum_id not in 
(select l1.forum_id
FROM step2_02_for.phpbb3x_forums l0
left join  step2_02_for.phpbb3x_forums l1 on l0.forum_id = l1.parent_id
left join  step2_02_for.phpbb3x_forums l2 on l1.forum_id = l2.parent_id
left join  step2_02_for.phpbb3x_forums l3 on l2.forum_id = l3.parent_id
where l0.parent_id = 0)

返回的值包括 74 和我期望的所有其他值。

对此的任何帮助将不胜感激。

Answer 1

我想象您面临的问题not in是子查询返回 NULL值(value)观。最简单的解决方法是输入 where <col> is not null以防止这种情况发生。

考虑简单的条件where col in (A, B, C, NULL) 。如果col承担A , B ，或C那么查询返回true。否则返回unknown，被视为false。

现在，将条件更改为 not in :where col not in (A, B, C, NULL) 。有两件事可能发生。当col是 A , B ，或C ，not in返回假。对于任何其他值，它返回未知，被视为 false。换句话说，如果有 NULL列表中的值，然后 NOT IN永远不会返回 true。

“真正的”解决方案是停止使用 NOT IN并使用 NOT EXISTS反而。这是转变。当你写:

 where y.col not in (select col2 from table x)

你真正的意思是:

 where not exists (select 1 from table x where x.col2 = y.col)