1

I need to write a query that returns the records that exist in one MySql table and do not exist in the other table. In this example, I want all of the wafers that exist in the wafer_log table that do not exist in the bt_log table.

Here is the current query I'm using:

    SELECT wafer_log.wafer, bt_log.id AS blid, wafer_log.id AS wlid 
    FROM bt_log RIGHT OUTER JOIN wafer_log
    ON bt_log.waferid = wafer_log.wafer
    WHERE wafer_log.wafer IS NOT NULL AND bt_log.id IS NULL;

My thought here was to get the wafer name from the table I care about as well as the ids for both of the tables and do an outer join on the wafer name. From there, I wanted to see all of the results where wafer name was not null in wafer_log table and id in the bt_log is null.

I don't feel like the results look right though.

Any help would be appreciated.

kformeck
  • 1,703
  • 5
  • 22
  • 43
  • This is similar to the question marked as duplicate but not identical. As to the selected answer, yes it works however subqueries (NOT IN (SELECT)) degrade exponentially. A better response would be: `SELECT wafer_log.wafer, wafer_log.id AS wlid FROM bt_log LEFT JOIN wafer_log ON bt_log.waferid = wafer_log.wafer WHERE wafer_log.wafer IS NULL;` Iterate over bt_log and display any wafer logs that are not found. Best results assume wafer_log.wafer and bt_log.waferid are indexed. http://dev.mysql.com/doc/refman/5.0/en/join.html – Will B. Feb 26 '14 at 01:45

1 Answers1

2

You don't need a join to do this, assuming 'id' is your primary key you can use a subquery:

SELECT wafer_log.wafer, wafer_log.id AS wlid 
FROM wafer_log
WHERE wafer_log.id NOT IN (SELECT id FROM bt_log);