not in operator in SQL

Question

there is 2 tables

table1
ID
1
2
4
6
7

TABLE2
2
4
6

i want those number from table1 which is not in table2 how i do this ? i try this

select id from table1 t1
inner join table2 t2 on t1.id=t2.id
where t1.id not in (select id from table2) 

but this is not working

Answer 1

SELECT t1.id
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
WHERE t2.id IS NULL

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 id 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 id column (the one we are sure that exists, from table1).

Answer 2

try this:

select id from table1 t1 where t1.id not in (select t2.id from table2 t2)

Answer 3

You don't need to join the two tables in this case. You could just do

  select id from table1 A where A.id not in (select B.id from table2 B);

You could also just simply use the sql set difference EXCEPT operator to achieve this

  (select id from table1) except (select id from table2);

Answer 4

Use NOT IN or NOT EXISTS

select id from table1 t1
where t1.id not in (select id from table2) 


select id from table1 t1
where not exists (select id from table2 where id = t1.id)