INNER JOIN where **every** row must match the WHERE clause?

Question

Here's a simplified example of what I'm trying to do. I have two tables, A and B.

A          B
-----      -----
id         id
name       a_id
           value

I want to select only the rows from A where ALL the values of the rows from B match a where clause. Something like:

SELECT * from A INNER JOIN B on B.a_id = A.id WHERE B.value > 2

The problem with the above query is that if ANY row from B has a value > 2 I'll get the corresponding row from A, and I only want the row from A if

1.) ALL the rows in B for B.a_id = A.id match the WHERE, OR

2.) There are no rows in B that reference A

B is basically a table of filters.

Answer 1

SELECT  *
FROM    a
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    b
        WHERE   b.a_id = a.a_id
                AND (b.value <= 2 OR b.value IS NULL)
        )

Answer 2

This should solve your problem:

SELECT *
FROM   a
WHERE  NOT EXISTS (SELECT *
                   FROM   b
                   WHERE  b.a_id = a.id
                   AND    b.value <= 2)

Here is the way in which this is obtained.

Suppose that we have available a universal quantifier (parallel to EXISTS, the existential quantifier), with a syntax like:

FORALL table WHERE condition1 : condition2

(to be read: FORALL the elements of table that satisfy the condition1, then condition2 is true)

So you could write your query in this way:

SELECT *
FROM   a
WHERE  FORALL b WHERE b.a_id = a.id : b.value > 2

(Note that forall is true even when no element in b exists with a value of a.id)

Then we can transform the universal quantifier in the existential one, with a double negation, as usual:

SELECT *
FROM   a
WHERE  NOT EXISTS b WHERE b.a_id = a.id : NOT (b.value > 2)

In plain SQL this can be written as:

SELECT *
FROM a
WHERE NOT EXISTS (SELECT * 
                  FROM   b 
                  WHERE  b.a_id = a.id
                  AND    (b.value > 2) IS NOT TRUE)          

This technique is very handy in case of universal quantification.