title: self join query
body: <p>Consider the following table:</p>
  
  <pre><code>mysql&gt; select * from phone_numbers;
  +-------------+------+-----------+
  | number      | type | person_id |
  +-------------+------+-----------+
  | 17182225465 | home |         1 |
  | 19172225465 | cell |         1 |
  | 12129876543 | home |         2 |
  | 13049876543 | cell |         2 |
  | 15064223454 | home |         3 |
  | 15064223454 | cell |         3 |
  | 18724356798 | home |         4 |
  | 19174335465 | cell |         5 |
  +-------------+------+-----------+
  </code></pre>
  
  <p>I'm trying to find those people who have home phones but not cells.  </p>
  
  <p>This query works:</p>
  
  <pre><code>mysql&gt; select h.*
      -&gt; from phone_numbers h
      -&gt; left join phone_numbers c
      -&gt; on h.person_id = c.person_id
      -&gt; and c.type = 'cell'
      -&gt; where h.type = 'home'
      -&gt; and c.number is null;
  +-------------+------+-----------+
  | number      | type | person_id |
  +-------------+------+-----------+
  | 18724356798 | home |         4 |
  +-------------+------+-----------+
  </code></pre>
  
  <p>but this one doesn't:</p>
  
  <pre><code>mysql&gt; select h.*
      -&gt; from phone_numbers h
      -&gt; left join phone_numbers c
      -&gt; on h.person_id = c.person_id
      -&gt; and h.type = 'home'
      -&gt; and c.type = 'cell'
      -&gt; where c.number is null;
  +-------------+------+-----------+
  | number      | type | person_id |
  +-------------+------+-----------+
  | 19172225465 | cell |         1 |
  | 13049876543 | cell |         2 |
  | 15064223454 | cell |         3 |
  | 18724356798 | home |         4 |
  | 19174335465 | cell |         5 |
  +-------------+------+-----------+
  </code></pre>
  
  <p>The only difference between the two is the location of the <code>h.type = 'home'</code> condition - in the first it's in the <code>where</code> clause and in the second it's part of the <code>on</code> clause.</p>
  
  <p>Why doesn't the second query return the same result as the first?</p>