Left join inner join в чем разница

Какая разница между правым и левым JOIN’ом?

Этот вопрос любят задавать на собеседованиях на позицию джуниора в IT-компаниях. Это, если хотите, классика жанра. Что же, давайте разбираться, в чём разница между SQL-запросами RIGHT и LEFT JOIN. Заодно, вспомним и запрос INNER JOIN.

Примечание: в статье описываются базовые случаи для общего понимания. В зависимости от конкретной базы данных возможны нюансы.

Внутреннее соединение INNER JOIN

С помощью этого запроса вы возвратите все записи из таблиц table_1 и table_2, которые связаны с помощью первичного (primary) и внешнего (foreign) ключей, а также отвечающие условию WHERE для таблицы table_1.

Если в какой-нибудь из вышеописанных таблиц отсутствует запись, которая соответствует соседней, эта пара не будет включена в общую выдачу. Таким образом, мы получим лишь те записи, которые существует как в первой, так и во второй таблицах. По сути, выборка осуществляется по наличию связи (ключу), то есть выдаются лишь записи, связанные между собой. Если у вас есть «одинокие» записи (записи без пары), то они выданы не будут.

 
SELECT * FROM table_1 INNER JOIN table_2 ON table_1.primary_key = table_2.foreign_key WHERE table_1.column_1 = ‘value’
Внешнее соединение LEFT JOIN
С помощью этого запроса вы вернёте все данные из «левой» таблицы даже в том случае, если не будет найдено соответствий в «правой» таблице. Подразумевается, что «левая» таблица в запросе находится левее знака равно, а «правая», соответственно, правее (стандартная логика правой и левой руки).
Говоря иначе, когда мы присоединяем «правую» таблицу к «левой», происходит выборка всех записей согласно условиям WHERE для «левой» таблицы. Если в «правой» таблице у нас отсутствуют соответствующие записи по ключам, они вернутся как NULL. В результате главной выступает именно «левая» таблица, и именно относительно неё осуществляется выдача. При этом в условии ON «левая» таблица прописывается первой по порядку (table_1), а «правая» – второй (table_2):
 
SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.primary_key = table_2.foreign_key WHERE table_1.column_1 = ‘value’
Внешнее соединение RIGHT JOIN
Используя этот запрос, вы вернёте все данные из «правой» таблицы даже в том случае, если не будут найдены соответствия в «левой» таблице. То есть всё происходит по аналогии с LEFT JOIN, однако NULL вернётся для полей «левой» таблицы. Иными словами, главной выступает именно правая «таблица» и выдача осуществляется относительно неё. Также обратите внимание на WHERE, т. к. условие выборки теперь затрагивает «правую» таблицу:
 
SELECT * FROM table_1 RIGHT JOIN table_2 ON table_1.primary_key = table_2.foreign_key WHERE table_2.column_1 = ‘value’
В двух словах:

LEFT JOIN — это абсолютно всё из левой таблицы, плюс то, что нашлось в правой (то, что удовлетворяет выражению ON). Если не нашлось в правой, то напротив записи из левой будет NULL;
RIGHT JOIN — наоборот;
INNER JOIN — только те записи из левой и правой таблиц, которые удовлетворяют выражению ON (в обеих таблицах NULL недопустим);
FULL JOIN — всё вместе.

Совет
Лучше всего разбираться с этими запросами на практике. Для этого создайте соответствующую базу данных и таблицы в ней. Это не займёт много времени. К примеру, вы можете попрактиковаться с помощью этого видеоурока. Только «потрогав» всё руками, вы действительно поймёте, какова разница между правым и левым JOIN’ом.
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

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

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



Как видите, условия вроде идентичны, но результат получается совершенно разным.
p.s. имейте ввиду, что в этой статье столбец id уникален в рамках своей таблицы. Т.е. если в Ваших таблицах столбец id не уникален, то результаты запросов будут совершенно иными.
JOIN-соединения
Возможно ниже изложенное - тема отдельной статьи, но хочется поделиться информацией о всех операциях горизонтального соединения данных.
Есть пять типов соединения:

JOIN – левая_таблица JOIN правая_таблица ON условия_соединения
LEFT JOIN – левая_таблица LEFT JOIN правая_таблица ON условия_соединения
RIGHT JOIN – левая_таблица RIGHT JOIN правая_таблица ON условия_соединения
FULL JOIN – левая_таблица FULL JOIN правая_таблица ON условия_соединения
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.
Лично я всегда при написании запросов использую только краткий синтаксис, по той причине:

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

