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

37 Replies

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

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!

37 thoughts on “MySQL: Finding the records in one table that are not in another table”

  1. Stephan Segraves (4 comments.)

    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.

    Reply ↓
  3. Stephan Segraves (4 comments.)

    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…

    Reply ↓

  4. Pingback: Paddy’s blog » MySQL: Finding the records in one table that are not in another table

  10. Robert

    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

    Reply ↓
  11. Diego Toala

    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.

    Reply ↓
  20. gary

    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??????????

    Reply ↓
  21. Chris (1 comments.)

    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.

    Reply ↓
  22. Suren Sarathkumara

    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!

    Reply ↓
  23. Rewards Generator (1 comments.)

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

    Reply ↓
  24. Marius F. (1 comments.)

    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 !

    Reply ↓
  25. Indranil

    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..

    Reply ↓
  26. anonym

    thank you so much!!

    love to see more and more people with the spirit of sharing knowledge!!

    open source is the way!! ;)

    Reply ↓
  29. Dave Reynolds

    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?

    Reply ↓

Leave a Reply