NVL function in Oracle 11g

Question

I do not have data in one of the tables in the following query; my result set is empty. I'm trying to use NVL() FUNCTION, however; it still gives empty set. Can someone help me to understand this behaviour. My expectation was to display '0', however; getting empty result. I'm using oracle 11g.

SELECT NVL(T8.COLLECTION_BALANCE_AMT, 0)
      from BV_COLLECTION_CLAIM T8 JOIN BV_CLAIM_LIABLE_INDV T2
      ON T8.CLAIM_ID = T2.CLAIM_ID
      JOIN BV_CLAIM_RECOVERY T3
      ON T8.CLAIM_ID = T3.CLAIM_ID

Answer 1

A join only returns matching rows. You're looking for a left join, which would return null for columns in the joined table where matching do not exist:

SELECT    NVL(T8.COLLECTION_BALANCE_AMT, 0)
FROM      BV_COLLECTION_CLAIM T8 
LEFT JOIN BV_CLAIM_LIABLE_INDV T2 -- here!
       ON T8.CLAIM_ID = T2.CLAIM_ID
LEFT JOIN BV_CLAIM_RECOVERY T3  -- here too!
       ON T8.CLAIM_ID = T3.CLAIM_ID