Postgres中的左外联接不返回Null值 [英] Left-Outer Join in Postgres Not Returning Values for Null

问题描述

下载包括下载时间，下载时间ID和Buno ID。
故障由故障代码，下载时间ID，状态和类型组成。下载可能有很多错误，并且可以与下载时间ID关联。

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.

该问题似乎需要外部连接，但尚未在Postgres上按预期方式工作，因为它似乎没有从LEFT表中返回带有空值的集合。

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

第二天，我进行了编辑以显示答案。所有答案都很接近，并有各种帮助。但是，JayC的答案是最接近的。 这是最终的SQL，唯一的变化是WHERE子句采用了错误代码IN语句：

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!

推荐答案

我给出我的答案，因为我对其他答案有很大的疑问。您必须注意过滤器的要求。请记住，where子句在加入后运行。因此，如果where子句中的任何过滤器要求 都引用了非外部联接表，则您（在许多情况下）都将外部联接无效。因此，以您的sql为例，似乎最简单的解决方案是使用适当的联接或适当地移动表名，然后将过滤条件从where子句移至join子句。

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

因为对fs_fault的过滤器要求在哪里并不严格。 （并且您的SQL引擎将完全改变它。）

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

编辑：这是 SQLFiddle 展示了对join子句和where子句的过滤。

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

这篇关于Postgres中的左外联接不返回Null值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！