带有空列的Oracle连接表

Question

我想加入4个表，例如在下面的代码中。其中IIN是主要钥匙。但是，如果一张表中存在IIN DOENST，则我的代码的结果将为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	延迟
null	Null	0	0	1	0

结果：

恐怖	特殊	纳税人	OSB	LSBOO	延迟
null	null	null	null	null	null null null null null null null null

Answer 1

如果任何表格可能的空IIN iin，您可以构建所有IIN的列表并将其加入到它上（尽管最好至少拥有一个具有所有IIN的表）：

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'

您也可以完全加入所有内容，将凝聚/多层次或在哪里放置。我将其中的1 = 1留在一个假设上，即您在编程中串联了其他子句。 请将其删除，您可以：

如果您只想查询一个IIN，

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

Answer 2

现在，它可以与空列一起使用，

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, '123456789123') = '123456789123'

谢谢Caius Jard