Oracle joining tables with null columns-CodePudding

I want to join 4 tables, like in a code below. Where IIN is primary key. But if IIN doenst exist in one table, my code's result will be null.

SELECT 
  COALESCE(D.TERRORIST, 0) as TERRORIST,
  COALESCE(D.SPECIAL,0) as SPECIAL,
  COALESCE(T.SIGN_INACTIVE, 0) as TAXPAYER,
  COALESCE(B.ANOTHER, 0) as OSB,
  COALESCE(B.LSBOO,0) as LSBOO,
  COALESCE(R.DELAY,0) as DELAY
FROM RBPM.LIST_CL_DWHFM D
    JOIN RBPM.LIST_CL_TAXPAYER T
      ON D.IIN = T.IIN
    JOIN RBPM.LIST_CL_BLACK B
      ON T.IIN = B.IIN
    JOIN RBPM.LIST_CL_RBO R
      ON B.IIN = R.IIN
WHERE 1=1
AND D.IIN = '123456789123'

For example:

Table RBPM.LIST_CL_DWHFM

IIN	TERRORIST	SPECIAL
null	null	null

Table RBPM.LIST_CL_TAXPAYER

IIN	TAXPAYER
'123456789123'	0

Table RBPM.LIST_CL_BLACK

IIN	OSB	LSBOO
'123456789123'	0	1

Table RBPM.LIST_CL_RBO

IIN	DELAY
'123456789123'	0

Expecting:

TERRORIST	SPECIAL	TAXPAYER	OSB	LSBOO	DELAY
null	null	0	0	1	0

Result:

TERRORIST	SPECIAL	TAXPAYER	OSB	LSBOO	DELAY
null	null	null	null	null	null

CodePudding user response：

If any table might have a null IIN you can build a list of all the IINs and left join onto it, (though it would be better to have at least one table that has all the IINs):

SELECT 
 COALESCE(D.TERRORIST, 0) as TERRORIST,
 COALESCE(D.SPECIAL,0) as SPECIAL,
 COALESCE(T.SIGN_INACTIVE, 0) as TAXPAYER,
 COALESCE(B.ANOTHER, 0) as OSB,
 COALESCE(B.LSBOO,0) as LSBOO,
 COALESCE(R.DELAY,0) as DELAY
FROM 
 (
  SELECT IIN FROM RBPM.LIST_CL_DWHFM
  UNION
  SELECT IIN FROM RBPM.LIST_CL_TAXPAYER
  UNION
  SELECT IIN FROM RBPM.LIST_CL_BLACK
  UNION
  SELECT IIN FROM RBPM.LIST_CL_RBO
 ) a
 LEFT JOIN RBPM.LIST_CL_DWHFM D ON a.IIN = D.IIN
 LEFT JOIN RBPM.LIST_CL_TAXPAYER T ON a.IIN = T.IIN
 LEFT JOIN RBPM.LIST_CL_BLACK B ON a.IIN = B.IIN
 LEFT JOIN RBPM.LIST_CL_RBO R ON a.IIN = R.IIN
WHERE 1=1
AND a.IIN = '123456789123'

You could also full outer join everything and put a coalesce/multiple or in the where

If you're only ever going to query one IIN you can:

SELECT 
 COALESCE(D.TERRORIST, 0) as TERRORIST,
 COALESCE(D.SPECIAL,0) as SPECIAL,
 COALESCE(T.SIGN_INACTIVE, 0) as TAXPAYER,
 COALESCE(B.ANOTHER, 0) as OSB,
 COALESCE(B.LSBOO,0) as LSBOO,
 COALESCE(R.DELAY,0) as DELAY
FROM 
 (
  SELECT '123456789123' AS IIN FROM DUAL
 ) x
 LEFT JOIN RBPM.LIST_CL_DWHFM D ON x.IIN = D.IIN
 LEFT JOIN RBPM.LIST_CL_TAXPAYER T ON x.IIN = T.IIN
 LEFT JOIN RBPM.LIST_CL_BLACK B ON x.IIN = B.IIN
 LEFT JOIN RBPM.LIST_CL_RBO R ON x.IIN = R.IIN

CodePudding user response：

NOW IT WORKS WELL WITH NULL COLUMNS,

SELECT 
     COALESCE(D.TERRORIST, 0) as TERRORIST,
     COALESCE(D.SPECIAL,0) as SPECIAL,
     COALESCE(T.SIGN_INACTIVE, 0) as TAXPAYER,
     COALESCE(B.ANOTHER, 0) as EX_OSB,
     COALESCE(B.LSBOO,0) as LSBOO,
     COALESCE(R.DELAY,0) as DELAY
    FROM 
     (
      SELECT IIN FROM RBPM.LIST_CL_DWHFM
      WHERE IIN = '123456789123'
      UNION
      SELECT IIN FROM RBPM.LIST_CL_TAXPAYER
      WHERE IIN = '123456789123'
      UNION
      SELECT IIN FROM RBPM.LIST_CL_BLACK
      WHERE IIN = '123456789123'
      UNION
      SELECT IIN FROM RBPM.LIST_CL_RBO
      WHERE IIN = '123456789123'
     ) ALLT
     LEFT JOIN RBPM.LIST_CL_DWHFM D ON ALLT.IIN = D.IIN
     LEFT JOIN RBPM.LIST_CL_TAXPAYER T ON ALLT.IIN = T.IIN
     LEFT JOIN RBPM.LIST_CL_BLACK B ON ALLT.IIN = B.IIN
     LEFT JOIN RBPM.LIST_CL_RBO R ON ALLT.IIN = R.IIN
    WHERE COALESCE(ALLT.IIN, #INP_CANCEL_CLN_IIN#) = '123456789123'

thanks Caius Jard