MySQL отличие LEFT от INNER

Очень подробно про джоины написано тут

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

Но для начала быстрая напоминалка о LEFT и INNER джоинах.

Допустим есть 2 таблицы alpha и beta. 

alpha

id nameA
1 mama
2 papa
3 ya

beta

id nameB
2 Pirate
3 Monkey

CREATE TABLE alpha (
id int(11) DEFAULT NULL,
nameA varchar(50) DEFAULT NULL );
INSERT INTO alpha(id, nameA) VALUES (1, 'mama'), (2, 'papa'), (3, 'ya');

CREATE TABLE beta (
id int(11) DEFAULT NULL,
nameB varchar(50) DEFAULT NULL );
INSERT INTO beta(id, nameB) VALUES (2, 'Pirate'), (3, 'Monkey');


Запрос c LEFT JOIN вернет все 3 строки из таблицы alpha, объединив их по id с таблицей beta, а недостающие значения заполнит NULL:

SELECT * FROM alpha LEFT JOIN beta USING (id) или SELECT * FROM alpha LEFT JOIN beta ON beta.id = alpha.id


id nameA nameB
1 mama NULL
2 papa Pirate
3 ya Monkey

     mysql left join

   


пояснение: показать строки таблицы alpha
и подходящие этим строкам данные


Запрос c INNER JOIN вернет 2 строки c id которые есть в обеих таблицах:

SELECT * FROM alpha INNER JOIN beta ON beta.id = alpha.id

 

id nameA nameB
2 papa Pirate
3 ya Monkey

     mysql inner join

   


пояснение: показать строки таблиц alpha и beta
по условию совпадению данных в этих строках


Теперь добавим еще одну строку с уже существующим id:

3 - INSERT INTO beta (id, nameB) VALUES (3, 'Ship');

INNER JOIN:

id nameA nameB
2 papa Pirate
3 ya Monkey
3 ya Ship

   

LEFT JOIN:

id nameA nameB
1 mama NULL
2 papa Pirate
3 ya Monkey
3 ya Ship

   

 

 

Как мы видим данные из таблицы alfa появились в третьей строке, лично мое мнение - третья строка не должна появляться при INNER JOIN, такое должно быть только при FULL OUTER JOIN.

Если мы сделаем INNER JOIN еще одной таблицы, то чтобы понять что произойдет, просто представьте, что у Вас уже есть таблица из двух таблиц и Вы просто добавляете к ней еще одну таблицу.

Например таблица

gamma

id nameG
1 lazer

CREATE TABLE gamma (
id int(11) DEFAULT NULL,
nameG varchar(50) DEFAULT NULL );
INSERT INTO gamma(id, nameG) VALUES (1, 'lazer');

В итоге, если мы сделаем запрос:

SELECT * 
FROM alpha 
INNER JOIN beta ON beta.id = alpha.id
INNER JOIN gamma ON gamma.id = beta.id

То в ответе получим ноль строк, потому что:

  1. в предыдущем запросе мы получили beta.id равные 2 и 3
  2. а в этом запросе говорим MySQL-ю: покажи нам строки в которых gamma.id = beta.id т.е. gamma.id = 2 или gamma.id = 3
  3. но ведь в таблице gamma нет строк с id = 2 или 3

Различие указания вариантов условий при LEFT JOIN

Создадим еще одну таблицу omega:

id z nameO
1 1 N1
2 1 N2
3 2 N3
    

CREATE TABLE omega (
id int(11) DEFAULT NULL,
z int(11) DEFAULT NULL,
nameO varchar(50) DEFAULT NULL );

   

INSERT INTO omega(id, z, nameO) VALUES
(1, 1, 'N1'),(2, 1, 'N2'),(3, 2, 'N3');

А теперь выполним 2 казалось бы одинаковых запроса:

SELECT * FROM alpha
LEFT JOIN omega ON omega.id = alpha.id
WHERE omega.z = 1