А на последок, диаграмма, поясняющая работу JOIN-ов:
Секция JOIN
JOIN создаёт новую таблицу путем объединения столбцов из одной или нескольких таблиц с использованием общих для каждой из них значений. Это обычная операция в базах данных с поддержкой SQL, которая соответствует join из реляционной алгебры. Частный случай соединения одной таблицы часто называют self-join.
Синтаксис
SELECT expr_list> FROM left_table> [GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN right_table> (ON expr_list>)|(USING column_list>) ... 
Выражения из секции ON и столбцы из секции USING называются «ключами соединения». Если не указано иное, при присоединение создаётся Декартово произведение из строк с совпадающими значениями ключей соединения, что может привести к получению результатов с гораздо большим количеством строк, чем исходные таблицы.
Поддерживаемые типы соединения​
Все типы из стандартного SQL JOIN поддерживаются:

INNER JOIN , возвращаются только совпадающие строки.
LEFT OUTER JOIN , не совпадающие строки из левой таблицы возвращаются в дополнение к совпадающим строкам.
RIGHT OUTER JOIN , не совпадающие строки из правой таблицы возвращаются в дополнение к совпадающим строкам.
FULL OUTER JOIN , не совпадающие строки из обеих таблиц возвращаются в дополнение к совпадающим строкам.
CROSS JOIN , производит декартово произведение таблиц целиком, ключи соединения не указываются.

Без указания типа JOIN подразумевается INNER . Ключевое слово OUTER можно опускать. Альтернативным синтаксисом для CROSS JOIN является указание нескольких таблиц, разделённых запятыми, в секции FROM.
Дополнительные типы соединений, доступные в ClickHouse:

LEFT SEMI JOIN и RIGHT SEMI JOIN , белый список по ключам соединения, не производит декартово произведение.
LEFT ANTI JOIN и RIGHT ANTI JOIN , черный список по ключам соединения, не производит декартово произведение.
LEFT ANY JOIN , RIGHT ANY JOIN и INNER ANY JOIN , Частично (для противоположных сторон LEFT и RIGHT ) или полностью (для INNER и FULL ) отключает декартово произведение для стандартных видов JOIN .
ASOF JOIN и LEFT ASOF JOIN , Для соединения последовательностей по нечеткому совпадению. Использование ASOF JOIN описано ниже.

Примечание
Если настройка join_algorithm установлена в значение partial_merge , то для RIGHT JOIN и FULL JOIN поддерживается только уровень строгости ALL ( SEMI , ANTI , ANY и ASOF не поддерживаются).
Настройки​
Значение строгости по умолчанию может быть переопределено с помощью настройки join_default_strictness.
Поведение сервера ClickHouse для операций ANY JOIN зависит от параметра any_join_distinct_right_table_keys.
См. также

join_algorithm
join_any_take_last_row
join_use_nulls
partial_merge_join_optimizations
partial_merge_join_rows_in_right_blocks
join_on_disk_max_files_to_merge
any_join_distinct_right_table_keys

Условия в секции ON​
Секция ON может содержать несколько условий, связанных операторами AND и OR . Условия, задающие ключи соединения, должны содержать столбцы левой и правой таблицы и должны использовать оператор равенства. Прочие условия могут использовать другие логические операторы, но в отдельном условии могут использоваться столбцы либо только левой, либо только правой таблицы.
Строки объединяются только тогда, когда всё составное условие выполнено. Если оно не выполнено, то строки могут попасть в результат в зависимости от типа JOIN . Обратите внимание, что если то же самое условие поместить в секцию WHERE , то строки, для которых оно не выполняется, никогда не попаду в результат.
Оператор OR внутри секции ON работает, используя алгоритм хеш-соединения — на каждый аргумент OR с ключами соединений для JOIN создается отдельная хеш-таблица, поэтому потребление памяти и время выполнения запроса растет линейно при увеличении количества выражений OR секции ON .
Примечание
Если в условии использованы столбцы из разных таблиц, то пока поддерживается только оператор равенства ( = ).
Пример
Рассмотрим table_1 и table_2 :
┌─Id─┬─name─┐ ┌─Id─┬─text───────────┬─scores─┐ │ 1 │ A │ │ 1 │ Text A │ 10 │ │ 2 │ B │ │ 1 │ Another text A │ 12 │ │ 3 │ C │ │ 2 │ Text B │ 15 │ └────┴──────┘ └────┴────────────────┴────────┘ 
Запрос с одним условием, задающим ключ соединения, и дополнительным условием для table_2 :
SELECT name, text FROM table_1 LEFT OUTER JOIN table_2 ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text'); 
Обратите внимание, что результат содержит строку с именем C и пустым текстом. Строка включена в результат, потому что использован тип соединения OUTER .
┌─name─┬─text───┐ │ A │ Text A │ │ B │ Text B │ │ C │ │ └──────┴────────┘ 
Запрос с типом соединения INNER и несколькими условиями:
SELECT name, text, scores FROM table_1 INNER JOIN table_2 ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text'); 
┌─name─┬─text───┬─scores─┐ │ B │ Text B │ 15 │ └──────┴────────┴────────┘ 
Запрос с типом соединения INNER и условием с оператором OR :
CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;  CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;  INSERT INTO t1 SELECT number as a, -a as b from numbers(5);  INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);  SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key; 
┌─a─┬──b─┬─val─┐ │ 0 │ 0 │ 0 │ │ 1 │ -1 │ 1 │ │ 2 │ -2 │ 2 │ │ 3 │ -3 │ 3 │ │ 4 │ -4 │ 4 │ └───┴────┴─────┘ 
Запрос с типом соединения INNER и условиями с операторами OR и AND :
SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3; 
┌─a─┬──b─┬─val─┐ │ 0 │ 0 │ 0 │ │ 2 │ -2 │ 2 │ │ 4 │ -4 │ 4 │ └───┴────┴─────┘ 
Использование ASOF JOIN​
ASOF JOIN применим в том случае, когда необходимо объединять записи, которые не имеют точного совпадения.
Для работы алгоритма необходим специальный столбец в таблицах. Этот столбец:

