<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>

No clue why your query didn't work.  Here's the identical thing in left outer join syntax - try it this way?
```
```
select a.*
from mytree a left outer join
     mytree b on a.id = b.parentid
where b.parentid is null
```
```