MySQL：选择两个表之间不常见的数据

Question

I have table1 with 3 numbers and table2 with 1 number:

table1       table2
n              n
1              1
2
3

I want to select data from table1 that is NOT present in table2 (numbes 2 and 3). I tried:

select table1.* from table1, table2 where table1.n <> table2.n

I also tried other where clauses:

where table1.n not like table2.n
where not table1.n = table2.n

But I don't get the results. I know it can be done in multiple steps, but I wonder if there is a simple query to do it. Thanks

Answer 1

You can do that using NOT IN or NOT EXISTS.

select * from table1
where table1.n not in (select table2.n from table2);

select * from table1
where  not exists (select table2.n from table2 where table2.n = table1.n);