How to determine which `Foos` have no associated `Bars`

Question

I want to find all Foos that do not have any associated Bars.

Here's the query I'm using. It's returning no records:

select * from foos f where f.id not in(select b.foo_id from bars b);

However, I know that the foo with id = 1583 has no associated Bars, so there should be at least one result in my previous query.

Can somebody point out a mistake I am making? Thanks.

Answer 1

That looks right; if it were me, I'd suspect typo. Have you examined the data?

 SELECT * from foos WHERE foos.id = 1583;
 SELECT * from bars WHERE bars.foo_id = 1583;

I assume you've recast your statement from your original to remove your actual table names -- so proofread your actual query.

Or try join syntax:

 SELECT * FROM foos LEFT JOIN bars on foos.id = bars.foo_id WHERE bars.foo_id IS NULL;

Answer 2

The reason is the SQL three valued logic.

Example: both statement will show you '?'.

SELECT CASE WHEN 2 NOT IN (3,4,NULL) THEN 'True' ELSE '?' END AS Test#0
SELECT CASE WHEN 2 <> 3 AND 2 <> 4 AND 2 <> NULL THEN 'True' ELSE '?' END AS Test#0
--                TRUE  AND  TRUE  AND UNKNOWN => UNKNOWN (ELSE -> ?)

The solution is to use NOT EXISTS instead of NOT IN:

DECLARE @foo TABLE(foo_id INT);
INSERT  @foo(foo_id)
SELECT 1 UNION ALL SELECT 2;

DECLARE @bars TABLE(bars_id INT IDENTITY(2,2), foo_id INT);
INSERT  @bars(foo_id)
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT NULL;

PRINT '@foo >'
SELECT * FROM @foo
PRINT '@bars >'
SELECT * FROM @bars

PRINT 'Test #1 >'
SELECT  *
FROM    @foo AS f
WHERE   f.foo_id NOT IN (SELECT b.foo_id FROM @bars AS b)

PRINT 'Test #2 >'
SELECT  *
FROM    @foo AS f
WHERE   NOT EXISTS (SELECT b.foo_id FROM @bars AS b WHERE b.foo_id = f.foo_id)

Results:

@foo >
foo_id
-----------
1
2

@bars >
bars_id     foo_id
----------- -----------
2           3
4           4
6           NULL

Test #1 >
foo_id
-----------

Test #2 >
foo_id
-----------
1
2