LEFT OUTER JOIN query not returning expected rows

Question

My aim is to do exactly what a LEFT OUTER JOIN intends to do using the 4th venn diagram: SQL Diagrams:

My query isn't returning any values at all, where in fact, it should be returning all within the Consultant_Memberships minus the one that is stored within Consultant_Memberships_Lists.

Please see the SQL Fiddle for an easier understanding:

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 

The query is using '5' as an ID for demonstration purposes to try and pick out the correct rows.

Battalion

Currently your question has almost no information, only links to information elsewhere. Those links are fine, but your question should also contain the necessary information. – Testimony

(Also, your phrasing -- "in fact, [my query] should be [...]" -- makes it sound like you think you've written the query correctly, and the DBMS is simply running it wrong. I assure you, that's not the case.) – Testimony

(I agree with ruakh. I changed "correct" to "expected" in the title, and encourage the use of neutral/non-accusatory language.) – Pumphrey

My apologies, I've added further information. – Battalion

Answer 1

You current query is basically doing an INNER JOIN because of the consultant_id = 5 on the WHERE clause. I believe you actually want to use:

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;

See SQL Fiddle with Demo

Shylashylock

@Bluefeet - It works perfectly and I've learnt more of the LEFT OUTER JOIN in the process. Thank you for your help, I appreciate it. – Battalion

+1 That predicate in the WHERE clause is negating the "outer"-ness of the LEFT JOIN. The easiest way for me to think about the OUTER join is this: if a matching row is not found, then a dummy row is generated so that there is a matching row to return. That generated dummy row is going to consist entirely of NULL values. So any "IS NOT NULL" predicate in the WHERE clause is going to exclude all those dummy rows that were generated. – Lonnalonnard

Answer 2

Use

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;

The Where clause used before in your query "consultant_memberships_list.consultant_id = 5 " was neglecting the left outer join.

Hi

It might be more helpful if you mentioned what change you made to the query (i.e. moving a predicate from a WHERE clause to an ON clause), and a mention of why that change is necessary. – Lonnalonnard