CODEWITHSUNDEEP
sqlsql-server
user5320422
user5320422

Reputation:

how to display records which is present in employee but not present in department table

i want to get records which is present in employees table but not in department i.e (Employees - department)

output should be like this

Employee(ID,Name), Department(Id,Name)

i tried this 

select * from Employee   as e 

  left  join Department as d on e.DeptId = d.ID

sample data 

ID  Name    DeptId  Salary  ID  Name
1   krishna  1     5000     1   developer
2   rakesh   2     8000     2   trainer
3   sanjay   3     9000     3   programmer
4   swapna   4     6000     4   seo
6   shiva    6     4000     NULL    NULL

i want to show records of shiva because he is in table employee but not in department table in sql server

Upvotes: 0

Views: 2759

Answers (2)

DaveFoyf
DaveFoyf

Reputation: 1

I had a little difficulty understanding the question but with your existing query a WHERE clause could be used to only show records that don't have an appartment.

select * from Employee   as e 
left  join Department as d on e.DeptId = d.ID 
WHERE d.ID is null

Alternatively

select 
* 
from Employee as e 
WHERE e.ID not in (SELECT d.ID FROM Department d)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460098

Either use an OUTER JOIN as you've already done and filter by d.ID is null(as Dave has already shown). Or use NOT EXISTS which is my favorite:

select e.*
from Employee e 
where not exists
(
    select 1 from Department d
    where e.DeptId = d.ID
)

Pros and cons of all approaches:

Upvotes: 1

Related Questions