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

User · Answer

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

User · Answer

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

User · Answer

There are three possible ways to do that    Option  SELECT  lt   FROM    table left lt LEFT JOIN     table right rt ON      rt value   lt value WHERE   rt value IS NULL  Option  SELECT  lt   FROM    table left lt WHERE   lt value NOT IN   SELECT  value FROM    table right rt    Option  SELECT  lt   FROM    table left lt WHERE   NOT EXISTS   SELECT  NULL FROM    table right rt WHERE   rt value   lt value

[mysql] MySQL Join Where Not Exists

Examples related to mysql

Examples related to join

Examples related to not-exists