儘管存在記錄，但子查詢不會返回任何行

我有兩個表，即員工和付款。以下是兩者的內容：

員工表

EmpID |EmpName
------ --------
  113    Harry

付款表

EmpID | Amount | Period | PaymentType | St
------------------------------------------
------------------------------------------     
113 |  100   | 201207 |    1        |  1
------------------------------------------
113 |  70    | 201206 |    1        |  0
------------------------------------------
113 |  120   | 201207 |    1        |  0
------------------------------------------
113 |  200   | 201207 |    1        |  1
------------------------------------------
113 |  90    | 201207 |    2        |  1
------------------------------------------

這是我的查詢：

   SELECT EMP.EMPID ID, PAID_TOTAL,PAID_CURRENT,PAID_PAST,PAID_OT
 FROM EMPLOYEES EMP
 , (
     SELECT EMPID,SUM(AMOUNT)PAID_TOTAL
     FROM PAYMENTS
     WHERE PERIOD<='201207' AND ST=1
     GROUP BY EMPID
   )
   PY_TOTAL, (
     SELECT EMPID,SUM(AMOUNT)PAID_CURRENT
     FROM PAYMENTS
     WHERE PERIOD='201207' AND PAYMENTTYPE=1 AND ST=1
     GROUP BY EMPID
   )
   PY_CURRENT, (
     SELECT EMPID,SUM(AMOUNT)PAID_PAST
     FROM PAYMENTS
     WHERE PERIOD<'201207' AND PAYMENTTYPE=1 AND ST=1
     GROUP BY EMPID
   )
   PY_PAST, (
     SELECT EMPID,SUM(AMOUNT)PAID_OT
     FROM PAYMENTS
     WHERE PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1
     GROUP BY EMPID
   )
   PY_OT
 WHERE 
   (
     EMP.EMPID=PY_TOTAL.EMPID OR EMP.EMPID=PY_CURRENT.EMPID OR
     EMP.EMPID=PY_PAST.EMPID OR EMP.EMPID=PY_OT.EMPID
   );

我期望得到的是：

 ID | PAID_TOTAL | PAID_CURRENT | PAID_PAST | PAID_OT
 113     460          300             70         90

但是上述查詢沒有返回任何行。

其中一個子查詢（派生表）返回 0 行，因此整個查詢（即 5 個表的交叉連接）也返回 0 行。

您可以使用適當的顯式連接進行重寫。如果您將它們設為外部 ( LEFT) 連接，那麼如果其中一個子查詢有 0 行，您將獲得結果：

 SELECT EMP.EMPID ID, 
        COALESCE(PAID_TOTAL, 0) AS PAID_TOTAL,
        COALESCE(PAID_CURRENT, 0) AS PAID_CURRENT,
        COALESCE(PAID_PAST, 0) AS PAID_PAST,
        COALESCE(PAID_OT, 0) AS PAID_OT
 FROM EMPLOYEES EMP
   LEFT JOIN (
     SELECT EMPID,SUM(AMOUNT)PAID_TOTAL
     FROM PAYMENTS
     WHERE PERIOD<='201207' AND ST=1
     GROUP BY EMPID
     ) PY_TOTAL ON EMP.EMPID=PY_TOTAL.EMPID
   ---
   --- more LEFT joins
   ---
   LEFT JOIN (
     SELECT EMPID,SUM(AMOUNT)PAID_OT
     FROM PAYMENTS
     WHERE PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1
     GROUP BY EMPID
   ) PY_OT ON EMP.EMPID=PY_OT.EMPID ;

---

但它可以寫得更簡單，只有一個Payments表連接：

 SELECT 
     EMP.EMPID AS ID, 
     COALESCE(SUM(CASE WHEN PERIOD<='201207' AND ST=1 THEN AMOUNT END), 0)
         AS PAID_TOTAL,
     COALESCE(SUM(CASE WHEN PERIOD='201207' AND PAYMENTTYPE=1 AND ST=1 THEN AMOUNT END), 0)
         AS PAID_CURRENT,
     COALESCE(SUM(CASE WHEN PERIOD<'201207' AND PAYMENTTYPE=1 AND ST=1 THEN AMOUNT END), 0)
         AS PAID_PAST,
     COALESCE(SUM(CASE WHEN PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1 THEN AMOUNT END), 0)
         AS PAID_OT
 FROM EMPLOYEES  EMP
   LEFT JOIN PAYMENTS  P
     ON EMP.EMPID = P.EMPID
 GROUP BY EMP.EMPID ;

這可能效率不高或需要不同的索引，但可以輕鬆更改為僅在結果中包含至少有一筆付款的員工：

 FROM EMPLOYEES  EMP
   INNER JOIN PAYMENTS  P
     ON EMP.EMPID = P.EMPID

或在審查期間/限制內至少支付一筆款項的員工：

 FROM EMPLOYEES  EMP
   INNER JOIN PAYMENTS  P
     ON  EMP.EMPID = P.EMPID
     AND (P.PERIOD<='201207' AND P.ST=1)

(P.PERIOD<='201207' AND P.ST=1)條件是所有 4 個條件中的OR一個，簡化。

引用自：https://dba.stackexchange.com/questions/58189