How to select all records from one table that do not exist in another table

Question

table1  id  name    table2  id  name    Query   SELECT name    FROM table2   -- that are not in table1 already

User · Answer

SELECT  lt column list gt  FROM TABLEA a LEFTJOIN TABLEB b  ON a Key   b Key  WHERE b Key IS NULL      https   www cloudways com blog how-to-join-two-tables-mysql

User · Answer

You can either do  SELECT name FROM table2 WHERE name NOT IN      SELECT name       FROM table1    or  SELECT name  FROM table2  WHERE NOT EXISTS       SELECT         FROM table1       WHERE table1 name   table2 name      See this question for 3 techniques to accomplish this

User · Answer

You can use EXCEPT in mssql or MINUS in oracle  they are identical according to    http   blog sqlauthority com 2008 08 07 sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle

User · Answer

See query   SELECT   FROM Table1 WHERE id NOT IN  SELECT          e id     FROM         Table1 e             INNER JOIN         Table2 s ON e id   s id     Conceptually would be  Fetching the matching records in subquery and then in main query fetching the records which are not in subquery

User · Answer

Here s what worked best for me   SELECT   FROM  T1 EXCEPT SELECT a   FROM  T1 a JOIN  T2 b ON a ID   b ID   This was more than twice as fast as any other method I tried

User · Answer

I m going to repost  since I m not cool enough yet to comment  in the correct answer    in case anyone else thought it needed better explaining   SELECT temp table 1 name FROM original table 1 temp table 1 LEFT JOIN original table 2 temp table 2 ON temp table 2 name   temp table 1 name WHERE temp table 2 name IS NULL   And I ve seen syntax in FROM needing commas between table names in mySQL but in sqlLite it seemed to prefer the space   The bottom line is when you use bad variable names it leaves questions   My variables should make more sense   And someone should explain why we need a comma or no comma

User · Answer

Watch out for pitfalls  If the field Name in Table1 contain Nulls you are in for surprises  Better is   SELECT name FROM table2 WHERE name NOT IN      SELECT ISNULL name           FROM table1

User · Answer

SELECT t1 name FROM table1 t1 LEFT JOIN table2 t2 ON t2 name   t1 name WHERE t2 name IS NULL   Q  What is happening here   A  Conceptually  we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column  If there is no such row  we just leave the table2 portion of our result empty for that row  Then we constrain our selection by picking only those rows in the result where the matching row does not exist  Finally  We ignore all fields from our result except for the name column  the one we are sure that exists  from table1    While it may not be the most performant method possible in all cases  it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

User · Answer

This is pure set theory which you can achieve with the minus operation    select id  name from table1 minus select id  name from table2

User · Answer

I don t have enough rep points to vote up froadie s answer  But I have to disagree with the comments on Kris s answer  The following answer  SELECT name FROM table2 WHERE name NOT IN      SELECT name       FROM table1   Is FAR more efficient in practice  I don t know why  but I m running it against 800k  records and the difference is tremendous with the advantage given to the 2nd answer posted above  Just my  0 02

User · Answer

That work sharp for me  SELECT    FROM  dbo   table1  t1 LEFT JOIN  dbo   table2  t2 ON t1  t1 ID    t2  t2 ID  WHERE t2  t2 ID  IS NULL

[sql] How to select all records from one table that do not exist in another table?

Examples related to sql

Examples related to sql-server

Examples related to tsql