PostgreSQL:LEFT JOIN不返回整个左表 [英] PostgreSQL: LEFT JOIN does not return entire left table

问题描述

我有2张桌子:

1. town可存储290个城镇
2. hospital存储了67个不同城镇中引用的医院

1. town which stores 290 towns
2. hospital which stores hospitals referenced in 67 different towns

以下查询返回290行，这是LEFT JOIN应该执行的操作:

The following query returns 290 rows which is what the LEFT JOIN is supposed to do:

SELECT a.code, count(b.*) 
FROM town a 
   LEFT JOIN hospital b ON a.code = b.town_code 
GROUP BY a.code ;

现在，在下面的查询中，我仅添加WHERE子句以选择医院类型，将返回56行:

Now, the following query in which I just add a WHERE clause to select a type of hospital returns 56 rows:

SELECT
  a.code,
  count(b.*)
FROM town a 
  LEFT JOIN hospital b ON a.code = b.town_code
WHERE b.type = '01'
GROUP BY a.code ;

我不明白为什么，我想知道我如何也可以用第二个查询发出我的290行?

I don't understand why and I'm wondering how could I get my 290 rows issued with the second query as well?

推荐答案

在LEFT JOIN时，将右侧表的条件放在ON子句中，以获取真实的LEFT JOIN行为. (在WHERE子句中时，会得到常规的INNER JOIN结果.)

When LEFT JOIN, put the right side table's conditions in the ON clause to get true LEFT JOIN behavior. (When in the WHERE clause, you get regular INNER JOIN result.)

SELECT
  a.code,
  count(b.*)
FROM town a 
  LEFT JOIN hospital b ON a.code = b.town_code AND b.type = '01'
GROUP BY a.code

这篇关于PostgreSQL:LEFT JOIN不返回整个左表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！