PostgreSQL中LEFT OUTER JOIN加WHERE字段IS NULL退化为INNER JOIN问题

核心问题原因 #

LEFT JOIN的执行逻辑是先完成关联生成临时结果集，再执行WHERE子句的过滤逻辑：

1. 第一个查询没有在关联阶段过滤年份，item表的VCR记录会关联到price表中year=2000的有效记录，临时结果集中VCR对应的p.year值为2000，而非NULL
2. 后续执行WHERE p.year = 2010 OR p.year IS NULL过滤时，VCR对应的行不符合条件，直接被筛除，最终就只剩下符合年份要求的电视机记录，表现效果类似INNER JOIN。

其他正确查询方案 #

除了已经提到的「JOIN子查询」和「ON条件加年份过滤」两种写法，还可以用UNION联合查询实现：

-- 先查有2010年定价的商品
SELECT i.*, p.year, p.value FROM item i 
JOIN price p ON i.id = p.item_id WHERE p.year = 2010
UNION ALL
-- 再补全没有2010年定价的商品
SELECT i.*, NULL AS year, NULL AS value FROM item i 
WHERE NOT EXISTS (
    SELECT 1 FROM price p WHERE p.item_id = i.id AND p.year = 2010
);

Django ORM 对应写法 #

1. 基于FilteredRelation（对应ON条件加过滤的SQL写法） #

Django 2.0及以上版本支持FilteredRelation，可以直接将过滤条件放到JOIN的ON子句中：

from django.db.models import FilteredRelation, Q

result = Item.objects.annotate(
    price_2010=FilteredRelation('price', condition=Q(price__year=2010))
).values('id', 'name', 'price_2010__year', 'price_2010__value')

2. 基于Prefetch预取（对应JOIN子查询的SQL写法） #

适合需要完整获取关联对象的场景：

from django.db.models import Prefetch

result = Item.objects.prefetch_related(
    Prefetch('price_set', queryset=Price.objects.filter(year=2010), to_attr='price_2010')
).all()
# 遍历时可通过 item.price_2010 取到对应2010年的价格列表，无数据时为空列表

3. 基于Subquery子查询 #

对应关联子查询的写法：

from django.db.models import Subquery, OuterRef

price_2010_sub = Price.objects.filter(item_id=OuterRef('id'), year=2010)
result = Item.objects.annotate(
    year=Subquery(price_2010_sub.values('year')),
    value=Subquery(price_2010_sub.values('value'))
).values('id', 'name', 'year', 'value')

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