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

u5i3ibmn  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(446)

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

左外联接不返回左表中的所有行(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'

会回来的

day_partition_1 | day_partition_2 | entity_id_1 | entity_id_2
-----------------+-----------------+-------------+-------------
2020-05-15 | 2020-05-15 | id_1 | id_1
2020-05-15 | NULL | id_2 | NULL

我觉得这样的行为不直观,背后有什么规律?
dl5txlt9

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'
ghhaqwfi

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 子句它影响所有记录,而不仅仅是联接表的记录。

相关问题