sql - Return column name where Join condition is null -

i have 2 tables: employee , person structure

employee: id, personid, designation, isactive person:id, name, contact 

employee's personid column references person's id , can null

i need return employee's name , join criteria

select emp.salary, emp.designation, emp.isactive, p.name employee emp join person p on p.id = emp.personid or (p.id null , emp.id null) 

this incorrect requirement is:

if emp.personid = null, return p.name = null else return p.name = person's name table 

any pointers on this?

you need outer join

select emp.salary, emp.designation, emp.isactive, p.name  employee emp left join person p  on p.id = emp.personid  

when use inner join (or join) select rows matching join critiria, in example never nave null person name because if employee not assiciated person, record not selected.

if use outer join (left/right join), record main table (1st left , 2nd right) selected.

hope helps.