Return column name where Join condition is null

I have 2 tables: Employee and Person with the structure

Employee: Id, PersonId, Designation, IsActive
Person:Id, Name, Contact

Employee's PersonId c开发者_如何学JAVAolumn references Person's Id and can be null

I need to return an employee's Name and my join criteria is

SELECT emp.Salary, emp.Designation, emp.IsActive, p.Name from Employee emp
JOIN Person P ON P.Id = emp.PersonId or (p.Id is NULL AND emp.Id IS NULL)

This is incorrect as my requirement is:

If emp.PersonId = null, return p.Name = NULL
else return p.Name = Person's Name from table

Any pointers on this?

---

you need an outer join

SELECT emp.Salary, emp.Designation, emp.IsActive, p.Name 
from Employee emp
left JOIN Person P 
ON P.Id = emp.PersonId 

when you use an INNER JOIN (or JOIN) you only select the rows matching the join critiria, in your example you would never nave a NULL Person name because if the Employee is not assiciated with a Person, that record would not be selected.

If you use OUTER JOIN (LEFT/RIGHT JOIN), ALL record from the main table (1st with LEFT and 2nd with RIGHT) will be selected.

Hope this helps.