了解LEFT JOIN中匹配的行数

问题描述

这是我的表结构: SQL小提琴

CREATE TABLE mytable (
    id int,
    related int
);

INSERT into mytable VALUES(1, NULL);
INSERT into mytable VALUES(2, 1);
INSERT into mytable VALUES(3, 1);

我有两个查询:

-- returns 3 rows
SELECT t1.id, t2.id
FROM mytable as t1
LEFT JOIN mytable as t2 ON t1.related = t2.id;

-- returns 4 rows
SELECT t1.id, t2.id
FROM mytable as t1
LEFT JOIN mytable as t2 ON t1.id = t2.related;

这些查询几乎相似，这就是一个self-join.但是正如您在小提琴中看到的那样，第一个查询返回3行，第二个查询返回4行.那不是我所期望的.我认为结果应该完全一样.

Those queries are almost similar and that's a self-join. But as you can see in the fiddle, the first query returns 3 rows and the second one returns 4 rows. That not what I've expected. I think the result should be exactly the same.

您能解释一下LEFT JOIN在后台如何工作吗?

Can you please explain me how LEFT JOIN works in the background?

推荐答案

第一个查询:t1.related = t2.id

t1              joined t2
id   related  | id   related
--------------+-------------
1    NULL     | --   --
2    1        | 1    NULL
3    1        | 1    NULL

内部联接只会导致两行，但是外部联接还会保留第一行，但不匹配.

An inner join would result in only two rows, but the outer join also preserves the first row that has no match.

第二个查询:t1.id = t2.related

t1              joined t2
id   related  | id   related
--------------+-------------
1    NULL     | 2    1
1    NULL     | 3    1
2    1        | --   --
3    1        | --   --

这里，内部联接也只会导致两行，但是外部联接也会保留没有匹配项的两行.

Here too, an inner join would result in only two rows, but the outer join also preserves the two rows that have no match.

这篇关于了解LEFT JOIN中匹配的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！