PostgreSQL全外连接前过滤数据的替代实现方案咨询

需求是统计各流派（包括无流派的电影、没有对应电影的流派）中时长超过90分钟的电影数量。初始查询在全外连接后用WHERE过滤时长，导致无对应电影的流派（比如示例中的Drama）被过滤掉，无法统计。

表结构 #

Movie表 #

Genre表 #

初始查询的问题 #

初始查询代码：

SELECT
COALESCE(genre.name,'No genre'),
COUNT(movies.name)
FROM movies
FULL OUTER JOIN genre ON genre.id = movies.genre_id
WHERE movies.length > 90
GROUP BY genre.name

错误原因：WHERE movies.length > 90会过滤掉所有movies.length为NULL的行——也就是那些没有对应电影的流派（比如Drama），因为全外连接后这些流派对应的movies字段全为NULL，不满足过滤条件，所以最终结果里看不到Drama流派的统计。

非CTE、非子查询的解决方案 #

将时长过滤条件从WHERE移到JOIN的ON子句中，这样在全外连接时就只匹配符合时长要求的电影，同时保留所有流派记录：

SELECT
  COALESCE(genre.name, 'No genre') AS 流派名称,
  COUNT(movies.name) AS 符合条件的电影数量
FROM movies
FULL OUTER JOIN genre 
  ON genre.id = movies.genre_id 
  AND movies.length > 90  -- 把过滤条件放在这里
GROUP BY genre.name
ORDER BY 流派名称;

逻辑说明 #

1. 全外连接时，仅关联movies中时长>90分钟的记录，对于没有符合条件电影的流派（比如Drama），依然会保留该行，只是对应的movies字段为NULL。
2. COUNT(movies.name)会自动忽略NULL值，所以没有对应电影的流派统计结果为0，无流派的电影（The shining）会被归类到No genre，统计数量为1。

正确输出 #

对比其他方案 #

这个方法不需要子查询、CTE或自连接，直接通过调整过滤条件的位置解决问题，逻辑更直观，属于标准的全外连接用法，并非取巧。

内容的提问来源于stack exchange，提问作者Emieligeter