title: Will this left join on same table ever return data?
body: <p>In SQL Server, on a re-engineering project, I'm walking through some old sprocs, and I've come across this bit. I've hopefully captured the essence in this example:</p>
  
  <h2>Example Table</h2>
  
  <pre><code>SELECT * FROM People
  
  Id | Name
  -------------------------
  1  | Bob Slydell
  2  | Jim Halpert
  3  | Pamela Landy
  4  | Bob Wiley
  5  | Jim Hawkins
  </code></pre>
  
  <h2>Example Query</h2>
  
  <pre><code>SELECT a.*
  FROM (
      SELECT DISTINCT Id, Name
      FROM People
      WHERE Id &gt; 3
  ) a
  LEFT JOIN People b
  ON a.Name = b.Name
  WHERE b.Name IS NULL
  </code></pre>
  
  <p>Please disregard formatting, style, and query efficiency issues here. This example is merely an attempt to capture the exact essence of the real query I'm working with.</p>
  
  <p>After looking over the real, more complex version of the query, I burned it down to this above, and I cannot for the life of me see how it would ever return any data. The <code>LEFT JOIN</code> should always exclude everything that was just selected because of the <code>b.Name IS NULL</code> check, right? (and it being the same table). If a row from People was found where b.Name IS NULL evals to true, then shouldn't that mean that data found in <code>People a</code> was never found? (impossible?)</p>
  
  <p>Just to be very clear, I'm not looking for a "solution". The code is what it is. I'm merely trying to understand its behavior for the purpose of re-engineering it.</p>
  
  <p>If this code indeed never returns results, then I'll conclude it was written incorrectly and use that knowledge during the re-engineering.</p>
  
  <p>If there is a valid data scenario where it would/could return results, then that will be news to me and I'll have to go back to the books on SQL Joins! #DrivenCrazy</p>