Select Performance...

I have a table of products which contains a parent-child hierarchy (a self-relationship, creating a built-in recursive structure using a foreign key). The point is: I must create a query that shows only products which are not parents. I can achieve this using a "not in" or a "full join".

Can anybody tell me which of the 2 below is the best option, and why?




Code Snippet
SELECT ProductID
FROM CRP.Product
WHERE ProductID NOT IN
(SELECT ProductFatherID FROM CRP.Product WHERE ProductFatherID is not NULL)

Execution plan:


Select (cost 0%) <--- Merge join (25%) <--- Clustered index scan (54%)
^--- Stream aggregate (1%) <--- Index seek (19%)






Code SnippetSELECT
PC.ProductID
FROM -- Product Child
CRP.Product PC
FULL JOIN -- Product Parent
(SELECT ProductFatherID ProductID
FROM CRP.Product
WHERE ProductFatherID is not NULL) PP ON (PC.ProductID = PP.ProductID)
WHERE
PP.ProductID is NULL


Execution plan:

Select (cost 0%) <--- Filter (2%) <--- Merge join (29%) <--- Clustered index scan (51%)
^--- Compute scalar (0%) <--- index seek (18%)