Subquerying results in no row returned although records exist

Question

I have two tables, namely Employees and Payments. Below is the contents of the two:

The Employees table

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

The Payments table

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
------------------------------------------

Here's my query:

    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
    );

What I would expect to get is:

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

But no row is returned from the above query.

Answer 1

One of the subqueries (derived tables) returns 0 rows and thus the whole query - which is a cross join of 5 tables - returns 0 rows as well.

You could rewrite with proper, explicit joins. If you make them outer (LEFT) joins, then you'll get results if one of the subqueries has 0 rows:

  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 ;

---

But it could be written even simpler, with only one join to Payments table:

  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 ;

This may not be as efficient or need different indexes but it can easily be changed to include in the results only employees with at least one payment:

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

or employees with at least one payment in the examined period/restrictions:

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

The (P.PERIOD<='201207' AND P.ST=1) condition is the OR of all the 4 conditions, simplified.

Answer 2

Like I mentioned in the comment above, an alternative answer would be to put the subqueries in the select clause, as follows:

SELECT
  EMPID AS ID, 
  (
    SELECT SUM(AMOUNT)
    FROM PAYMENTS
    WHERE PERIOD<='201207' AND ST=1
    AND EMPID = EMP.EMPID
  ) AS PAID_TOTAL,
  (
    SELECT SUM(AMOUNT)
    FROM PAYMENTS
    WHERE PERIOD='201207' AND PAYMENTTYPE=1 AND ST=1
    AND EMPID = EMP.EMPID
  ) AS PAID_CURRENT,
  (
    SELECT SUM(AMOUNT)
    FROM PAYMENTS
    WHERE PERIOD<'201207' AND PAYMENTTYPE=1 AND ST=1
    AND EMPID = EMP.EMPID
  ) AS PAID_PAST,
  (
    SELECT SUM(AMOUNT)
    FROM PAYMENTS
    WHERE PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1
    AND EMPID = EMP.EMPID
  ) AS PAID_OT
FROM EMPLOYEES EMP;

I often prefer this approach as it avoids GROUP BY, any explicit (OUTER) JOINs and COALESCE.