So I have a database like so:
Users
id | name
Emails
id | email
Table 3
users_id | email_id
How do I select all users who don’t have an entry in Table 3 (as in, doesn’t have an email)?
select id from Users
where id not in
(select users_id from Table3)
Perfect!
Thank you.
I did not know you could do that, I really need to sharpen my SQL knowledge.
This tute on subqueries might help you.
the NOT IN ( subquery ) construction often performs poorly
here’s an alternative:
SELECT Users.id
FROM Users
LEFT OUTER
JOIN Table3
ON Table3.users_id = Users.id
WHERE Table3.users_id IS NULL
“poorly” is a relative term.
Can you quantify how much poorer it performs. If it only saves a few nano seconds of execution time then it wouldn’t make any difference at all to me.
It can be significantly slower on larger sites - I had one query which used several exclusion subqueries like this , and I reduced the execution time from minutes (up to 10 on bad days) down to seconds just by going to OUTER JOINS. It’s even worse when dealing with string comparisons…
I only have to run this query once per year, it isn’t going to be designed in the application. However, I appreciate the alternative, Rudy. I still haven’t gotten around to reading your book but soon, I hope!
That may or may not be true because “larger” on its own doesn’t mean anything unless you quantify it. For example, how many records on average are in the tables you are querying?
Someone could just as easily tell me they notice no significant increase in execution time on “large” sites and again that would mean nothing unless they quantify “large”.
so, basically, you are free to choose whichever version of the query you want to run, it’s entirely up to you
just file away my alternative suggestion in case you need it some day
i’m just sayin
yeah, i can
by the number of times i’ve answered a thread about a poorly performing query, and the poster tries the LEFT OUTER JOIN with IS NULL and reports a marked performance improvement, versus the poster comes back and says it didn’t help
check my post count, i am not going to dig up those threads myself, but you should feel free to gather these stats for your own quantification if you are still in doubt
I never said that in all cases there is no significant difference in execution time.
Heck, even you said
the NOT IN ( subquery ) construction often performs poorly
which is saying that sometimes it does not perform poorly.
yeah, sometimes it’s fine
like when the tables are so small that mysql reads them into memory before doing the join
I’ve only ever used “small” tables - whatever “small” means
and you’ve only ever used the “slow” query, but never noticed
now you’re resorting to not telling the truth.
and then hawk, in another thread, is wondering why this website’s “community engagement” has dropped 
okay, i stand corrected
of course, yes, you might at some point have used the LEFT OUTER JOIN with IS NULL
i certainly wouldn’t want to be caught in a lie, eh
lol
That’s for everyone else here and elsewhere to judge for themselves if they want to
I can only go by what you actually say, not what you claim you meant to say after being caught out.
a fwiw tip 
- maybe next time consider getting your facts straight before posting 
“maybe next time…” – you sound like the geico caveman
for what it’s worth, it wasn’t a lie, which is a deliberate misrepresentation, but a conclusion that i reached based on evidence that you presented through the course of the thread
and as long as we’re dispensing behavioural advice, perhaps you should try to be just a tiny bit less confrontational and argumentative
so, basically, you are free to choose whichever version of the query you want to run, it’s entirely up to you
just file away my alternative suggestion in case you need it some day
i’m just sayin