MySQL Join Where Not Exists

Question

I have a MySQL query that joins two tables

• Voters
• Households

They join on voters.household_id and household.id.

Now what I need to do is to modify it where the voter table is joined to a third table called elimination, along voter.id and elimination.voter_id. However the catch is that I want to exclude any records in the voter table that have a corresponding record in the elimination table.

How do I craft a query to do this?

This is my current query:

SELECT `voter`.`ID`, `voter`.`Last_Name`, `voter`.`First_Name`,        `voter`.`Middle_Name`, `voter`.`Age`, `voter`.`Sex`,        `voter`.`Party`, `voter`.`Demo`, `voter`.`PV`,        `household`.`Address`, `household`.`City`, `household`.`Zip` FROM (`voter`) JOIN `household` ON `voter`.`House_ID`=`household`.`id` WHERE `CT` = '5' AND `Precnum` = 'CTY3' AND  `Last_Name`  LIKE '%Cumbee%' AND  `First_Name`  LIKE '%John%' ORDER BY `Last_Name` ASC LIMIT 30  

Answer 1

I'd probably use a LEFT JOIN, which will return rows even if there's no match, and then you can select only the rows with no match by checking for NULLs.

So, something like:

SELECT V.* FROM voter V LEFT JOIN elimination E ON V.id = E.voter_id WHERE E.voter_id IS NULL 

Whether that's more or less efficient than using a subquery depends on optimization, indexes, whether its possible to have more than one elimination per voter, etc.

Answer 2

I'd use a 'where not exists' -- exactly as you suggest in your title:

SELECT `voter`.`ID`, `voter`.`Last_Name`, `voter`.`First_Name`,        `voter`.`Middle_Name`, `voter`.`Age`, `voter`.`Sex`,        `voter`.`Party`, `voter`.`Demo`, `voter`.`PV`,        `household`.`Address`, `household`.`City`, `household`.`Zip` FROM (`voter`) JOIN `household` ON `voter`.`House_ID`=`household`.`id` WHERE `CT` = '5' AND `Precnum` = 'CTY3' AND  `Last_Name`  LIKE '%Cumbee%' AND  `First_Name`  LIKE '%John%'  AND NOT EXISTS (   SELECT * FROM `elimination`    WHERE `elimination`.`voter_id` = `voter`.`ID` )  ORDER BY `Last_Name` ASC LIMIT 30 

That may be marginally faster than doing a left join (of course, depending on your indexes, cardinality of your tables, etc), and is almost certainly much faster than using IN.