Categories
Development

MySQL: Finding the records in one table that are not in another table

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?

Comments

comments

By Donncha

Donncha Ó Caoimh is a software developer at Automattic and WordPress plugin developer. He posts photos at In Photos and can also be found on Google+ and Twitter.

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.

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.

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!! 😉

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?

Leave a Reply