CODEWITHSUNDEEP
mysqlsql
J Doe
J Doe

Reputation: 113

How to use "not in" in SQL

Try to figure out how to use "not in" in SQL.

I'm needing to find out the callers who have never made a call. Im doing this by linking the Caller_id from the Caller table to the Caller_id in the Issue table. I then tried to use "not in" to see if any of the Caller_id from Caller table have not appeared in the Issue table meaning they've never made a call.

https://i.sstatic.net/Rdc47.jpg - How the tables link together

Select first_name, last_name
From Caller
JOIN Issue ON Caller.Caller_id = Issue.Caller_id 
WHERE Caller.Caller_id = Issue.Caller_id 
AND Caller.Caller_id not in Issue.Caller_id

My code doesnt return an output as its wrong but it should just display the first_name and last_name of the callers with 0 calls.

Upvotes: 1

Views: 88

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Don't use NOT IN with a subquery. It doesn't do the right thing if any of the values returned by the subquery are NULL.

Use either NOT EXISTS or LEFT JOIN/WHERE. Your query is pretty far down that path, so you can easily do:

Select c.first_name, c.last_name
From Caller c left join
     Issue i
     on c.Caller_id = i.Caller_id 
where i.caller_id is null;

Upvotes: 2

Related Questions