CODEWITHSUNDEEP
mysqlnot-existsnotin
radleybobins
radleybobins

Reputation: 981

Why aren't "NOT IN" and "NOT EXISTS" working?

I've tried to accomplish this in a few different ways, and I'm coming up short. I'm trying to figure out the IDs of clients who have not scheduled an appointment this year.

My first query takes forever, and times out most of the time:

SELECT c.clientId
FROM clients c
WHERE NOT EXISTS (
   SELECT a.appointmentId FROM appointments a WHERE a.date >= "2020-02-15" AND c.clientId = a.clientId)

My second query is super fast, but that's likely because it's not taking the NOT IN into account. The results show the same thing as if I run the query without the NOT EXISTS. I think this means I'm not properly matching up a.clientId = c.clientId. But, I can't seem to figure out how to make that work.

SELECT c.clientId
FROM clients c
WHERE c.clientId NOT IN (
    SELECT a.appointmentId FROM appointments a WHERE a.date >= "2020-02-15")

I also tried it this third way with no luck, it returns an empty set:

SELECT c.clientId
FROM clients c
LEFT JOIN appointments a
ON a.clientId = c.clientId
WHERE a.appointmentId IS NULL
AND a.date >= '2020-02-15'

Thanks in advance!

Upvotes: 0

Views: 30

Answers (1)

Nick
Nick

Reputation: 147196

The problem with your second query is that you're comparing clientId in client with appointmentId in appointments, so it's unlikely to do any filtering. It should be written as

SELECT clientId
FROM clients
WHERE clientId NOT IN (
    SELECT clientId FROM appointments WHERE date >= "2020-02-15")

The issue with your third query is that the a.date >= '2020-02-15' condition in the WHERE clause effectively turns your LEFT JOIN into an INNER JOIN (see the manual), thus a.appointmentId IS NULL is never true. You need to add the date comparison to the JOIN condition:

SELECT c.clientId
FROM clients c
LEFT JOIN appointments a
ON a.clientId = c.clientId AND a.date >= '2020-02-15'
WHERE a.appointmentId IS NULL

Note that if all you want is the clientId value, you don't need to use the clients table at all:

SELECT clientId
FROM appointments
GROUP BY clientId
HAVING MAX(date) < '2020-02-15'

Demo on dbfiddle

Upvotes: 1

Related Questions