“on .."和“on .."之间的区别和“在..哪里"在 SQL 左连接中? [英] Difference between "on .. and" and "on .. where" in SQL Left Join?

问题描述

Sql 语句.

1.select a.* from A a left join B b on a.id =b.id and a.id=2;

2.select a.* from A a left join B b on a.id =b.id where a.id=2;

这两条sql语句有什么区别?

what is the difference of this two sql statement?

推荐答案

create table A(id int);
create table B(id int);

INSERT INTO A VALUES(1);
INSERT INTO A VALUES(2);
INSERT INTO A VALUES(3);

INSERT INTO B VALUES(1);
INSERT INTO B VALUES(2);
INSERT INTO B VALUES(3);

SELECT * FROM A;
SELECT * FROM B;

id
-----------
1
2
3

id
-----------
1
2
3

对 JOIN 进行过滤以防止在 JOIN 过程中添加行.

Filter on the JOIN to prevent rows from being added during the JOIN process.

select a.*,b.*
from   A a left join B b 
on     a.id =b.id and a.id=2;

id          id
----------- -----------
1           NULL
2           2
3           NULL

WHERE 将在 JOIN 发生后进行过滤.

WHERE will filter after the JOIN has occurred.

select a.*,b.* 
from   A a left join B b 
on     a.id =b.id 
where  a.id=2;

id          id
----------- -----------
2           2

这篇关于“on .."和“on .."之间的区别和“在..哪里"在 SQL 左连接中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！