Check for non-match in join table

Question

I have two queries who's results should be mutually exclusive, but they are not.

This one properly finds all accounts that have an email with a matching name and order_id:

SELECT * FROM `accounts` 
LEFT OUTER JOIN `emails` ON emails.account_id = accounts.id 
WHERE (emails.type_name = 'name' AND emails.order_id = 1)

This one should should find all accounts that don't have an email with a matching name and order_id, or don't have an email at all:

SELECT * FROM `accounts` 
LEFT OUTER JOIN `emails` ON emails.account_id = accounts.id 
WHERE (!(emails.type_name = 'name' AND emails.order_id = 1) OR emails.id IS NULL)

However, this latter query is returning accounts that have a matching email if they also have a non-matching email, thus it's returning accounts from the first query. Any help would be greatly appreciated.

Answer 1

Consider the following test case:

CREATE TABLE accounts (id int);
CREATE TABLE emails (id int, account_id int, type_name varchar(10), order_id int);

INSERT INTO accounts VALUES (1), (2), (3), (4);

INSERT INTO emails VALUES (1, 1, 'name', 1);
INSERT INTO emails VALUES (2, 1, 'no-name', 1);
INSERT INTO emails VALUES (3, 2, 'name', 1);
INSERT INTO emails VALUES (4, 2, 'no-name', 1);
INSERT INTO emails VALUES (5, 3, 'name', 2);

Then this works as expected:

SELECT * FROM `accounts` 
LEFT OUTER JOIN `emails` ON emails.account_id = accounts.id 
WHERE (emails.type_name = 'name' AND emails.order_id = 1);

+------+------+------------+-----------+----------+
| id   | id   | account_id | type_name | order_id |
+------+------+------------+-----------+----------+
|    1 |    1 |          1 | name      |        1 |
|    2 |    3 |          2 | name      |        1 |
+------+------+------------+-----------+----------+
2 rows in set (0.00 sec)

The problem with your second query is that it can return a NULL row if there is an account with no email, as is the case of account number 4:

SELECT * FROM `accounts` 
LEFT OUTER JOIN `emails` ON emails.account_id = accounts.id 
WHERE (!(emails.type_name = 'name' AND emails.order_id = 1) OR emails.id IS NULL);

+------+------+------------+-----------+----------+
| id   | id   | account_id | type_name | order_id |
+------+------+------------+-----------+----------+
|    1 |    2 |          1 | no-name   |        1 |
|    2 |    4 |          2 | no-name   |        1 |
|    3 |    5 |          3 | name      |        2 |
|    4 | NULL |       NULL | NULL      |     NULL |
+------+------+------------+-----------+----------+
4 rows in set (0.01 sec)

Why wouldn't this be enough for a mutually exclusive result set with no NULL rows?:

SELECT * FROM `accounts` 
LEFT OUTER JOIN `emails` ON emails.account_id = accounts.id 
WHERE NOT (emails.type_name = 'name' AND emails.order_id = 1)

+------+------+------------+-----------+----------+
| id   | id   | account_id | type_name | order_id |
+------+------+------------+-----------+----------+
|    1 |    2 |          1 | no-name   |        1 |
|    2 |    4 |          2 | no-name   |        1 |
|    3 |    5 |          3 | name      |        2 |
+------+------+------------+-----------+----------+
3 rows in set (0.00 sec)

Answer 2

If your target is to match a pair of (type_name,order_id) then it should work -

SELECT * FROM `accounts` 
LEFT OUTER JOIN `emails` ON emails.account_id = accounts.id 
WHERE (emails.type_name != 'name' OR emails.order_id != 1)