LEFT OUTER JOIN查询没有返回预期的行(LEFT OUTER JOIN query no

我的目标是做的正是一个LEFT OUTER JOIN打算使用4维恩图做： SQL图表 ：

我的查询没有返回任何价值可言，在事实上，它应该在Consultant_Memberships减去存储Consultant_Memberships_Lists中的一个内返回所有。

请参阅SQL小提琴一个更容易理解：

SELECT * 
FROM   consultant_memberships 
       LEFT OUTER JOIN consultant_memberships_list 
                    ON consultant_memberships.`id` = 
                       consultant_memberships_list.membership_id 
WHERE  consultant_memberships_list.consultant_id = $id 
       AND consultant_memberships_list.membership_id IS NULL 

该查询使用“5”作为演示目的的ID，试图挑选出正确的行。

Answer 1:

您当前的查询，基本上是做一个INNER JOIN因为的consultant_id = 5的WHERE子句。 我相信你真的想使用：

SELECT * 
FROM   consultant_memberships m
LEFT OUTER JOIN consultant_memberships_list l
  ON m.`id` = l.membership_id 
  AND l.consultant_id = 5 
WHERE l.membership_id IS NULL;

请参阅SQL拨弄演示

Answer 2:

使用

SELECT * 
   FROM   consultant_memberships 
       LEFT  Outer JOIN consultant_memberships_list 
                ON consultant_memberships_list.membership_id = consultant_memberships.`id`  
                and  consultant_memberships_list.consultant_id = 5 
 where  consultant_memberships_list.membership_id IS NULL;

在where子句中的查询之前使用“consultant_memberships_list.consultant_id = 5”被忽略了左外连接。

文章来源: LEFT OUTER JOIN query not returning expected rows