postgresql Postgres 中的左外连接不返回 Null 值

Left-Outer Join in Postgres Not Returning Values for Null

提问by MAbraham1

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.

下载由下载时间、下载时间 id 和 buno ID 组成。故障由故障代码、下载时间 ID、状态和类型组成。一次下载可能有很多错误，并且可以在下载时间 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.

问题似乎需要 OUTER JOIN，但没有看到这在 Postgres 上按预期工作，因为它似乎没有从 LEFT 表返回带有空值的集合。

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:

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

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!

谢谢大家的帮助！喜欢这个网站！

回答by JayC

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

我给出我的答案是因为我对其他答案有很大的怀疑。你必须小心过滤器的要求。请记住， where 子句在您的 join 之后运行。因此，如果where 子句中存在任何引用非外部连接表的过滤器要求，则您（在许多情况下）已取消外部连接。因此，对于您的 sql，似乎最简单的解决方案是使用正确的连接或适当移动表名，然后将过滤条件从 where 子句移到 join 子句中。

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

因为 fs_fault 上的过滤器要求在哪里并不重要。（无论如何，您的 SQL 引擎都会改变这一切）。

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

编辑：这是一个SQLFiddle，演示了对 join 子句与 where 子句的过滤。

回答by krlmlr

This will require a RIGHT OUTER JOIN. The right outer join includes all values from the right table, with NULLs where there is no entry in the left table (I'm not sure if this will work with GROUP BY, though...) iffs_faultwere a table with all fault codes.

这将需要一个RIGHT OUTER JOIN. 右外连接包括来自右表的所有值，如果是一个包含所有故障代码的表，NULL则左表中没有条目的 s（我不确定这是否适用于GROUP BY...）。fs_fault

In your case, fs_faultseems to contain all faults for a download. Might this be the case for the unexpected behavior?

在您的情况下，fs_fault似乎包含下载的所有错误。这可能是意外行为的情况吗？

回答by kgrittn

If you want counts by faultcode, this seems like the simplest solution:

如果您想按故障代码计数，这似乎是最简单的解决方案：

WITH fc(faultcode) AS (VALUES (1000,1100))
SELECT fc.faultcode, count(d.downloadtimeid) as faultcount 
  FROM fc
  LEFT JOIN (fs_fault f ON f.faultcode = fc.faultcode
                       AND f.statusid IN(2, 4)
  JOIN download_time d ON d.id = f.downloadtimeid
                      AND d.bunoid = 166501
                      AND d.downloadtime::date BETWEEN date '2011-04-11'
                                                   AND date '2011-05-01')
  GROUP BY fc.faultcode
  ORDER BY fc.faultcode

Note that I kept your conditions, where faults are not counted if they don't have the right statusid or bunoid. I was a bit afraid that the date selection might not have been doing what you thought, so I suggested an alternative. Even that might not do what you want if you're using TIMESTAMP WITHOUT TIME ZONE, but that's another story. I also added an ORDER BYclause, since you probably don't want the results in inconsistent order; without that clause it may or may not be in GROUP BYsequence, and that might change without warning.

请注意，我保留了您的条件，如果它们没有正确的 statusid 或 bunoid，则不计算错误。我有点担心日期选择可能没有按照你的想法去做，所以我提出了一个替代方案。如果您使用TIMESTAMP WITHOUT TIME ZONE，即使那样也可能达不到您想要的效果，但那是另一回事了。我还添加了一个ORDER BY子句，因为您可能不希望结果的顺序不一致；如果没有该条款，它可能会或可能不会按GROUP BY顺序排列，并且可能会在没有警告的情况下发生变化。

回答by Gordon Linoff

The left outer join selects everything in the first table plus matching rows in the second table. The first table seems to consist of download attempts. So, your result from the "from" includes all download attempts.

左外连接选择第一个表中的所有内容以及第二个表中的匹配行。第一个表似乎包含下载尝试。因此，“来自”的结果包括所有下载尝试。

But, it does not necessarily contain all your fault codes. What is happening is that you have no faults for one or more codes that meet the criteria.

但是，它不一定包含您所有的故障代码。发生的情况是，您对符合标准的一个或多个代码没有错误。

You need a table that contains all the fault codes, in order for this to work. Here I just create a list of the fault codes as the first table. I think the following query does this:

您需要一个包含所有故障代码的表格，以使其正常工作。在这里，我只是创建了一个故障代码列表作为第一个表。我认为以下查询是这样做的：

SELECT thefaults.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount
FROM  (select 1000 as faultcode union all select 1100
      ) thefaults join
      fs_fault f
      on f.faultcode = thefaults.faultcode and
         f.statusid in (2, 4) left outer join
      download_time d
      ON f.downloadtimeid = d.id
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 

I admit: I am using SQL Server syntax to create "thefaults".

我承认：我使用 SQL Server 语法来创建“错误”。