Должен содержать упорядоченную последовательность.
Может быть одного из следующих типов: Int, UInt, Float, Date, DateTime, Decimal.
Не может быть единственным столбцом в секции JOIN .

Синтаксис ASOF JOIN . ON :
SELECT expressions_list FROM table_1 ASOF LEFT JOIN table_2 ON equi_cond AND closest_match_cond 
Можно использовать произвольное количество условий равенства и одно условие на ближайшее совпадение. Например, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t 
Условия, поддержанные для проверки на ближайшее совпадение: > , >= , < , 


Синтаксис ASOF JOIN . USING :
SELECT expressions_list FROM table_1 ASOF JOIN table_2 USING (equi_column1, ... equi_columnN, asof_column) 
Для слияния по равенству ASOF JOIN использует equi_columnX , а для слияния по ближайшему совпадению использует asof_column с условием table_1.asof_column >= table_2.asof_column . Столбец asof_column должен быть последним в секции USING .
Например, рассмотрим следующие таблицы:
 table_1 table_2  event | ev_time | user_id event | ev_time | user_id ----------|---------|---------- ----------|---------|----------  . . event_1_1 | 12:00 | 42 event_2_1 | 11:59 | 42  . event_2_2 | 12:30 | 42 event_1_2 | 13:00 | 42 event_2_3 | 13:00 | 42  . . 
ASOF JOIN принимает метку времени пользовательского события из table_1 и находит такое событие в table_2 метка времени которого наиболее близка к метке времени события из table_1 в соответствии с условием на ближайшее совпадение. При этом столбец user_id используется для объединения по равенству, а столбец ev_time для объединения по ближайшему совпадению. В нашем примере event_1_1 может быть объединено с event_2_1 , event_1_2 может быть объединено с event_2_3 , а event_2_2 не объединяется.
Примечание
ASOF JOIN не поддержан для движка таблиц Join.
Чтобы задать значение строгости по умолчанию, используйте сессионный параметр join_default_strictness.
Распределённый JOIN​
Есть два пути для выполнения соединения с участием распределённых таблиц:

При использовании обычного JOIN , запрос отправляется на удалённые серверы. На каждом из них выполняются подзапросы для формирования «правой» таблицы, и с этой таблицей выполняется соединение. То есть, «правая» таблица формируется на каждом сервере отдельно.
При использовании GLOBAL . JOIN , сначала сервер-инициатор запроса запускает подзапрос для вычисления правой таблицы. Эта временная таблица передаётся на каждый удалённый сервер, и на них выполняются запросы с использованием переданных временных данных.

