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?
Look what I found today!
34 Comments
Stephan Segraves (4 comments.) on September 16, 2004 at 5:17 pm.
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.
Donncha (1707 comments.) on September 16, 2004 at 5:30 pm.
That’s a good point. I’ll have to do it some other way methinks.
Stephan Segraves (4 comments.) on September 16, 2004 at 5:34 pm.
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…
greg on February 7, 2011 at 11:31 pm.
thanks for this one!
Ishakya on August 31, 2011 at 9:43 am.
It’s works like a charm……..Thanks & superb explaining…
basil (1 comments.) on October 25, 2011 at 8:38 am.
thanx man …i t works…
Klaus @ TechPatio (1 comments.) on December 16, 2011 at 3:48 pm.
Thanks – the “NOT IN” saved my day, just what I was looking for
Cedric on February 15, 2012 at 1:09 pm.
W00T THNX !
works perfectly in php too ^^ you saved me
Pingback: Paddy’s blog » MySQL: Finding the records in one table that are not in another table
Dmitry (1 comments.) on February 19, 2008 at 3:00 pm.
Thank you for idea! I like MySQL
Chris (1 comments.) on June 25, 2008 at 7:53 am.
Great post. The NOT IN and sub select fixed my query issue. Thanks.
rene (1 comments.) on July 4, 2008 at 11:27 am.
not in doesnt seem to work in php 4
Dave on August 20, 2008 at 9:51 pm.
The version of PHP should not matter for MySQL queries.
Hector Lara on September 2, 2008 at 10:22 pm.
Stephan, Thanks a lot I only modified a bit the query and was very usefull foe me.
Robert on October 9, 2008 at 7:50 pm.
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
chopsmith on August 27, 2010 at 3:59 pm.
@Robert:
Your select query should not have “IS NOT IN”, it should just have “NOT IN”.
Diego Toala on October 31, 2008 at 6:19 pm.
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.
Anton Ashardi (1 comments.) on April 5, 2009 at 9:58 am.
Sweet!! Thanks for the tutorial…
Ermalia on July 14, 2009 at 7:20 am.
Thank’s for this article. cause this article can help me in my work.
Ollie Worley on October 20, 2009 at 11:55 pm.
Thanks for the solve this just helped me get an extra 10 marks on my assignment. You da’man Stephan!
Kurt Wittman (1 comments.) on January 17, 2010 at 11:10 pm.
Thank you! This solved a problem I had Perfectly!
Josef on February 9, 2010 at 3:16 pm.
Genius! Thank you!
Diana* on April 2, 2010 at 6:22 am.
Thanx a lot..:)
Sathya (1 comments.) on April 7, 2010 at 9:10 pm.
Fixed my problem!! thank u!1
myltik (1 comments.) on April 20, 2010 at 2:49 pm.
thanks for the post man !
gary on September 2, 2010 at 12:01 pm.
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??????????
Chris (1 comments.) on November 1, 2010 at 5:27 pm.
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.
Suren Sarathkumara on November 9, 2010 at 7:41 am.
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!
Rewards Generator (1 comments.) on December 19, 2010 at 5:49 pm.
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!!
Marius F. (1 comments.) on February 28, 2011 at 5:48 pm.
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 !
Indranil on March 8, 2011 at 8:46 am.
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..
anonym on January 21, 2012 at 9:02 pm.
thank you so much!!
love to see more and more people with the spirit of sharing knowledge!!
open source is the way!!
sadasdasd on March 25, 2012 at 10:30 am.
thank you
badru on April 30, 2012 at 5:56 am.
that work amazing ola keep it up