CODEWITHSUNDEEP
oracle-databasejoin
Mallik Tiru
Mallik Tiru

Reputation: 11

Oracle- Left outer join on multiple tables not returning desired null values

I have 3 tables, CAL,SOURCE,HISTORY.

CAL TABLE CAL_DATE 01/05/16 02/05/16 03/05/16 04/05/16 05/05/16 06/05/16 07/05/16

SOURCE TABLE TABLE_ID GROUP 1210 Sales 1211 Reference 1230 Marketing 1245 Sales 1650 Reference 1784 Sales

HISTORY
RUN_DATE TABLE_ID STATUS 01/05/16 1210 COMPLETED 02/05/16 1210 COMPLETED 02/05/16 1211 COMPLETED 03/05/16 1211 COMPLETED 01/05/16 1230 COMPLETED 03/05/16 1230 COMPLETED

Query that i used.

SELECT TO_CHAR(C.CAL_DATE,'mm/dd/yyyy') AS CAL_DATE,TO_CHAR(C.CAL_DATE,'day') AS WDAY,X.* FROM CAL C LEFT OUTER JOIN
(
    SELECT S.GROUP,S.TABLE_ID,H.RUN_DATE,H.STATUS FROM TABLE S 
    LEFT JOIN HISTORY H
    ON S.TABLE_ID=H.TABLE_ID
    WHERE 1=1 
    AND STATUS='COMPLETED'    
)  X
ON TO_CHAR(C.CAL_DATE,'dd/mm/yyyy')=TO_CHAR(RUN_DATE,'dd/mm/yyyy')
AND X.TABLE_ID IN (1210,1211,1230)
WHERE TO_CHAR(C.CAL_DATE,'mm/dd/yyyy') <= TO_CHAR('03/05/2016','mm/dd/yyyy')
ORDER BY SOURCE_TABLE_ID ASC

Expected output is given below but i am getting different output. When I pass more than 1 table id incodition nulls are filtered out. Please help me to correct the query.

CAL_DATE TABLEID Status 01/05/16 1210 Completed 02/05/16 1210 Completed 03/05/16 null null 01/05/16 null null 02/05/16 1211 Completed 03/05/16 1211 Completed 01/05/16 1230 Completed 02/05/16 null null 03/05/16 1230 Completed

Upvotes: 1

Views: 1071

Answers (1)

krokodilko
krokodilko

Reputation: 36107

You need to gain a better understanding how a LEFT JOIN works (outer joins in general - left/right and full [outer] join)

The LEFT JOIN is always performed in two steps:

SELECT ....
FROM table1
LEFT JOIN table1 ON join_conditions
WHERE where_conditions

Step 1 - the LEFT JOIN is performed first (using conditions specified in ON clause to join two tables)
Step 2 - the WHERE conditions is applied to a resulset generated by the join in step 1

How the LEFT JOIN works - a quick reminder: LEFT JOIN returns always ALL rows from the left table, even these rows for which there is no match in the right table. When there is no match (the ON condition evaluates to false), LEFT JOIN returns NULLs for the right table.
The RIGHT JOIN works in the same way, but it returns all rows from the RIGHT table, not the left one as LEFT JOIN.

SO if you have this query:

SELECT S.GROUP,S.TABLE_ID,H.RUN_DATE,H.STATUS 
FROM source_table S 
LEFT JOIN HISTORY H
ON S.TABLE_ID=H.TABLE_ID
WHERE H.STATUS='COMPLETED'

the database first performs the LEFT JOIN, that is:

SELECT S.GROUP,S.TABLE_ID,H.RUN_DATE,H.STATUS 
FROM source_table S 
LEFT JOIN HISTORY H
ON S.TABLE_ID=H.TABLE_ID

The above query gives the following result (notice NULLs in the last 3 rows on the rigth side):

|   S.GROUP | S.TABLE_ID |                 H.RUN_DATE |  H.STATUS |
|-----------|------------|----------------------------|-----------|
|     Sales |       1210 |  January, 05 2016 00:00:00 | COMPLETED |
|     Sales |       1210 | February, 05 2016 00:00:00 | COMPLETED |
| Reference |       1211 | February, 05 2016 00:00:00 | COMPLETED |
| Reference |       1211 |    March, 05 2016 00:00:00 | COMPLETED |
| Marketing |       1230 |  January, 05 2016 00:00:00 | COMPLETED |
| Marketing |       1230 |    March, 05 2016 00:00:00 | COMPLETED |
|     Sales |       1245 |                     (null) |    (null) |
| Reference |       1650 |                     (null) |    (null) |
|     Sales |       1784 |                     (null) |    (null) |

And then the database performs the WHERE condition on the above resultset:

WHERE H.STATUS='COMPLETED'

Since NULL='COMPLETED' evaluates to FALSE, then the final result of the query is:

|     GROUP | TABLE_ID |                   RUN_DATE |    STATUS |
|-----------|----------|----------------------------|-----------|
|     Sales |     1210 |  January, 05 2016 00:00:00 | COMPLETED |
|     Sales |     1210 | February, 05 2016 00:00:00 | COMPLETED |
| Reference |     1211 | February, 05 2016 00:00:00 | COMPLETED |
| Reference |     1211 |    March, 05 2016 00:00:00 | COMPLETED |
| Marketing |     1230 |  January, 05 2016 00:00:00 | COMPLETED |
| Marketing |     1230 |    March, 05 2016 00:00:00 | COMPLETED |

that is: all NULLs were skipped.
See this demo: http://sqlfiddle.com/#!9/e2ed0/3

If you want to get also records with NULL values, you need to change this condition to:

WHERE ( H.STATUS='COMPLETED' OR H.STATUS IS NULL )

you can also remove ths condition from the WHERE clause, and add it to the ON condition of the LEFT JOIN, that is:

SELECT S.GROUP,S.TABLE_ID,H.RUN_DATE,H.STATUS 
FROM source_table S 
LEFT JOIN HISTORY H
ON ( S.TABLE_ID=H.TABLE_ID AND H.STATUS='COMPLETED' )

see the last query in this demo: http://sqlfiddle.com/#!9/e2ed0/3

Upvotes: 2

Related Questions