SELECT * FROM alpha
LEFT JOIN omega ON omega.id = alpha.id
AND omega.z = 1

id nameA id1 z nameO
1 mama 1 1 N1
2 papa 2 1 N2

 

id nameA id1 z nameO
1 mama 1 1 N1
2 papa 2 1 N2
3 ya null null null
WHERE указывает какие строки показать
(срабатывает после JOIN-a)
AND указывает какие строки присоединить
(срабатывает во время JOIN-a)

Как видите, условия вроде идентичны, но результат получается совершенно разным.

p.s. имейте ввиду, что в этой статье столбец id уникален в рамках своей таблицы. Т.е. если в Ваших таблицах столбец id не уникален, то результаты запросов будут совершенно иными.

JOIN-соединения

Возможно ниже изложенное - тема отдельной статьи, но хочется поделиться информацией о всех операциях горизонтального соединения данных.

Есть пять типов соединения:

  1. JOIN – левая_таблица JOIN правая_таблица ON условия_соединения
  2. LEFT JOIN – левая_таблица LEFT JOIN правая_таблица ON условия_соединения
  3. RIGHT JOIN – левая_таблица RIGHT JOIN правая_таблица ON условия_соединения
  4. FULL JOIN – левая_таблица FULL JOIN правая_таблица ON условия_соединения
  5. CROSS JOIN – левая_таблица CROSS JOIN правая_таблица
Краткий синтаксисПолный синтаксисОписание (Это не всегда всем сразу понятно. Так что, если не понятно, то просто вернитесь сюда после рассмотрения примеров.)
JOIN INNER JOIN Из строк левой_таблицы и правой_таблицы объединяются и возвращаются только те строки, по которым выполняются условия_соединения.
LEFT JOIN LEFT OUTER JOIN Возвращаются все строки левой_таблицы (ключевое слово LEFT). Данными правой_таблицы дополняются только те строки левой_таблицы, для которых выполняются условия_соединения. Для недостающих данных вместо строк правой_таблицы вставляются NULL-значения.
RIGHT JOIN RIGHT OUTER JOIN Возвращаются все строки правой_таблицы (ключевое слово RIGHT). Данными левой_таблицы дополняются только те строки правой_таблицы, для которых выполняются условия_соединения. Для недостающих данных вместо строк левой_таблицы вставляются NULL-значения.
FULL JOIN FULL OUTER JOIN Возвращаются все строки левой_таблицы и правой_таблицы. Если для строк левой_таблицы и правой_таблицы выполняются условия_соединения, то они объединяются в одну строку. Для строк, для которых не выполняются условия_соединения, NULL-значения вставляются на место левой_таблицы, либо на место правой_таблицы, в зависимости от того данных какой таблицы в строке не имеется.
CROSS JOIN - Объединение каждой строки левой_таблицы со всеми строками правой_таблицы. Этот вид соединения иногда называют декартовым произведением.

Как видно из таблицы полный синтаксис от краткого отличается только наличием слов INNER или OUTER.

Лично я всегда при написании запросов использую только краткий синтаксис, по той причине:

  1. Это короче и не засоряет запрос лишними словами;
  2. По словам LEFT, RIGHT, FULL и CROSS и так понятно о каком соединении идет речь, так же и в случае просто JOIN;
  3. Считаю слова INNER и OUTER в данном случае ненужными рудиментами, которые больше путают начинающих.

А на последок, диаграмма, поясняющая работу JOIN-ов:

MySQL отличие LEFT от INNER

Источники: 1

Оцени публикацию:
  • 9,43
Оценили: 9


Предложения и пожелания:

 

youtube.com/watch?v=7hFivbgIEqk

При полном или частичном использовании материалов данного сайта, ссылка на сайт "yapro.ru" обязательна как на источник информации.
Автоматический импорт материалов и информации с сайта запрещен.
Copyrights © 2007 - 2019 YaPro.Ru

Лебеденко Николай Николаевич
Ошибка в тексте? Выделите её мышкой и нажмите: Ctrl + Enter