September 16th, 2004
MySQL: Finding the records in one table that are not in another table
Development, by Donncha.OK, this’ll make users of “proper databases” cringe but because MySQL doesn’t support sub-selects you can’t do the most obvious way of selecting records in one table that aren’t in another. Here’s how it’s done, with a clear explanation of why it works!
select po.id
from po left join pn
on po.id=pn.id
where pn.id is null
Unfortunately, I can’t figure out how to filter the second table as I don’t want to select from the whole table, only a subset. Anyone solved this?
You might also like
- Swans in Ballydehob
Awwww! Such beautiful creatures! Majestic, rulers of their domain, proud! Oh- Deleting duplicate rows from a MySQL database
Esos shows a good way of removing duplicate entries from- Simple MySQL Backup
If, like me, you have a database full of small
If you like this post then please subscribe to my full RSS feed. You can also click here to subscribe by email. There are also my fabulous photos and funny videos to explore too!
If I am reading your post right you are wanting to filter the pn table. Technically it’s not possible because you are not actually selecting anything from that table, just from the po table where its ID does not reside in the pn table. So pn table will not contain anything for these records.
That’s a good point. I’ll have to do it some other way methinks.
I forgot to mention, if your MySQL version is 4.1+ it does support subqueries.
SELECT DISTINCT po.id
FROM po
WHERE po.id NOT IN (
SELECT pn.id
FROM pn);
Something like that should work…
Thank you for idea! I like MySQL
Great post. The NOT IN and sub select fixed my query issue. Thanks.
not in doesnt seem to work in php 4
The version of PHP should not matter for MySQL queries.
Stephan, Thanks a lot I only modified a bit the query and was very usefull foe me.
Funny I have to agree with rene, I just came across a funny thing with PHP 4. When doing a
SELECT a FROM table1 WHERE a IS NOT IN ( SELECT b FROM table2 )
Now I thought this is crazy how can php have anything to do with this its only a transport protocol right !! Well I went over to SQLyog or whatever you use pasted the SQL statement in and guess what…. it worked. So watch out if your mixing PHP 4 and a mySQL version that allows sub selects expect some strange things along the way. I would love that this was not the case but this is what I have found !!
Gonna have to get to the bottom of this one
Hi,thank you Stephan, the not in query resolve my problem, i have been searching in the mysql reference manual for exists and not exists subqueries , but the result for the query is 0 rows , i have this scenario , i have 2 tables , PollUsers with all the users authorized to answer the poll, and PollHistoryUser with all the users, that filled the poll and i wanna a list for users who not filled the poll.
PollUsers(idUser) PollHistoryUser(idUser)
1 1
2
3
Result List(idUser)
2
3
1. Not Exists SubQuery
select p.idUser from PollUsers p where not exists (select ph.idUser from PollHistoryUser ph)
Result: 0 Rows
2.Not in Query
select distinct p.idUser from PollUsers p where p.idUser not in (select ph.idUser from PollHistoryUser ph)
Result:625 Rows …Works!
thank you man.
Sweet!! Thanks for the tutorial…
Thank’s for this article. cause this article can help me in my work.
Thanks for the solve this just helped me get an extra 10 marks on my assignment. You da’man Stephan!
Thank you! This solved a problem I had Perfectly!
Genius! Thank you!
[...] go to Holy Shmoly for this simple concise [...]