左外部连接，其中"字段为空"用作内部连接

Question

今天我在PostgreSQL中遇到了一些无法解释的(对我来说)行为-LEFT OUTER JOIN不返回主表的记录(与空连接一个字段)，如果在WHERE表达式中使用了连接表字段

为了更容易地掌握案例细节，我将提供一个示例。因此，假设我们有2个表:item包含一些商品，price引用item，包含不同年份的商品价格:

CREATE TABLE item(
id INTEGER PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE price(
id INTEGER PRIMARY KEY,
item_id INTEGER NOT NULL,
year INTEGER NOT NULL,
value INTEGER NOT NULL,
CONSTRAINT goods_fk FOREIGN KEY (item_id) REFERENCES item(id)
);

表item有2个记录(电视机和录像机物品),和表price有3个记录,电视机的价格在2000年和2010年和2000年价格录像机只:

INSERT INTO item(id, name)
VALUES
(1, 'TV set'),
(2, 'VCR');
INSERT INTO price(id, item_id, year, value)
VALUES
(1, 1, 2000, 290),
(2, 1, 2010, 270),
(3, 2, 2000, 770);
-- no price of VCR for 2010

现在让我们做一个LEFT OUTER JOIN查询，获取2010年所有商品的价格:

SELECT
i.*,
p.year,
p.value
FROM item i
LEFT OUTER JOIN price p ON i.id = p.item_id
WHERE p.year = 2010 OR p.year IS NULL;

由于某种原因，该查询将只返回电视机的结果，该电视机有今年的价格。结果中没有VCR记录:

id |  name  | year | value 
----+--------+------+-------
1 | TV set | 2010 |   270
(1 row)

经过一些实验，我找到了一种方法来使查询返回我需要的结果(item表的所有记录，在连接表的字段中为空，以防有一年没有任何记录。它是通过将年份过滤移动到JOIN条件中实现的:

SELECT
i.*,
p.year,
p.value
FROM item i
LEFT OUTER JOIN (
SELECT * FROM price
WHERE year = 2010 -- <= here I filter a year
) p ON i.id = p.item_id;

现在的结果是:

id |  name  | year | value 
----+--------+------+-------
1 | TV set | 2010 |   270
2 | VCR    |      |      
(2 rows)

我的主要问题是-为什么第一个查询(在WHERE中使用年份过滤)不像预期的那样工作，而是变成像INNER JOIN一样的东西?

在我目前的项目中，这个问题严重阻碍了我，所以我也会感谢下一个相关问题的提示/提示:

1. 是否有其他选择来达到适当的结果?
2. …特别是-容易翻译成Django的ORM查询集?

Upd: @astentx建议将过滤条件直接移动到JOIN中(它也有效):

SELECT
i.*,
p.year,
p.value
FROM item i
LEFT OUTER JOIN price p 
ON  
i.id = p.item_id 
AND p.year = 2010;

虽然，和我的第一个解决方案一样，我不知道如何用Django ORM查询集来表达它。还有其他建议吗?

Answer 1

第一个查询没有按预期工作，因为预期是错误的。它不像INNER JOIN那样工作。只有当VCR没有价格时，查询才返回VCR的记录。

Answer 2

SELECT
i.*,
y.year,
p.value
FROM item i
CROSS JOIN (SELECT 2010 AS year) y -- here could be a table
LEFT OUTER JOIN price p 
ON (p.item_id = i.id
AND p.year = y.year);