Разница между "на.. и" и "на.. где" в SQL Left Join?

Ответ 1

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.

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.

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

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

Ответ 2

выберите a. * из A левое соединение B b на a.id = b.id и a.id = 2;

В условии соединения используется только a.id, поэтому записи, в которых a.id <> 2 не отфильтровываются. Вы можете получить такой результат:

+------+------+
| a.id | b.id |
+------+------+
| 1    | NULL |
| 2    | 2    |
| 3    | NULL |
+------+------+

Вы не выбираете ни один из столбцов b, но если вы это сделаете, это будет легче понять.

выберите a. * из A левое соединение B b на a.id = b.id, где a.id = 2;

Теперь записи, в которых a.id <> 2 отфильтровываются.

+------+------+
| a.id | b.id |
+------+------+
| 2    | 2    |
+------+------+

Ответ 3

Как @hvd говорит, что предложение where, которое фильтрует строки, возвращаемые соединением, поэтому версия "где" не будет возвращать внешние строки (которые имеют a.id = null).

Однако существует и другая существенная разница: даже если внешние объединенные строки не были отфильтрованы, может произойти значительное повышение производительности, приведя условие в предложение "on", потому что набор результатов делается меньше раньше.

Это особенно заметно, когда серия других левых присоединительных таблиц следует за тем, у кого есть условие "и", - вы можете предотвратить объединение даже из следующих таблиц для неподходящих строк и потенциально отрубить миллионы строк от фильтрации ( "where" ).

Ответ 4

Я пробую какое-то время, и я знаю, в чем причина, это связано только с приоритетом.

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

это означает, что левое соединение (где b.id = 2) - это условие  сначала фильтр B

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

это означает, что после объединения B, затем фильтруйте a.id = 2

Ответ 5

Если вы думаете о синтаксисе SQL-запроса, "AND" расширяет блок join (как если бы там была скобка), где "WHERE" определяет начало блока WHERE/filtering запроса.