mysql – LEFT OUTER JOIN查询不返回预期的行

我的目标是使用第4个维恩图来完成LEFT OUTER JOIN打算做的事情：SQL Diagrams：

我的查询根本没有返回任何值,实际上,它应该返回Consultant_Memberships中的所有值减去Consultant_Memberships_Lists中存储的值.

请参阅SQL Fiddle以便更容易理解：

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用于演示目的,以尝试挑选正确的行.
最佳答案
由于WHERE子句上的consultant_id = 5,您当前的查询基本上是在进行INNER JOIN.我相信你真的想用：

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 Fiddle with Demo