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?
Categories
39 replies on “MySQL: Finding the records in one table that are not in another table”
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…
thanks for this one! 🙂
It’s works like a charm……..Thanks & superb explaining…
thanx man …i t works…
Thanks – the “NOT IN” saved my day, just what I was looking for 🙂
W00T THNX ! 🙂
works perfectly in php too ^^ you saved me 😛
Sorry for digging the old thread, But thank you Stephan Segraves, You saved my days!
[…] go to Holy Shmoly for this simple concise […]
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
@Robert:
Your select query should not have “IS NOT IN”, it should just have “NOT IN”.
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!
Thanx a lot..:)
Fixed my problem!! thank u!1
thanks for the post man !
hey man here is a question for you….
i have two tables and need to get the info from the one if certain info is not in the other. eg:
table 1:
RoomName RoomCapacity RoomInSeasonPrice RoomOutSeasonPrice Description
table 2:
BookingID BookingDateIn BookingDateOut BookingRoomPrice GuestID EmployeeID RoomName
so i need all the info from the first table if the RoomName is not in the second table…
any idea’s??????????
As a note, even though MySQL now supports subqueries your original version is oftentimes much faster than the NOT IN(SELECT… method, especially once the data set gets to be huge.
Why? I believe you have the ability to take advantage of keys, with the NOT IN(SELECT… you’re really doing two full table scans.
How about:
select Table1.Field1, Table1.Field2, Table2.Field1 from Table1, Table2 where Table1.Field1 != Table2.Field1 and Table1.Field1 = “filter1” and Table2.Field1 = “filter2”
I didn’t check it!
WOOOOOOOOOOOOO!
Thanks so much. I’ve been searching for ages on how to exclude results from a certain MySQL query in my script.
After reading Diego’s post I realised I had named my table the wrong name lol.
I was literally sitting here for 2 hours confused – what a waste of time!
Anyway thanks Diego!!
Chris observation is the most important thing to say ..in this topic.
DON”T USE two full select in the same query, your server will work very hard .
You should take each values from the first table by using a “while ( ) { ; }” and check/compare/use it in a query related to the second.
IT IS TOUSAND LIGHT YEARS FASTER !
How about this,
SELECT * FROM TABLE1 WHERE FIELD NOT IN (SELECT DISTINCT(FIELD) FROM TABLE2,TABLE3,TABLE4 WHERE FIELD ”) AND FIELD ”;
expandability of the query is kept in mind..
thank you so much!!
love to see more and more people with the spirit of sharing knowledge!!
open source is the way!! 😉
thank you
that work amazing ola keep it up
Can anyone tell me why this works:
SELECT * FROM sales s WHERE NOT EXISTS (SELECT * FROM payments p WHERE s.SALES_ID = p.SALES_ID)
Bu this doesn’t?
SELECT * FROM sales s WHERE s.VENDOR_ID=2 AND WHERE NOT EXISTS (SELECT * FROM payments p WHERE s.SALES_ID = p.SALES_ID)
Are you not allowed to have filters on WHERE NOT EXISTS or is my syntax just incorrect?
Fixed a problem i am trying to solve for days now…. THANZ
Thanks! it helped me alot
Thanks a lot. Glad that i am alteast know this simple and handy MySQL query in 2013 !