How To Make a Query That Returns a Record That Is Not In Another Table? [duplicate]

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

Query:

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

Solution 1:

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

Solution 2:

You can either do

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT name 
     FROM table1)

SELECT name 
FROM table2 
WHERE NOT EXISTS 
    (SELECT * 
     FROM table1 
     WHERE table1.name = table2.name)

See this question for 3 techniques to accomplish this

Solution 3:

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.

Solution 4:

SELECT <column_list>
FROM TABLEA a
LEFTJOIN TABLEB b 
ON a.Key = b.Key 
WHERE b.Key IS NULL;

enter image description here

https://www.cloudways.com/blog/how-to-join-two-tables-mysql/

Solution 5:

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

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