Oracle JOIN that include NULLS?

Question

I have the following simple SELECT Statement:

SELECT COUNT (*)
    FROM EPS_PROPOSAL prop
    WHERE prop.REQUESTED_START_DATE_INITIAL >= DATE '2018-10-01';
 -- 2183

Now, I have 2 additional tables that I'd like to join to my EPS_PROPOSAL table. BUDGET_DOCUMENT and BUDGET

The relationship between the 3 tables is so:

EPS_PROPOSAL.DOCUMENT_NUMBER ===> BUDGET_DOCUMENT.PARENT_DOCUMENT_KEY

BUDGET_DOCUMENT.DOCUMENT_NUMBER ===> BUDGET.DOCUMENT_NUMBER

Given this relationship, I'm not understanding the following result:

   SELECT COUNT (*) 
       FROM EPS_PROPOSAL prop
       JOIN BUDGET_DOCUMENT doc on prop.DOCUMENT_NUMBER = doc.PARENT_DOCUMENT_KEY
       JOIN BUDGET bud on doc.DOCUMENT_NUMBER = bud.DOCUMENT_NUMBER
       WHERE prop.REQUESTED_START_DATE_INITIAL >= DATE '2018-10-01' AND bud.FINAL_VERSION_FLAG = 'Y'; 
   -- 1585

Looking into the actual records of the later SELECT statement, I'm noticing that it is leaving out all records where BUDGET.TOTAL_COST is NULL AND BUDGET.FINAL_VERSION_FLAG is NULL.

SELECT prop.NPS_PROPOSAL_NUMBER, bud.TOTAL_COST FROM EPS_PROPOSAL prop
    JOIN BUDGET_DOCUMENT doc on prop.DOCUMENT_NUMBER = doc.PARENT_DOCUMENT_KEY
    JOIN BUDGET bud on doc.DOCUMENT_NUMBER = bud.DOCUMENT_NUMBER
    WHERE prop.REQUESTED_START_DATE_INITIAL >= DATE '2018-10-01' AND bud.FINAL_VERSION_FLAG = 'Y'
    ORDER BY TO_NUMBER(prop.NPS_PROPOSAL_NUMBER); -- 1585

Question: How can I include all the records that intersect these 3 tables including those records for the BUDGET table WHERE TOTAL_COST and FINAL_VERSION_FLAG are also NULL but still satisfy my >= DATE filter?

Answer 1

I suggest you

• start with the initial query (the one that contains only one table and returns 2183 rows)
• then join only one table (budget_document) and check number of rows returned
• join another table (budget); check number of rows
• finally, add final_version_flag condition

Because - to me - it looks as if total_cost is a victim here. It's not that its NULL values cause rows not to be returned, but joins don't return those rows.

Pick any of those that are missing and re-run everything once again, this time by filtering rows on document_number (or its pair row(s) in other table(s)) and see what's going on as you add new elements to the query.

Alternatively/additionally, see whether outer joins would do any good (as now you're using inner joins which means that only rows that "match" are returned as a result).

We can't do that for you, we don't have your data.