Left-Outer Join in Postgres Not Returning Values for Null

Question

A download is comprised of download-times, download-time id, and buno ID. Faults are comprised of fault-codes, download-time id, status, and type. A download can have many faults, and can be joined on the download-time id.

Given a set of fault-codes, the results must contain each fault-code with a corresponding fault-count. If a fault-code is not found in the download, the fault-code must be returned with a fault-count of zero.

The problem seems to require an OUTER JOIN, but haven't seen this working as expected on Postgres as it does not seem to return the set with nulls from the LEFT table.

The query is below, with some details left out for brevity:

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount 
FROM    download_time d 
LEFT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id
    AND f.faultcode IN (1000,1100)
    AND f.statusid IN(2, 4)
WHERE (d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

The following day, I've edited to show the answer. All answers were close and had various elements of assistance. However, JayC's answer was closest. Here is the final SQL, having the only change as the WHERE clause taking the fault-code IN statement:

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount
FROM    download_time d  
RIGHT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id
        AND f.statusid IN(2, 4)
        AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012'
        AND d.bunoid = 166501
WHERE f.faultcode IN (1000,1100)
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

Thanks, all for your assistance! Love this site!

Answer 1

I'm giving my answer because I have significant doubts about the other answers. You gotta be careful about filter requirements. Remember, the where clause runs after your joins. So if there are any filter requirements in the where clause that refer to the non-outer joined table, you have (in many circumstances) nullified your outer join. So taking your sql, It seems the simplest solution is to either use the proper join or move the table names appropriately, and then move the filter conditions out of the where clause and into the join clause.

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount 
FROM    download_time d 
RIGHT OUTER JOIN fs_fault f ON 
    f.downloadtimeid = d.id
    AND f.faultcode IN (1000,1100)
    AND f.statusid IN(2, 4)
    AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

Another way which I believe should be equivalent is

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount 
FROM    download_time d 
RIGHT OUTER JOIN fs_fault f ON 
    f.downloadtimeid = d.id
    AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
WHERE
    f.faultcode IN (1000,1100)
    AND f.statusid IN(2, 4)
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

As it doesn't strictly matter where the filter requirements on fs_fault are. (and your SQL engine's going to change that all up anyway).

Edit: Here's a SQLFiddle demonstrating filtering on the join clause vs. the where clause.