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.

Can you edit your question and provide sample data and desired results? — Gordon Linoff, Jun 30 '15 at 20:13
Your definition is **contradictory**. If you have more than one distinct value in `a.id` it is **impossible** for `ALL the rows in B match the WHERE`, because a row in `B` cannot have more than one value at a time. Please clarify. It would seem you mean: *Return all rows from `A`, where all rows in `B` **that match on `B.a_id = A.id`** also fulfill the second condition `B.value > 2`.* — Erwin Brandstetter, Jul 01 '15 at 18:28
You're right, @ErwinBrandstetter, I had the constraint that B.a_id = A.id in mind. Edited to clarify. — jhickner, Jul 01 '15 at 22:00

score 8 · Accepted Answer · answered Jun 30 '15 at 20:11

8

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)
        )

answered Jun 30 '15 at 20:11

Quassnoi

413,100
91
616
614

1

You can also write the query like this if you chose to use `EXISTS` logic over join `SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.a_idAND (IFNULL(b.value, 0) <= 2)` – Jaylen Jun 30 '15 at 20:18

Renzo · Answer 2 · 2015-07-01T19:02:49.513

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.

Only NULL is not considered. – Erwin Brandstetter Jul 01 '15 at 18:49 — Erwin Brandstetter, Jul 01 '15 at 18:49

score 2 · Answer 3 · edited May 23 '17 at 11:47

Answering this question (which it seems you actually meant to ask):

Return all rows from A, where all rows in B with B.a_id = A.id also pass the test B.value > 2.

Which is equivalent to:

Return all rows from A, where no row in B with B.a_id = A.id fails the test B.value > 2.

SELECT a.*  --  "rows from A" (so don't include other columns)
FROM   a
LEFT   JOIN b ON b.a_id = a.id
             AND (b.value > 2) IS NOT TRUE -- safe inversion of logic
WHERE  b.a_id IS NULL;

When inverting a WHERE condition carefully consider NULL. IS NOT TRUE is the simple and safe way to perfectly invert a WHERE condition. The alternative would be (b.value <= 2 OR b.value IS NULL) which is longer but may be faster (easier to support with index).

Select rows which are not present in other table

score 1 · Answer 4 · answered Jun 30 '15 at 20:13

1

Try this

SELECT * FROM A 
LEFT JOIN B ON B.a_id = A.id
WHERE B.value > 2 OR B.a_id IS NULL

answered Jun 30 '15 at 20:13

Jaylen

39,043
40
128
221

This will return a row if there is but a single entry in `B` which matches the filter for `a_id`, regardless of other entries which don't – Quassnoi Jun 30 '15 at 20:20
This query should return all the records in A that do not exists in B and have value > 2 which is what the OP is asking for – Jaylen Jun 30 '15 at 20:23
the op is asking that **all** the rows in B match the `WHERE`. Your query will return if **any** row in `B` matches the `WHERE`, or if there are no rows in `B` for this `a_id`. – Quassnoi Jun 30 '15 at 20:26
I am not sure if I misunderstood the 2 conditions "1.) All the rows in B match the WHERE, OR 2)There are no rows in B that reference A" – Jaylen Jun 30 '15 at 20:27
say there are two entries in `b (a_id, value)`: (1, 1), (1, 3). Op does not want `a_id = 1` to return. – Quassnoi Jun 30 '15 at 20:29
1

`2)There are no rows in B that reference A` tell me that he want all the row in B if there is no match in B + anything that have a match in B but have a value > 2 if this is not that the OP is requesting I suggest that he put a jFiddle together with dummy data so we know what to recommend – Jaylen Jun 30 '15 at 20:35
I only want to select the row from A if **ALL** the rows from B that reference A have a value > 2. **OR** if there are no rows from B that reference A. – jhickner Jun 30 '15 at 20:36
@jhickner my query should do just that. Please give it a shot a let me know the result. you should put example data and your expected results in your question – Jaylen Jun 30 '15 at 20:39
Not it doesn't. you return all columns from A and B, possibly multiple combinations (multiplying A), irregardless of whether there are offending rows in B for the same a_id. This couldn't me more wrong. – Erwin Brandstetter Jul 01 '15 at 18:53

KAD · Answer 5 · 2015-07-01T18:58:33.783

1

SELECT * FROM A LEFT JOIN B ON b.a_id = a.id
WHERE B.a_id IS NULL OR NOT EXIST (
        SELECT  1
        FROM    b
        WHERE  b.value <= 2)

edited Jul 01 '15 at 18:58

answered Jun 30 '15 at 20:21

KAD

10,972
4
31
73

This is invalid syntax (no join condition). – Erwin Brandstetter Jul 01 '15 at 18:50
An now it's invalid logic :) Any single row with `b.value = 1` would remove all rows from A with *any* match in B on `b.a_id = a.id`. – Erwin Brandstetter Jul 01 '15 at 19:03
1.) ALL the rows in B match the WHERE, OR (in this case if b.value = 1 then not all the rows in B will match the where) so it will try to make the second case true 2.) There are no rows in B that reference A,..... Where there is no row referencing A meaning B.a_id is null. That's what the requirements are in the question!! – KAD Jul 01 '15 at 19:16

score 0 · Answer 6 · answered Jun 30 '15 at 20:24

SELECT a.is, a.name, c.id as B_id, c.value from A 
INNER JOIN (Select b.id, b.a_id, b.value from B WHERE B.value > 2) C
on C.a_id = A.id

Note it is a poor practice to use select *. You shoudl only specify fields you need. IN this case, I might possibly remove the b.Id refernces becasue they are probably not needed. If you have a join there is a 100% chance you are wasting resouces sending data you don't need becasue the join fields will be repeated. That is why I did nto include a_id in the final result set.

score 0 · Answer 7 · answered Jun 30 '15 at 21:52

0

If you prefer not to use EXISTS, you can use an outer join.

SELECT A.* 
FROM 
    A 
    LEFT JOIN B ON 
        B.a_id = A.id 
        AND B.value <= 2 -- note: condition reversed!!
WHERE B.id IS NULL

This works by searching for the existence of a failing record in B. If it finds one, then the join will match, and the final WHERE clause will exclude that record.

answered Jun 30 '15 at 21:52

recursive

83,943
34
151
241

Only NULL is not considered. – Erwin Brandstetter Jul 01 '15 at 18:49

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

7 Answers7

INNER JOIN where every row must match the WHERE clause?