Search code examples
sqloracle-databaseouter-joinnvl

Why NVL() doesn't work in the following outer join(+)?

I'm trying to outer join the two tables, and when there is a null value displayed in the column of "Full Name", replace it with 'No one'.

The outer join worked fine, the problem is, the null value is still null, not 'No one'.

The following is my code. 

SELECT 
NVL(to_char(e.FIRST_NAME||' '||e.LAST_NAME),'No One') "Full Name",
d.DEPARTMENT_NAME
FROM EMPLOYEES e,DEPARTMENTS d
WHERE e.DEPARTMENT_ID(+)=d.DEPARTMENT_ID;

Follwing is a screenshot of the result. enter image description here

Thanks for having a look!

Solution

  • "NVL does not work" because there will always be at least a space character in the argument to NVL.

    SELECT 
decode(e.FIRST_NAME||e.LAST_NAME,null,'No one',e.FIRST_NAME||' '||e.LAST_NAME) "Full Name",
d.DEPARTMENT_NAME
FROM EMPLOYEES e,DEPARTMENTS d
WHERE e.DEPARTMENT_ID(+)=d.DEPARTMENT_ID;

    could be a working alternative.