MySQL: LEFT/RIGHT/INNER JOIN объединение таблиц

MySQL поддерживает синтаксис [LEFT|RIGHT|INNER] JOIN для части определения таблиц в операторе SELECT и операторах DELETE и UPDATE для нескольких таблиц. Объединения таблиц позволяют извлекать данные из нескольких таблиц без создания временных таблиц и за один запрос.

Пример объединения нескольких таблиц при помощи LEFT JOIN:

SELECT * FROM t1 
    LEFT JOIN t2 ON (t2.a = t1.a)
    LEFT JOIN t3 ON (t2.b = t3.b)

-- эквивалентно
SELECT * FROM t1 
LEFT JOIN t2
    INNER JOIN t3
    ON t2.b = t3.b
ON t2.a = t1.a

Здесь все записи из таблицы t1 соединяются с соответствующими записями таблицы t2, в свою очередь все записи из таблицы t2 соединяются с соответствующими записями таблицы t3.

Эквивалентный вариант объединяет t1 с t3 через промежуточную таблицу t2. Так как соединение между t1 и t2 является LEFT JOIN, то получаем все записи таблицы t1.

Инструкция ON служит для условий, указывающих, как соединять таблицы. Другими словами, выражение ON (t2.a = t1.a) говорит MySQL по каким полям объединять таблицу t1 с таблицей t2. Оператор ON может содержать сложные условия объединения таблиц, например ON (t2.a = t1.a AND t2.b = t1.b) - здесь происходит соединение таблицы t1 с таблицей t2 по двум столбцам a и b.

При объединении таблиц, вместо ссылки на таблицу может использоваться псевдоним alias, который присваивается при помощи выражений tbl AS alias или tbl alias:

-- использование `tbl AS alias`
SELECT * FROM left_tbl AS a 
      LEFT JOIN right_tbl AS b ON (b.id = a.id)

-- эквивалентно, без `AS`
SELECT * FROM left_tbl a 
      LEFT JOIN right_tbl b ON (b.id = a.id)

Поддерживаемые типы объединений в MySQL:

• LEFT JOIN: возвращает все записи из левой таблицы и соответствующие записи из правой таблицы.
• RIGHT JOIN: возвращает все записи из правой таблицы и соответствующие записи из левой таблицы.
• INNER JOIN или просто JOIN: выбирает записи, которые имеют совпадающие значения в обеих таблицах (перекрёстное объединение).

В MySQL CROSS JOIN и INNER JOIN являются синтаксическими эквивалентами (они могут заменять друг друга). В стандартном SQL они не эквивалентны. INNER JOIN используется с предложением ON, в противном случае используется CROSS JOIN.

LEFT JOIN:

На практике чаще всего используется объединение таблиц при помощи инструкции LEFT JOIN.

SELECT * FROM left_tbl 
    LEFT JOIN right_tbl ON (right_tbl.id = left_tbl.id)

Запрос возвращает все записи из левой (первой) таблицы left_tbl и только совпавшие записи (согласно right_tbl.id = left_tbl.id) из правой (второй) таблицы right_tbl.

При использовании LEFT JOIN, если нет соответствующей строки/записи для правой таблицы в операторе ON, то для правой таблицы используется строка со всеми столбцами, установленными в NULL. Это поведение можно использовать, чтобы найти строки/записи в таблице, которые не имеют аналога в другой таблице:

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

RIGHT JOIN:

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

SELECT * FROM left_tbl 
    RIGHT JOIN right_tbl ON (right_tbl.id = left_tbl.id)

Запрос возвращает все записи из правой (второй) таблицы right_tbl и только совпавшие записи (согласно right_tbl.id = left_tbl.id) из левой (первой) таблицы left_tbl.

JOIN,
INNER JOIN:

Объединения при помощи JOIN или INNER JOIN производят декартово произведение между указанными таблицами (то есть каждая строка в первой таблице соединяется с каждой совпадающей строкой во второй таблице).

Инструкции JOIN/INNER JOIN и запятая , между названиями таблиц в операторе FROM семантически эквивалентны. Способ связывания таблиц в этом случае задается в условии WHERE.

SELECT * FROM left_tbl 
    INNER JOIN right_tbl ON (left_tbl.id = right_tbl.id)

-- эквивалентно
SELECT * FROM left_tbl, right_tbl 
    WHERE left_tbl.id = right_tbl.id

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