Is there alternative way to write this query?

Question

I have tables A, B, C, where A represents items which can have zero or more sub-items stored in C. B table only has 2 foreign keys to connect A and C.

I have this sql query:

select * from A
where not exists (select * from B natural join C where B.id = A.id and C.value > 10);

Which says: "Give me every item from table A where all sub-items have value less than 10.

Is there a way to optimize this? And is there a way to write this not using exists operator?

Answer 1

There are three commonly used ways to test if a value is in one table but not another:

• NOT EXISTS
• NOT IN
• LEFT JOIN ... WHERE ... IS NULL

You have already shown code for the first. Here is the second:

SELECT *
FROM A
WHERE id NOT IN (
    SELECT b.id
    FROM B
    NATURAL JOIN C
    WHERE C.value > 10
)

And with a left join:

SELECT *
FROM A
LEFT JOIN (
    SELECT b.id
    FROM B
    NATURAL JOIN C
    WHERE C.value > 10
) BC
ON A.id = BC.id
WHERE BC.id IS NULL

Depending on the database type and version, the three different methods can result in different query plans with different performance characteristics.