CODEWITHSUNDEEP
sqlsql-server
user 123
user 123

Reputation: 41

not in operator in SQL

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

Upvotes: 0

Views: 98

Answers (4)

Shushil Bohara
Shushil Bohara

Reputation: 5656

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)

Upvotes: 0

CocoCrisp
CocoCrisp

Reputation: 815

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).

Upvotes: 3

Daniel Isaac
Daniel Isaac

Reputation: 745

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);

Upvotes: 2

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

try this:

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

Upvotes: 2

Related Questions