Find all children who are not parent

Question

I'm trying to retrieve all children who are not parent.

The table looks like this

ID  |  ParentID
---------------
1        NULL
2         1
3        NULL
4         2

At first I tried

SELECT *
FROM [SMD].[dbo].[ProposalFollowUp]
WHERE ID NOT IN (SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp])

but it returns no row. I wanted to select all rows that are not in parentID. I don't understand why it's not working.

Then I tried this

SELECT *
FROM [SMD].[dbo].[ProposalFollowUp] AS a
WHERE a.ID NOT IN 
(SELECT b.ID FROM [SMD].[dbo].[ProposalFollowUp] as b WHERE b.ParentID = a.ID)

but this returns all rows

Anyone can tell me what I'm missing

Thank you!

Answer 1

Using not in exposes a well-known SQL quirk:

WHERE ID NOT IN (SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp])

To understand why, exand the query:

WHERE ID NOT IN (null, 1, null, 2)

And that translates to:

where id <> null and id <> 1 and id <> null and id <> 2

The trick is that id <> null is never true. In SQL's three-valued logic, it evaluates to unknown. And that means your where clause never approves any row.

To solve this, use exists (like Tim Schmelter's answer), or exclude null from the subquery:

WHERE ID NOT IN (
    SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp] WHERE ParentID IS NOT NULL)