SQL statement for join but not in other table

Question

I have two tables: customer and mailing :

+==========+  +=============+
| customer |  | mailing     |
+==========+  +=============+
| id       |  | id          |
+----------+  +-------------+
| name     |  | customer_id |
+----------+  +-------------+
              | mailing_id  |
              +-------------+

Every time I send a mailing to a customer, I add a row in the mailings table with that mailing id. One mailing can be sent to multiple customers.

I want to have a sql call that returns all customers that have not yet received a certain mailing. How to ?

I am using mysql

score 5 · Accepted Answer · answered Jul 18 '13 at 12:51

5

select * from customer where id not in (
    select customer_id from mailing where mailing_id = @mailing_id
)

answered Jul 18 '13 at 12:51

Polly Shaw

2,932
1
15
21

if it is MySQL, `IN ()` does not work properly when containing a subquery and can run for hours even on very small tables – Daniel W. Jul 18 '13 at 12:53
@DanFromGermany Really? According to this link it compares quite well to `NOT EXISTS` http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ – joe776 Jul 18 '13 at 12:59
Why do you have the condition in the subquery? It's not needed, is it? – joe776 Jul 18 '13 at 13:01
@joe776 that post is from 2009, compare with this question: http://stackoverflow.com/questions/6135376/mysql-select-where-field-in-subquery-extremely-slow-why "_Never use IN with a subquery; this is notoriously slow. Only ever use IN with a fixed list of values._" – Daniel W. Jul 18 '13 at 13:04
@DanFromGermany IMHO, evaluating a query plan beats "I never use X because it's notoriously slow". There is no evidence in the answer as to why it should be like that. Of course, it always depends on the actual data inside your DB, indexes, etc... – joe776 Jul 18 '13 at 13:12

Daniel W. · Answer 2 · 2013-07-18T13:30:22.510

2

SELECT * FROM customers c
JOIN mailings m
ON c.id = m.id
WHERE NOT EXISTS (
    SELECT id
    FROM mailings i
    WHERE i.id = c.id
    GROUP BY i.id
)

edited Jul 18 '13 at 13:30

answered Jul 18 '13 at 12:53

Daniel W.

31,164
13
93
151

I'm not sure if this is 100% correct now, if OP needs any further help, pls setup an sqlfiddle – Daniel W. Jul 18 '13 at 13:11
The join between `customer` and `mailing` requires that there already is a mailing for the customer. Furthermore, the subquery always returns a result if there is any mailing for a customer, so if there is any mailing at all you won't get results. – joe776 Jul 18 '13 at 13:18
right, updated :) I'm still not sure about the `IN ()`, we had a lot of problems with subqueries in `IN ()` and I often read that they don't work, even with percona or Mariadb – Daniel W. Jul 18 '13 at 13:31

score 2 · Answer 3 · answered Jul 18 '13 at 12:55

2

Something like

Select c.ID, c.Name
From Customers C
left Join mailing m On c.id = m.customer_id
where m.customer_id is null

answered Jul 18 '13 at 12:55

Tony Hopkinson

20,172
3
31
39

And this one is the fastest one, unless you have to use distinct! – makciook Jul 18 '13 at 13:07

joe776 · Answer 4 · 2013-07-18T13:26:28.983

What you describe is called an ANTI JOIN. Usually there are three different ways for formulating it in SQL: A NOT IN condition with a subquery, a NOT EXISTS condition with a correlated subquery, or a LEFT JOIN with a NOT NULL condition. So for your query the possibilities are:

SELECT *
FROM customer
WHERE id NOT IN
    ( SELECT customer_id
     FROM mailing)

SELECT *
FROM customer c
WHERE NOT EXISTS
    ( SELECT customer_id
     FROM mailing m
     WHERE m.customer_id = c.id)

SELECT *
FROM customer c
LEFT JOIN mailing m ON c.id = m.customer_id
WHERE m.customer_id IS NULL

This blog post compares the different possibilities with MySQL 5.1. According to it, LEFT JOIN / IS NULL and NOT IN are faster than NOT EXISTS.
However, you should try for yourself which one is the fastest. That always depends on your data, indexes, ...

SQL statement for join but not in other table

4 Answers4