• left join: LEFT JOIN возвращает все записи из левой таблицы и записи из правой таблицы, где поля соединения равны.
  • right join: RIGHT JOIN возвращает все записи из правой таблицы и записи из левой таблицы, где поля соединения равны.
  • inner join: INNER JOIN, также называемый соединением по равенству, возвращает только строки, в которых поля соединения в обеих таблицах равны.
  • full join: OUTER JOIN, возвращает строки из обеих таблиц: LEFT JOIN + RIGHT JOIN.
  • cross join: Результат — декартово произведение, то есть количество строк в первой таблице, умноженное на количество строк во второй таблице.

Ключевое слово ON

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

При использовании [LEFT JOIN]left join,onиwhereразница между условиями следующая:

  • 1、 onУсловие [ON] — это условие, используемое при создании временной таблицы. Независимо отonтого, истинно ли условие в [ON], записи из левой таблицы будут возвращены.
  • 2、whereУсловие [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-запроса:

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'

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того, истинно ли условие в [ON], он вернётleftилиrightзаписи из таблицы [LEFT],full[FULL JOIN] имеетleftиrightобъединение особенностей [LEFT JOIN и RIGHT JOIN]. Однако,inner jion[WHERE] не обладает этой особенностью, поэтому независимо от того, размещено ли условие вon[ON] или вwhere[WHERE], возвращаемые наборы результатов одинаковы.

Адрес оригинальной статьи: https://www.cnblogs.com/wlzhang/p/4532587.html