Finding the differences in two tables

Question

I have two large tables in a database. They both contain a column called "name". My goal is to locate rows that contain names that are in one database but not the other.

I'm guessing there will be a join statement and a where, but I cannot figure out how to use the two in tandem in order to create a successful query.

Suggestions?

Answer 1

 SELECT * FROM TABLE_A WHERE NAME NOT IN 
   ( SELECT NAME FROM TABLE_B )

Answer 2

EXISTS might be faster than IN, see Difference between EXISTS and IN in SQL?.

You can use EXISTS like this. It's useful to know both approaches since they are not exactly equal. You can swap the EXISTS quantifier for SOME, ALL or ANY. I think you can figure out what would happen :)

select * from a1 where not exists(select 1 from a2 where name=a1.name);

Note that they are not 100% equal! SQL has three-valued logic!