title: Can someone explain why this left outer join behaves this way?
body: <p>I have two tables, <code>item</code> and <code>item_popularity</code> and need to find, for a handful of items provided as input, those that have no popularity. I figured a <code>LEFT OUTER JOIN</code> with a <code>NULL</code> check afterwards would be the right way, but I've been gettin some really weird behavior. I then modified the query a bit and got it to work but I'd really like to understand why it was behaving that way.</p>
  
  <p>Let's assume those tables have these values:</p>
  
  <pre><code> item
  +----+----------+
  | id | name     |
  +----+----------+
  | 1  | Item 1   |
  | 2  | Item 2   |
  | 3  | Item 3   |
  | 4  | Item 4   |
  | 5  | Item 5   |
  +----+----------+
  
  item_popularity
  +---------+-------------+----------+
  | id_item | popularity  | id_store |
  +---------+-------------+----------+
  | 1       | 0.78        | 1        |
  | 3       | 0.23        | 1        |
  | 4       | 0.6765      | 1        |
  +----+------------------+----------+
  </code></pre>
  
  <p>My original query was as follows:</p>
  
  <pre><code>SELECT item.id, item.name, item_popularity.popularity FROM item
  LEFT OUTER JOIN item_popularity
  ON item.id = item_popularity.id_item
  WHERE item.id IN (provided input)
  AND id_store = (provided store)
  AND item_popularity.id_item IS NULL
  </code></pre>
  
  <p>However that would return no items, because it was only joining with items that HAD popularity, as can be seen <a href="http://sqlfiddle.com/#!2/25339f/1" rel="nofollow">on this fiddle</a>. I googled a bit and found lots of people with the same problem, and everywhere people were saying to move that first bit of the <code>WHERE</code> clause to the <code>JOIN</code>, because the way it was it would cause the <code>LEFT JOIN</code> to become an <code>INNER JOIN</code> or something like that.</p>
  
  <p>So I did that, and then the query became:</p>
  
  <pre><code>SELECT item.id, item.name, item_popularity.popularity FROM item
  LEFT OUTER JOIN item_popularity
  ON item.id = item_popularity.id_item
  AND item.id IN (provided input)
  AND id_store = (provided store)
  WHERE item_popularity.id_item IS NULL
  </code></pre>
  
  <p>Now to the weird bit. Let's say I was looking for items 2, 3 and 5. I know that only 3 has a popularity score, so I should see 2 and 5 as the result, right? Well, sort of. I see 2 and 5 there, but it also lists 1 and 4 in the results! <a href="http://sqlfiddle.com/#!2/25339f/2" rel="nofollow">Here's the fiddle</a>.</p>
  
  <p>Ok, so the only way I managed to get the correct results were by repeating the id filter in the <code>WHERE</code> clause, making the query this ugly looking thing, as seen <a href="http://sqlfiddle.com/#!2/25339f/3" rel="nofollow">here</a>:</p>
  
  <pre><code>SELECT item.id, item.name, item_popularity.popularity FROM item
  LEFT OUTER JOIN item_popularity
  ON item.id = item_popularity.id_item
  AND item.id IN (provided input)
  AND id_store = (provided store)
  WHERE item.id IN (provided input)
  AND item_popularity.id_item IS NULL
  </code></pre>
  
  <p>I don't know if I should repeat the <code>id_store</code> filter in the <code>WHERE</code>, I might just for the sake of certainty. Can anyone explain why this happens and is there a better way to fix it?</p>