CODEWITHSUNDEEP
sqloracle-databaseouter-joinnvl
Wilheim
Wilheim

Reputation: 123

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!

Upvotes: 1

Views: 3097

Answers (4)

Patrick Marchand
Patrick Marchand

Reputation: 3445

Since no one mentioned it yet, you can use NVL2 which has the form:

NVL2(<expression>, <value if expression is not null>, <value if expression is null>)

So you could simply do:

NVL2(e.FIRST_NAME||e.LAST_NAME, TRIM(e.FIRST_NAME||' '||e.LAST_NAME), 'No One') "Full Name"

The TRIM is case it's possible that either the first or last name can be null (but not both); it would remove the trailing/leading space that would be left over.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Unlike other databases, Oracle allows the concatenation of NULL values in strings.

I would write your query as:

SELECT (CASE WHEN e.FIRST_NAME IS NULL AND e.LAST_NAME IS NULL THEN 'No One'
             WHEN e.FIRST_NAME IS NULL THEN e.LAST_NAME
             WHEN e.LAST_NAME IS NULL THEN e.FIRST_NAME
             ELSE e.FIRST_NAME || ' ' || e.LAST_NAME
        END) "Full Name", d.DEPARTMENT_NAME
FROM DEPARTMENTS d LEFT JOIN
     EMPLOYEES e
     ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

Notes:

  • This uses proper explicit JOIN syntax. The (+) notation for joins is ancient and deprecated. You should learn proper explicit JOIN syntax.
  • The logic no longer has extra spaces at the beginning/end of a name, when one of the values is missing.
  • The logic is explicit and uses the ANSI standard CASE expression.

And to add, there are fancy ways of shortening the expression:

coalesce(trim(e.FIRST_NAME || ' ' || e.LAST_NAME), 'No One')

I just think the case logic is much clearer.

Upvotes: 3

user2672165
user2672165

Reputation: 3049

"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.

Upvotes: 4

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

Because the expression e.FIRST_NAME||' '||e.LAST_NAME is never null. In case the row is outer joined, the value is ' '.

Upvotes: 2

Related Questions