Шпаргалка MySQL JOIN

Уже несколько лет успешно использую MySQL, но почему-то периодически возникает тупняк, когда нужно объединить несколько таблиц в запросе. Лезу в инет, со скрипом начинаю заново вникать в элементарные, казалось бы, вещи. Надоело, сделал свою "шпору" по теме использования JOIN в MySQL

Согласно справки MySQL 5.7 поддерживает следующий синтаксис оператора JOIN при использовании в командах SELECT:

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

где table_reference определено, как: 
table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]

Есть ньюансы относильно таблиц и индексов, но это к теме не относится, разбираемся непосредственно с JOIN-ами :)

Общие замечания

Слово ON почти равносильно WHERE. Для него можно использовать любые операторы сравнения. При оптимизации JOIN-запроса все, что есть в ON, будет перенесено в WHERE. Т.о. можно выполнить некоторые объединения без указания ON, но это бессмысленно.

Если в условии перечисляются равенства одноименных полей, то вместо ON можно использовать USING. На заметку: при запросе всех полей таблиц (*) c использованием USING, оптимизатор в ответе не дублирует совпадающие поля. Поля, по которым проводилось сравнение, идут первыми. Например:

SELECT * FROM a JOIN b ON a.C1=b.C1 AND a.C2=b.C2 AND a.C3=b.C3,...

Результат

равносильный запрос:

SELECT * FROM a JOIN b USING (C1,C2,C3,...)

Результат

Я буду придерживаться короткого оптимизированного оформления, где это возможно.

В примерах запросов для улучшения восприятия кода я не использую обратные кавычки. В реальных запросах не стоит пренебрегать этим оформлением.

Исходные таблицы (дамп):

Слово OUTER обусловлено только требованиями совместимости с ODBC, т.е. LEFT OUTER JOIN в MySQL выдаст тоже самое, что и LEFT JOIN.

LEFT | RIGHT JOIN не может использоваться без условия описанного в ON/USING, получите ошибку синтаксиса.

JOIN, INNER JOIN, CROSS JOIN - в MySQL это одно и то же (в стандартном SQL есть разница). STRAIGHT_JOIN так же идентично JOIN, за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех случаев, когда оптимизатор объединения располагает таблицы в неправильном порядке.

INNER JOIN и [перечисление таблиц через запятую] - эквивалентны при отстутствии условия объединения. В итоге получится "Декартов результат", т.е. каждому ряду одной таблицы дописывается каждый ряд другой таблицы.

Выражение NATURAL [LEFT] JOIN - это семантический эквивалент INNER JOIN или LEFT JOIN с выражением USING, в котором указаны все столбцы, имеющиеся в обеих таблицах. Понятное дело, в таком случае таблицы должны иметь схожие структуры. Используя таблицы примеров получим равносильные запросы:

SELECT * FROM t1 NATURAL JOIN t2
-- равносильно
SELECT * FROM t1 JOIN t2 USING (id, company)

Результатом будет:

Строгое соответвие

SELECT * FROM t1 JOIN t2 USING (company)
 -- тоже самое
SELECT * FROM t1, t2 WHERE t1.company = t2.company

Выбрать только те записи, у которых совпадают значения в указанном поле.

Левостроннее объединение

SELECT * FROM t1 LEFT JOIN t2 USING (company)

Для каждой записи t1 ищем равную по значению в поле company запись в t2. Если не найдем, то вместо значений из t2 пишем NULL.

Добавим некоторую "фильтрацию"

SELECT * FROM t1 LEFT JOIN t2 USING (company) WHERE t2.company IS NULL

Получим записи из первой таблицы, для которых нет равных по полю company во второй таблице.

Правостороннее объединение

Суть его такая же, что и LEFT JOIN, все зависит только от последовательности таблиц. Для поддержания переносимости БД на другие платформы рекомендуется использовать только LEFT JOIN. Однако для полного понимания приведу еще один пример. Допустим сохраним ту же последовательность таблиц в запросе, тогда в результате

SELECT * FROM t1 RIGHT JOIN t2 USING (company)
-- равносильное ему левостороннее объединение
SELECT * FROM t2 LEFT JOIN t1 USING (company) 

получим

Т.е. теперь вся правая таблица входит в результат, а при остутствии в левой таблице подходящих значений вместо них получим NULL.

А вот такой запрос может сбить с толку в понимании значения LEFT|RIGHT JOIN:

SELECT * FROM t1 RIGHT JOIN t2 USING (id) --другое поле равенства

В результате нет полей с Null. Это потому, что каждой записи из t2 нашлось совпадение по полю id в таблице t1. Я это не сразу понял, хотя очевидно :)

Все виды объединений пытаются создать максимально полный результат, объединяя записи "все-со-всеми" с учетом условий. Поэтому рекомендую проводить объединения по полям с уникальными индексами, иначе можно получить лишние записи. В приведенных примерах использовано поле "company", значение в котором может быть неуникальным, но для иллюстрации материала удобнее оперировать словами, а не числами.

Наиболее яркий пример объединения "всех-со-всеми":

SELECT * FROM t2, t1 -- вообще нет условия объединения

Получим 20 записей ( COUNT(t1) * COUNT(t2) ), т.е. к каждой записи из t2 дописывается первая запись из t1, затем вторая запись из t1 и т.д. На общий результат влияет только количество записей в каждой таблице.

До 5-й версии в MySQL существовал вид объединения, способный вернуть уникальную выборку с полным объединением двух таблиц, FULL OUTER JOIN. Так же с дополнительным условием к нему можно было получить выборку записей, существующих только в каждой из таблиц. Сейчас нет нормальной замены этому выражению. Если понадобится, придется изобретать велик..