CODEWITHSUNDEEP
sqlsql-server
Arindam Das
Arindam Das

Reputation: 699

How to select data from two table with out using "NOT IN" in sql server?

I have 2 tables

Emp1
ID | Name
1  | X
2  | Y
3  | Z

Emp2
ID | Salary
1  | 10
2  | 20

I want to show the IDs from Emp1 which are not present in Emp2 with out using NOT IN

so the result should be like this

ID
3

now what i have done is this :

select  e1.ID
from Emp1 e1 left join  Emp2 e2
on e1.ID <> e2.ID

but i am getting this :

ID
1
2
3
3

so what should i do ?? WITH OUT using NOT IN

Upvotes: 0

Views: 150

Answers (4)

Sarajog
Sarajog

Reputation: 164

What you need is what parado already said. Here's a good picture for some other Joins and what Range they give back:

enter image description here

Sarajog

Upvotes: 1

Robert
Robert

Reputation: 25753

Try left join with is null condition as below

select e1.id 
from emp1 e1
left join emp2 e2 on e2.id = e1.id
where e2.id is null

or not exists condition as below

select e1.id 
from emp1 e1
where not exists
(
  select 1
  from emp2 e2
  where e2.id = e1.id
)

Upvotes: 5

DNac
DNac

Reputation: 2783

Use this

select id from emp1
except
select id from emp2;

SQL Fiddle

Upvotes: 3

Abhishek Jain
Abhishek Jain

Reputation: 2607

Try this:

SELECT  
    e1.ID
FROM Emp1 e1 LEFT JOIN Emp2 e2 on e1.ID = e2.ID 
WHERE e2.ID IS NOT NULL

Upvotes: 1

Related Questions