MySQL Join Where Not Exists

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, how ever 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

From stackoverflow

• Try adding the following to your WHERE clause:

  AND `voter`.`id` NOT IN (SELECT `voter_id` FROM (`elimination`))
  

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

  -----sharks

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