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

table1 (id, name)
table2 (id, name)

Query:

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

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.

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

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.

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.

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.

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/

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)

SELECT <column_list>
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/

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

This is pure set theory which you can achieve with the minus operation.

select id, name from table1
minus
select id, name from table2