当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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

网友答案:

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:

  • Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?
网友答案:

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) 
分享给朋友:
您可能感兴趣的文章:
随机阅读: