SQL Selecting rows and excluding children

Question

I have two tables A and B. A is the parent of B. I'm trying to find all As which do not have a specific B as a child. Ordinarily I would do

SELECT A.id FROM A 
    WHERE A.id NOT IN 
        (SELECT B.AId FROM B WHERE B.someFK = foo);

However for performance reasons I'm keen not to use an inner select. I've tried something like:

SELECT A.id FROM A 
    LEFT JOIN B ON (A.id = B.AId)
    WHERE B.someFK != foo OR B.someFK IS NULL

The problem is this returns As which have more than one child regardless of if they have the specified B.

EDIT: Changed B.id to B.someFK

Answer 1

I'd go with the exists clause, since it was made for such a purpose:

SELECT A.id FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = foo and B.AId=a.id);

In most databases it's far more performant than the in clause (which basically is an array to compare against) for large record sets.

Aggregations are also expensive, so using an exists statement is the way to go imho. You might try aggregations for your scenario, though.

Answer 2

Your LEFT JOIN will return one row for each A-B link (or one row for an A with no link to B), and then removes the links that fail to match your criteria - leaving behind every other link that a particular A has in B, which is not what you want.

However, I think an inner select is necessary.
But maybe try an EXISTS instead:

SELECT A.id
FROM A 
WHERE NOT EXISTS (SELECT * FROM B WHERE B.AId = A.id AND B.someFK = foo);