Query to get non matching values from two table

Question

Two tables: Department, Employee

Department columns:

• DepartmentID,
• DepartmentName

Employee columns:

• EmployeeID
• EmployeeName
• DepartmentID

Now I want result with columns DepartmentName, EmployeeName for those records which doesn't have any matching values of DepartmentID in both the tables.

How can it be done? In how many other ways it can be done?

Answer 1

This is an extremely delayed answer, but I wanted to post it to help others that come across this - it's a good resource for understanding joins. In your case (or ones similar), you will want a FULL OUTER JOIN with exclusions (i.e., where NULL). The link includes an image and the syntax you need.

Here's the full poster to help understand other joins.

Sandy's second link is similar, but the graphic is nicer and easier to follow on the source I'm posting. I would have just commented, but my reputation isn't high enough.

Answer 2

SELECT d.DepartmentName,e.EmployeeName

FROM Department as d

FULL JOIN Employee as e

    ON e.DepartmentID = d.DepartmentID

WHERE e.DepartmentID IS null OR d.DepartmentID IS null

Answer 3

The below query would help you list of all departments that are not referenced in employee table

select DepartmentName
FROM Department d
left outer join EMPLOYEE e 
    on e.DepartmentID = d.DepartmentID 
where e.EmployeeID is null

The below query would help you list of all employees who don't have a corresponding department,

select EmployeeName
FROM EMPLOYEE e
left outer join Department d
    on e.DepartmentID = d.DepartmentID 
where d.DepartmentID is null

Answer 4

Really don't have time to try and give any code example at this point of time, but what you are looking for is may be achieved by using OUTER JOIN query of SQL server.

MSDN Join....

Joins in Sql Server...

Hope it helps.