title: Find leaf nodes in hierarchical tree
body: <p>I have a table in my database which stores a tree structure. Here are the relevant fields:</p>
  
  <pre><code>mytree (id, parentid, otherfields...)
  </code></pre>
  
  <p>I want to find all the leaf nodes (that is, any record whose <code>id</code> is not another record's <code>parentid</code>)</p>
  
  <p>I've tried this:</p>
  
  <pre><code>SELECT * FROM mytree WHERE `id` NOT IN (SELECT DISTINCT `parentid` FROM `mytree`)
  </code></pre>
  
  <p>But that returned an empty set. Strangely, removing the "NOT" returns the set of all the non-leaf nodes.</p>
  
  <p>Can anyone see where I'm going wrong?</p>
  
  <p><em>Update:</em> Thanks for the answers folks, they all have been correct and worked for me. I've accepted Daniel's since it also explains why my query didn't work (the NULL thing).</p>