Будьте аккуратны при использовании GLOBAL . За дополнительной информацией обращайтесь в раздел Распределенные подзапросы.
Неявные преобразования типов​
Запросы INNER JOIN , LEFT JOIN , RIGHT JOIN и FULL JOIN поддерживают неявные преобразования типов для ключей соединения. Однако запрос не может быть выполнен, если не существует типа, к которому можно привести значения ключей с обеих сторон (например, нет типа, который бы одновременно вмещал в себя значения UInt64 и Int64 , или String и Int32 ).
Пример
Рассмотрим таблицу t_1 :
┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐ │ 1 │ 1 │ UInt16 │ UInt8 │ │ 2 │ 2 │ UInt16 │ UInt8 │ └───┴───┴───────────────┴───────────────┘ 
┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐ │ -1 │ 1 │ Int16 │ Nullable(Int64) │ │ 1 │ -1 │ Int16 │ Nullable(Int64) │ │ 1 │ 1 │ Int16 │ Nullable(Int64) │ └────┴──────┴───────────────┴─────────────────┘ 
SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b); 
┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐ │ 1 │ 1 │ Int32 │ Nullable(Int64) │ │ 2 │ 2 │ Int32 │ Nullable(Int64) │ │ -1 │ 1 │ Int32 │ Nullable(Int64) │ │ 1 │ -1 │ Int32 │ Nullable(Int64) │ └────┴──────┴───────────────┴─────────────────┘ 
Рекомендации по использованию​
Обработка пустых ячеек и NULL​
При соединении таблиц могут появляться пустые ячейки. Настройка join_use_nulls определяет, как ClickHouse заполняет эти ячейки.
Если ключами JOIN выступают поля типа Nullable, то строки, где хотя бы один из ключей имеет значение NULL, не соединяются.
Синтаксис​
Требуется, чтобы столбцы, указанные в USING , назывались одинаково в обоих подзапросах, а остальные столбцы - по-разному. Изменить имена столбцов в подзапросах можно с помощью синонимов.
В секции USING указывается один или несколько столбцов для соединения, что обозначает условие на равенство этих столбцов. Список столбцов задаётся без скобок. Более сложные условия соединения не поддерживаются.
Ограничения cинтаксиса​
Для множественных секций JOIN в одном запросе SELECT :

Получение всех столбцов через * возможно только при объединении таблиц, но не подзапросов.
Секция PREWHERE недоступна.

Для секций ON , WHERE и GROUP BY :

Нельзя использовать произвольные выражения в секциях ON , WHERE , и GROUP BY , однако можно определить выражение в секции SELECT и затем использовать его через алиас в других секциях.

Производительность​
При запуске JOIN , отсутствует оптимизация порядка выполнения по отношению к другим стадиям запроса. Соединение (поиск в «правой» таблице) выполняется до фильтрации в WHERE и до агрегации. Чтобы явно задать порядок вычислений, рекомендуется выполнять JOIN подзапроса с подзапросом.
Каждый раз для выполнения запроса с одинаковым JOIN , подзапрос выполняется заново — результат не кэшируется. Это можно избежать, используя специальный движок таблиц Join, представляющий собой подготовленное множество для соединения, которое всегда находится в оперативке.
В некоторых случаях более эффективно использовать IN вместо JOIN .
Если JOIN необходим для соединения с таблицами измерений (dimension tables - сравнительно небольшие таблицы, которые содержат свойства измерений - например, имена для рекламных кампаний), то использование JOIN может быть не очень удобным из-за громоздкости синтаксиса, а также из-за того, что правая таблица читается заново при каждом запросе. Специально для таких случаев существует функциональность «Внешние словари», которую следует использовать вместо JOIN . Дополнительные сведения смотрите в разделе «Внешние словари».
Ограничения по памяти​
По умолчанию ClickHouse использует алгоритм hash join. ClickHouse берет правую таблицу и создает для нее хеш-таблицу в оперативной памяти. При включённой настройке join_algorithm = 'auto' , после некоторого порога потребления памяти ClickHouse переходит к алгоритму merge join. Описание алгоритмов JOIN см. в настройке join_algorithm.
Если вы хотите ограничить потребление памяти во время выполнения операции JOIN , используйте настройки:

max_rows_in_join — ограничивает количество строк в хеш-таблице.
max_bytes_in_join — ограничивает размер хеш-таблицы.

