Когда база данных возвращает записи, объединяя две или более таблиц, она всегда создаёт промежуточную временную таблицу, а затем возвращает эту временную таблицу пользователю.

При использованииleft joinкогда,onиwhereРазница между условиями следующая:

1, onУсловие ON используется при создании временной таблицы; оно не проверяетonистинно ли условие в ON, оно вернёт записи из левой таблицы.

2. Условие WHERE — это условие фильтрации временной таблицы после её создания. На этом этапе уже нетleft joinсмысла LEFT JOIN (обязательно возвращать записи из левой таблицы); все записи, для которых условие не истинно, отфильтровываются.

Предположим, есть две таблицы:

Таблица 1: tab1

id size
1  10
2  20
3  30

Таблица 2: tab2

size name
10   AAA
20   BBB
20   CCC

Два SQL-запроса:

1、select * from tab1 left join tab2 on tab1.size = tab2.size where tab2.name='AAA'
2、select * from tab1 left join tab2 on tab1.size = tab2.size and tab2.name='AAA'

Процесс выполнения первого SQL-запроса:

1. Промежуточная таблица

условие on:

tab1.size = tab2.size
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 (null) (null)

2. Затем отфильтровать промежуточную таблицу

условие where:

tab2.name='AAA'

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA

Процесс второго SQL-запроса:

1. Промежуточная таблица

условие on:

tab1.size = tab2.size and tab2.name='AAA'
(条件不为真也会返回左表中的记录) tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)

На самом деле ключевая причина приведённых выше результатов — этоleft join,right join,full joinособенность LEFT JOIN.

Независимо от того, истинно ли условие в ON, будут возвращены записи из таблицы LEFT или RIGHT; FULL обладает объединением характеристик LEFT и RIGHT.

Но INNER JOIN не обладает этой особенностью; независимо от того, размещено ли условие в ON или WHERE, возвращаемый результирующий набор одинаков.