筛选器如何影响sql中的左联接

u5i3ibmn 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(348)

这个问题在这里已经有答案了：

左外联接不返回左表中的所有行(3个答案）
11个月前关门了。
我试图理解为什么下面的查询只返回一行，尽管 LEFT JOIN ```
with t1(day_partition, entity_id, feature_1) AS (values
('2020-05-15', 'id_1', 'x'),
('2020-05-15', 'id_2', 'y')
),

t2(day_partition, entity_id, feature_2) AS (values
('2020-05-15', 'id_1', 1)
)

SELECT
t1.day_partition AS day_partition_1,
t2.day_partition AS day_partition_2,
t1.entity_id AS entity_id_1,
t2.entity_id AS entity_id_2
FROM
t1
LEFT JOIN
t2
ON
t1.entity_id = t2.entity_id
WHERE
t2.day_partition = '2020-05-15'
;

退货

day_partition_1 | day_partition_2 | entity_id_1 | entity_id_2
-----------------+-----------------+-------------+-------------
2020-05-15 | 2020-05-15 | o1 | o1

但是，卸下

WHERE
t2.day_partition = '2020-05-15'

会回来的

我觉得这样的行为不直观，背后有什么规律？

sql Join left-join

来源：https://stackoverflow.com/questions/62078623/how-does-filter-affect-left-join-in-sql

2条答案

按热度按时间

dl5txlt91#

这是故意的。环境条件 where 条款是强制性的，所以在 left join ed表最终逐出 left join 回来时空无一人。基本上，这会让 left join 到一个 inner join .
您需要放置所有与来自 left join 房间里的一张table on join子句：

FROM t1
LEFT JOIN t2
    ON t1.entity_id = t2.entity_id
    AND t2.day_partition = '2020-05-15'

通过查看你的结果集，我倾向于认为你实际上想要一个条件 t1 :

FROM t1
LEFT JOIN t2
    ON t1.entity_id = t2.entity_id
WHERE t1.day_partition = '2020-05-15'

赞(0）回复(0）举报 2021-08-13

ghhaqwfi2#

如果你把条件放在 join 正如你所料

SELECT
  t1.day_partition AS day_partition_1,
  t2.day_partition AS day_partition_2,
  t1.entity_id AS entity_id_1,
  t2.entity_id AS entity_id_2
FROM
  t1
LEFT JOIN
  t2
ON
  t1.entity_id = t2.entity_id AND t2.day_partition = '2020-05-15'

查询解析器不知道您在想什么。如果你在一个 where 子句它影响所有记录，而不仅仅是联接表的记录。

赞(0）回复(0）举报 2021-08-13

我来回答

筛选器如何影响sql中的左联接

2条答案

相关问题

热门标签

最新问答