Sql statement.
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;
what is the difference of this two sql statement?
这两个sql语句有什么区别？
5 个解决方案
#1
23
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
Filter on the JOIN to prevent rows from being added during the JOIN process.
过滤JOIN以防止在JOIN过程中添加行。
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 will filter after the JOIN has occurred.
在JOIN发生后，WHERE将进行过滤。
select a.*,b.*
from A a left join B b
on a.id =b.id
where a.id=2;
id id
----------- -----------
2 2
#2
8
select a.* from A a left join B b on a.id =b.id and a.id=2;
从a.id = b.id和a.id = 2的A左边连接B b中选择a。*;
This only uses a.id in the join condition, so records where a.id <> 2 don't get filtered out. You might get a result like this:
这仅在连接条件中使用a.id，因此不会过滤掉a.id <> 2的记录。你可能会得到这样的结果：
+------+------+ | a.id | b.id | +------+------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +------+------+
You don't select any of b's columns, but if you do, it'll be easier to understand.
你没有选择任何b的列，但如果你这样做，它会更容易理解。
select a.* from A a left join B b on a.id =b.id where a.id=2;
从a.id = b.id中的左连接B b中选择a。*，其中a.id = 2;
Now records where a.id <> 2 do get filtered out.
现在记录过滤出a.id <> 2的位置。
+------+------+ | a.id | b.id | +------+------+ | 2 | 2 | +------+------+
#3
0
As @hvd says, the "where" clause filters rows returned by the join, so the "where" version won't return outer-joined rows (which have a.id = null).
正如@hvd所说，“where”子句过滤连接返回的行，因此“where”版本不会返回外连接行（具有a.id = null）。
However there is another significant difference: Even if the outer joined rows were not filtered out, there can be a massive performance boost putting the condition into the "on" clause, because the result set is made smaller earlier.
然而，还有另一个显着的区别：即使外部连接的行没有被过滤掉，也可以通过将条件置于“on”子句中进行大量的性能提升，因为结果集会更早地变小。
This is particularly pronounced when a series of other left joined tables follows the one with the "and" condition - you can prevent joins from even happening to the following tables for unsuitable rows and potentially chop off millions of rows from reaching the filtering ("where") stage.
当一系列其他左连接表跟随具有“和”条件的表时，这尤其明显 - 您可以防止连接发生到下面的表中，以获得不合适的行，并且可能会切断数百万行到达过滤（“ “）阶段。
#4
0
I try some time ,and I know what is the reason, it only related to a priority.
我试了一下，我知道是什么原因，它只与优先权有关。
select * from A a left join B b on a.id=b.id and b.id=2
this means A left join (where b.id=2) this is the condition filter B first
这意味着左连接（其中b.id = 2）这首先是条件过滤器B.
Select * from A a left join B b on a.id=b.id where a.id=2
this means after join B ,then filter by a.id=2
这意味着在加入B之后，然后按a.id = 2过滤
#5
0
If you think about the syntax of a SQL query, the 'AND' extends the join block (as if where parenthesis) where as the 'WHERE' defines the start of the WHERE/filtering block of the query.
如果考虑SQL查询的语法，'AND'会扩展连接块（就像括号一样），其中'WHERE'定义查询的WHERE /过滤块的开头。
#1
23
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
Filter on the JOIN to prevent rows from being added during the JOIN process.
过滤JOIN以防止在JOIN过程中添加行。
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 will filter after the JOIN has occurred.
在JOIN发生后，WHERE将进行过滤。
select a.*,b.*
from A a left join B b
on a.id =b.id
where a.id=2;
id id
----------- -----------
2 2
#2
8
select a.* from A a left join B b on a.id =b.id and a.id=2;
从a.id = b.id和a.id = 2的A左边连接B b中选择a。*;
This only uses a.id in the join condition, so records where a.id <> 2 don't get filtered out. You might get a result like this:
这仅在连接条件中使用a.id，因此不会过滤掉a.id <> 2的记录。你可能会得到这样的结果：
+------+------+ | a.id | b.id | +------+------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +------+------+
You don't select any of b's columns, but if you do, it'll be easier to understand.
你没有选择任何b的列，但如果你这样做，它会更容易理解。
select a.* from A a left join B b on a.id =b.id where a.id=2;
从a.id = b.id中的左连接B b中选择a。*，其中a.id = 2;
Now records where a.id <> 2 do get filtered out.
现在记录过滤出a.id <> 2的位置。
+------+------+ | a.id | b.id | +------+------+ | 2 | 2 | +------+------+
#3
0
As @hvd says, the "where" clause filters rows returned by the join, so the "where" version won't return outer-joined rows (which have a.id = null).
正如@hvd所说，“where”子句过滤连接返回的行，因此“where”版本不会返回外连接行（具有a.id = null）。
However there is another significant difference: Even if the outer joined rows were not filtered out, there can be a massive performance boost putting the condition into the "on" clause, because the result set is made smaller earlier.
然而，还有另一个显着的区别：即使外部连接的行没有被过滤掉，也可以通过将条件置于“on”子句中进行大量的性能提升，因为结果集会更早地变小。
This is particularly pronounced when a series of other left joined tables follows the one with the "and" condition - you can prevent joins from even happening to the following tables for unsuitable rows and potentially chop off millions of rows from reaching the filtering ("where") stage.
当一系列其他左连接表跟随具有“和”条件的表时，这尤其明显 - 您可以防止连接发生到下面的表中，以获得不合适的行，并且可能会切断数百万行到达过滤（“ “）阶段。
#4
0
I try some time ,and I know what is the reason, it only related to a priority.
我试了一下，我知道是什么原因，它只与优先权有关。
select * from A a left join B b on a.id=b.id and b.id=2
this means A left join (where b.id=2) this is the condition filter B first
这意味着左连接（其中b.id = 2）这首先是条件过滤器B.
Select * from A a left join B b on a.id=b.id where a.id=2
this means after join B ,then filter by a.id=2
这意味着在加入B之后，然后按a.id = 2过滤
#5
0
If you think about the syntax of a SQL query, the 'AND' extends the join block (as if where parenthesis) where as the 'WHERE' defines the start of the WHERE/filtering block of the query.
如果考虑SQL查询的语法，'AND'会扩展连接块（就像括号一样），其中'WHERE'定义查询的WHERE /过滤块的开头。