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

пояснение: показать строки таблицы 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

пояснение: показать строки таблиц 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-ов:

Источники: 1

---