MySQL NOT IN query

Question

I wanted to run a simple query to throw up all the rows of Table1 where a principal column value is not present in a column in another table  Table2    I tried using   SELECT   FROM Table1 WHERE Table1 principal NOT IN Table2 principal   This is instead throwing a syntax error  Google search led me to forums where people were saying that MySQL does not support NOT IN and something extremely complex needs to be used  Is this true  Or am I making a horrendous mistake

User · Answer

The subquery option has already been answered, but note that in many cases a LEFT JOIN can be a faster way to do this:

SELECT table1.*
FROM table1 LEFT JOIN table2 ON table2.principal=table1.principal
WHERE table2.principal IS NULL

If you want to check multiple tables to make sure it's not present in any of the tables (like in SRKR's comment), you can use this:

SELECT table1.*
FROM table1
LEFT JOIN table2 ON table2.name=table1.name
LEFT JOIN table3 ON table3.name=table1.name
WHERE table2.name IS NULL AND table3.name IS NULL

User · Answer

NOT IN vs  NOT EXISTS vs  LEFT JOIN   IS NULL in MySQL     MySQL  as well as all other systems except SQL Server  is able to optimize LEFT JOIN   IS NULL to return FALSE as soon the matching value is found  and it is the only system that cared to document this behavior      hellip   Since MySQL is not capable of using HASH and MERGE join algorithms  the only ANTI JOIN it is capable of is the NESTED LOOPS ANTI JOIN     hellip       Essentially   NOT IN  is exactly the same plan that LEFT JOIN   IS NULL uses  despite the fact these plans are executed by the different branches of code and they look different in the results of EXPLAIN  The algorithms are in fact the same in fact and the queries complete in same time      hellip       It   s hard to tell exact reason for  performance drop when using NOT EXISTS   since this drop is linear and does not seem to depend on data distribution  number of values in both tables etc   as long as both fields are indexed  Since there are three pieces of code in MySQL that essentialy do one job  it is possible that the code responsible for EXISTS makes some kind of an extra check which takes extra time      hellip       MySQL can optimize all three methods to do a sort of NESTED LOOPS ANTI JOIN      hellip     However  these three methods generate three different plans which are executed by three different pieces of code  The code that executes EXISTS predicate is about 30  less efficient   hellip          That   s why the best way to search for missing values in MySQL is using a LEFT JOIN   IS NULL or NOT IN rather than NOT EXISTS     emphases added

User · Answer

Be carefull NOT IN is not an alias for  lt  gt  ANY  but for  lt  gt  ALL   http   dev mysql com doc refman 5 0 en any-in-some-subqueries html  SELECT c FROM t1 LEFT JOIN t2 USING  c  WHERE t2 c IS NULL   cant  be replaced by  SELECT c FROM t1 WHERE c NOT IN  SELECT c FROM t2    You must use   SELECT c FROM t1 WHERE c  lt  gt  ANY  SELECT c FROM t2

User · Answer

To use IN  you must have a set  use this syntax instead   SELECT   FROM Table1 WHERE Table1 principal NOT IN  SELECT principal FROM table2

User · Answer

Unfortunately it seems to be a issue with MySql usage of  NOT IN  clause  the screen-shoot below shows the sub-query option returning wrong results   mysql gt  show variables like   version     ------------------------- ------------------------------    Variable name             Value                           ------------------------- ------------------------------    innodb version            1 1 8                            protocol version          10                               slave type conversions                                     version                   5 5 21                           version comment           MySQL Community Server  GPL      version compile machine   x86 64                           version compile os        Linux                           ------------------------- ------------------------------  7 rows in set  0 07 sec   mysql gt  select count    from TABLE A where TABLE A Pkey not in  select distinct TABLE B Fkey from TABLE B     ----------    count       ----------           0    ----------  1 row in set  0 07 sec   mysql gt  select count    from TABLE A left join TABLE B on TABLE A Pkey   TABLE B Fkey where TABLE B Pkey is null   ----------    count       ----------         139    ----------  1 row in set  0 06 sec   mysql gt  select count    from TABLE A where NOT EXISTS  select   FROM TABLE B WHERE TABLE B Fkey   TABLE A Pkey     ----------    count       ----------         139    ----------  1 row in set  0 06 sec   mysql gt

[sql] MySQL "NOT IN" query

Examples related to sql

Examples related to mysql