seo Question posted in
Some basic information to get you started with this can be found here.

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

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

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search