По достижении любого из этих ограничений ClickHouse действует в соответствии с настройкой join_overflow_mode.
Примеры​
SELECT  CounterID,  hits,  visits FROM ( SELECT  CounterID, count() AS hits FROM test.hits GROUP BY CounterID ) ANY LEFT JOIN ( SELECT  CounterID, sum(Sign) AS visits FROM test.visits GROUP BY CounterID ) USING CounterID ORDER BY hits DESC LIMIT 10 
┌─CounterID─┬───hits─┬─visits─┐ │ 1143050 │ 523264 │ 13665 │ │ 731962 │ 475698 │ 102716 │ │ 722545 │ 337212 │ 108187 │ │ 722889 │ 252197 │ 10547 │ │ 2237260 │ 196036 │ 9522 │ │ 23057320 │ 147211 │ 7689 │ │ 722818 │ 90109 │ 17847 │ │ 48221 │ 85379 │ 4652 │ │ 19762435 │ 77807 │ 7026 │ │ 722884 │ 77492 │ 11056 │ └───────────┴────────┴────────┘ 
Объяснение SQL объединений JOIN: LEFT/RIGHT/INNER/OUTER

Разберем пример. Имеем две таблицы: пользователи и отделы.
U) users D) departments 
id name d_id id name 
-- ---- ---- -- ---- 
1 Владимир 1 1 Сейлз 
2 Антон 2 2 Поддержка 
3 Александр 6 3 Финансы 
4 Борис 2 4 Логистика 
5 Юрий 4
SELECT u.id , u.name , d.name AS d_name 
FROM users u 
INNER JOIN departments d ON u.d_id = d.id
Запрос вернет объединенные данные, которые пересекаются по условию, указанному в INNER JOIN ON . 
В нашем случае условие . должен совпадать с .
В результате отсутствуют:
- пользователь Александр (отдел 6 - не существует) 
- отдел Финансы (нет пользователей)
id name d_name 
-- -------- --------- 
1 Владимир Сейлз 
2 Антон Поддержка 
4 Борис Поддержка 
3 Юрий Логистика
рис. Inner join
Внутреннее объединение INNER JOIN (синоним JOIN, ключевое слово INNER можно опустить).
Выбираются только совпадающие данные из объединяемых таблиц.
Чтобы получить данные, которые подходят по условию частично, необходимо использовать
внешнее объединение - OUTER JOIN.
Такое объединение вернет данные из обеих таблиц (совпадающие по условию объединения) ПЛЮС дополнит выборку оставшимися данными из внешней таблицы, которые по условию не подходят, заполнив недостающие данные значением NULL.
рис. Left join
Существует два типа внешнего объединения OUTER JOIN - LEFT OUTER JOIN и RIGHT OUTER JOIN.
Работают они одинаково, разница заключается в том что LEFT - указывает что "внешней" таблицей будет находящаяся слева (в нашем примере это таблица users). 
Ключевое слово OUTER можно опустить. Запись LEFT JOIN идентична LEFT OUTER JOIN.
SELECT u.id , u.name , d.name AS d_name 
FROM users u 
LEFT OUTER JOIN departments d ON u.d_id = d.id
Получаем полный список пользователей и сопоставленные департаменты.
id name d_name 
-- -------- --------- 
1 Владимир Сейлз 
2 Антон Поддержка 
3 Александр NULL 
4 Борис Поддержка 
5 Юрий Логистика
WHERE d.id IS NULL
в выборке останется только 3#Александр, так как у него не назначен департамент.
рис. Left outer join с фильтрацией по полю
RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей.
SELECT u.id , u.name , d.name AS d_name 
FROM users u 
RIGHT OUTER JOIN departments d ON u.d_id = d.id
id name d_name 
-- -------- --------- 
1 Владимир Сейлз 
2 Антон Поддержка 
4 Борис Поддержка 
NULL NULL Финансы 
5 Юрий Логистика
Дополнительно можно отфильтровать данные, проверяя их на NULL.
SELECT d.id , d.name 
FROM users u 
RIGHT OUTER JOIN departments d ON u.d_id = d.id 
WHERE u.id IS null
В нашем примере указав WHERE u.id IS null, мы выберем департаменты, в которых не числятся пользователи. (3#Финансы)
 Все примеры вы можете протестировать здесь: 
Cross/Full Join
FULL JOIN возвращает `объединение` объединений LEFT и RIGHT таблиц, комбинируя результат двух запросов.
CROSS JOIN возвращает перекрестное (декартово) объединение двух таблиц. Результатом будет выборка всех записей первой таблицы объединенная с каждой строкой второй таблицы. Важным моментом является то, что для кросса не нужно указывать условие объединения.
Дублирование строк при использовании JOIN
При использовании объединения новички часто забывают что результирующая выборка может содержать дублирующиеся данные! 
Если вам нужна одна запись, делайте объединение с подзапросом
SELECT t1. * , t2. * from left_table t1 left join ( select * from right_table where some_column = 1 limit 1 ) t2 ON t1.id = t2.join_id
Self Join
Выборка из одной и той же таблицы для нескольких условий.
Рассмотрим задачку от яндекса:
Есть таблица товаров.
CREATE TABLE `ya _ goods` ( 
`id` int ( 11 ) unsigned NOT NULL AUTO_INCREMENT , 
`name` varchar ( 64 ) NOT NULL , 
PRIMARY KEY ( `id` ) 
) ENGINE = InnoDB DEFAULT CHARSET = utf8 ; 
insert into ya_goods values ( 1 , 'яблоки' ) , ( 2 , 'яблоки' ) , ( 3 , 'груши' ) , ( 4 , 'яблоки' ) , ( 5 , 'апельсины' ) , ( 6 , 'груши' ) ;
Она содержит следующие значения.
`id` `name` 
1 Яблоки 
2 Яблоки 
3 Груши 
4 Яблоки 
5 Апельсины 
6 Груши
Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:
При решении задачи необходимо учесть, что пары (x,y) и (y,x) — одинаковы.
SELECT g1.id id1 , g2.id id2 
-- CONCAT('(', LEAST(g1.id, g2.id), ',', GREATEST(g1.id, g2.id), ')') row 
FROM ya_goods g1 
INNER JOIN ya_goods g2 ON g1.name = g2.name 
WHERE g1.id <> g2.id 
GROUP BY LEAST ( g1.id , g2.id ) , GREATEST ( g1.id , g2.id ) 
ORDER BY g1.id ;
-- или без группировки (быстрее)
SELECT DISTINCT CONCAT ( '(' , LEAST ( g1.id , g2.id ) , ',' , GREATEST ( g1.id , g2.id ) , ')' ) row 
FROM ya_goods g1 
INNER JOIN ya_goods g2 ON g1.name = g2.name 
WHERE g1.id <> g2.id
Объединяем таблицы ya_goods по одинаковому полю `name`, группируем по уникальным idентификаторам и получаем результат.
Множественное объединение multi join
Пригодится нам, если необходимо выбрать более одного значения из таблиц для нескольких условий.
Пример: набор вариантов (вес, объем) товаров. 
Продукты в таблице products, Варианты - таблица product_options, Значения вариантов - таблица product2options 
Необходимо: фильтровать продукты по дате, и имеющимся вариантам
CREATE TABLE `products` ( 
`id` int ( 11 ) , 
`title` varchar ( 255 ) , 
`created _ at` datetime 
)
CREATE TABLE `product _ options` ( 
`id` int ( 11 ) , 
`name` varchar ( 255 ) 
)
CREATE TABLE `product2options` ( 
`product _ id` int ( 11 ) , 
`option _ id` int ( 11 ) , 
`value` int ( 11 ) 
)
INSERT INTO `products` ( `id` , `title` , `created _ at` ) VALUES 
( 1 , 'Кружка' , '2009-01-17 20:00:00' ) , 
( 2 , 'Ложка' , '2009-01-18 20:00:00' ) , 
( 3 , 'Тарелка' , '2009-01-19 20:00:00' ) ;
INSERT INTO `product _ options` ( `id` , `name` ) VALUES 
( 11 , 'Вес' ) , 
( 12 , 'Объем' ) ;
INSERT INTO `product2options` ( `product _ id` , `option _ id` , `value` ) VALUES 
( 1 , 11 , 200 ) , 
( 1 , 12 , 250 ) , 
( 2 , 11 , 35 ) , 
( 2 , 12 , 15 ) , 
( 3 , 11 , 310 ) , 
( 3 , 12 , 300 ) , 
( 2 , 11 , 45 ) , 
( 2 , 12 , 25 ) ;
Пример: выбрать товары, 
добавленные после 17/01/2009 в следующих вариантах: 
- вес=310, объем=300 
- вес=35, объем=15 
- вес=45, объем=25 
- вес=200, объем=250
Просто перечислить условия вариантов в подзапросе/джоине через OR/AND не сработает, 
необходимо осуществить объединение таблиц вариантов равное количеству этих самых вариантов (у нас - 2: объем и вес)
SELECT p. * , po1.name 'P1' , p2o1. value , po2.name 'P2' , p2o2. value
FROM products p
INNER JOIN product2options p2o1 ON p.id = p2o1.product_id 
INNER JOIN product_options po1 ON po1.id = p2o1.option_id
INNER JOIN product2options p2o2 ON p.id = p2o2.product_id 
INNER JOIN product_options po2 ON po2.id = p2o2.option_id
WHERE p.created_at > '2009-01-17 21:00' 
AND ( -- тарелка#3 
p2o1.option_id = 11 AND p2o1. value = 310 
AND p2o2.option_id = 12 AND p2o2. value = 300 
OR -- ложка#2 
p2o1.option_id = 11 AND p2o1. value = 35 
AND p2o2.option_id = 12 AND p2o2. value = 15 
OR -- ложка#2 
p2o1.option_id = 11 AND p2o1. value = 45 
AND p2o2.option_id = 12 AND p2o2. value = 25 
OR -- кружка#1 не попадает по дате 
p2o1.option_id = 12 AND p2o1. value = 250 
AND p2o2.option_id = 11 AND p2o2. value = 200 
) 
;
id title created_at P1 value P2 value 
2 Ложка 2009-01-18 20:00:00 Вес 35 Объем 15 
3 Тарелка 2009-01-19 20:00:00 Вес 310 Объем 300 
2 Ложка 2009-01-18 20:00:00 Вес 45 Объем 25
-- не попадает по дате 
1 Кружка 2009-01-17 20:00:00 Объем 250 Вес 200
UPDATE и JOIN
Объединение можно использовать совместно с UPDATE. 
Например, имеем таблицу houses (id, title, area). Нужно выбрать title, если в нем встречается `число м2`, заменить поле area, если оно меньше. Т.к. в mysql отстутсутствует поддержка регулярных выражений, нужно немного поколдовать с locate и substr. 
В подзапросе выбираем интересующие нас данные, и в финальной стадии осуществляем обновление данных подходящий по критерию (p5 > area).
UPDATE houses base 
INNER JOIN ( 
-- Антарис аренда офиса 1594 м2, по ставке 12700 руб. м2/год -> 1594 
SELECT 
id , 
@baseString := title title , 
@areaTitleEnd := LOCATE ( ' м2' , @baseString ) as p2 , 
@tmpString := LTRIM ( REVERSE ( SUBSTR ( @baseString , 1 , @areaTitleEnd ) ) ) as p3 , 
@areaTitleBegin := LEFT ( @tmpString , - 1 + LOCATE ( ' ' , @tmpString ) ) as p4 , 
@ value := CAST ( REVERSE ( @areaTitleBegin ) as UNSIGNED ) as p5
FROM ga_pageviews 
WHERE title like ' % м2 % ' 
) calc USING ( `id` ) 
SET base. area = calc.p5 
WHERE base. area < calc.p5
DELETE и JOIN
Рассмотрим пример с удалением дубликатов. Есть таблица tableWithDups (id, email). Нужно удалить строки с одинаковыми email:
DELETE tableWithDups 
FROM tableWithDups 
INNER JOIN ( 
SELECT MAX ( id ) AS lastId , email 
FROM tableWithDups 
GROUP BY email 
HAVING COUNT ( * ) > 1 
) dups ON dups.email = tableWithDups.email 
WHERE tableWithDups.id < dups.lastId ;
Последние два примера не совместимы с ANSI SQL, но работают в mySQL.
За бортом статьи остались смежные объединениям (а также специфичные для определенных базданных темы): 
SELF JOIN, FULL OUTER JOIN, CROSS JOIN (CROSS [OUTER] APPLY), операции над множествами UNION [ALL], INTERSECT, EXCEPT и т.д.
@tags: sql, mysql, sql server, oracle, sqlite, postgresql 


Похожие публикации:

В приложении gboard произошла ошибка как исправить
Как в документ ворд вставить ссылку на другой документ
Как добавить элемент в начало списка
Как перевести png в bmp