MySQL: оператор JOIN

Большинство начинающих веб программистов начинает свое изучение MySQL с простейших операторов SELECT, UPDATE и DELETE. Данными операторами вполне можно описать весь необходимый функционал простого сайта, но, как можно догадаться, на этом возможности языка SQL далеко не заканчиваются. В процессе разработки обязательно потребуется объединение данных из нескольких таблиц. И для этих целей существует оператор JOIN. Данный оператор является основным оператором стандарта SQL92 и поддерживается большинством СУБД. Общий синтаксис JOIN выглядит следующим образом:

SELECT FIELD [,... n]
FROM MainTable
{INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN JoinTable ON <conditions>

Однако, сразу следует отметить, что СУБД MySQL поддерживает только два наиболее популярных выражения: INNER JOIN и LEFT JOIN. Рассмотрим как работает каждый из операторов, для этого создадим две таблицы: TableA и TableB.

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

Попробуем объединить данные из этих таблиц используя различные варианты конструкции оператора JOIN. 1. INNER JOIN – внутреннее соединение. Объединяет две таблицы, где каждая строка обоих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор.

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

[caption id="attachment_428" align="aligncenter" width="502"]

INNER JOIN[/caption] Выбор по первичному ключу и индексу положительно сказывается на скорости выборки. 2. OUTER JOIN – внешнее объединение. Присоединение таблицы с необязательным присутствием записи в таблице. Также как и в случае с inner join, условие по индексированным полям и первичному ключу ускоряет все виды outer join'ов. 2.1 LEFT OUTER JOIN или LEFT JOIN-левое внешнее объединения. Левосторонние объединения позволяют извлекать данные из левой таблицы, дополняя их по возможности данными из правой таблицы, поля правой таблицы заполняются значениями NULL.

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

[caption id="attachment_433" align="aligncenter" width="502"]

LEFT OUTER JOIN[/caption] Если дополнить предыдущий запрос условием на проверку несуществования, то можно получить список записей, которые не имеют пары в таблице TableB:

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null

[caption id="attachment_435" align="aligncenter" width="502"]

LEFT OUTER JOIN[/caption] 2.2 RIGHT OUTER JOIN или RIGHT JOIN - Правостороннее внешнее объединение Этот вид объединений практически ничем не отличается от левостороннего объединения, за тем исключением, что данные берутся из второй таблицы, которая находится справа от конструкции JOIN, и сравниваются с данными, которые находятся в таблице, указанной перед конструкцией. 2.3 FULL OUTER JOIN – комбинация правого и левого объединений. К левой таблице присоединяются все записи из правой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL и плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

[caption id="attachment_434" align="aligncenter" width="502"]

FULL OUTER JOIN[/caption]

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

[caption id="attachment_436" align="aligncenter" width="502"]

FULL OUTER JOIN[/caption] 3. CROSS JOIN – перекрестное объединение (декартово произведение), выводятся все возможные сочетания из обеих таблиц. Для этого типа оператора JOIN условия не указывается.

SELECT * FROM TableA
CROSS JOIN TableB

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Rutabaga
3  Ninja      3   Rutabaga
4  Spaghetti  4   Rutabaga
1  Pirate     1   Pirate
2  Monkey     2   Pirate
3  Ninja      3   Pirate
4  Spaghetti  4   Pirate
1  Pirate     1   Darth Vader
2  Monkey     2   Darth Vader
3  Ninja      3   Darth Vader
4  Spaghetti  4   Darth Vader
1  Pirate     1   Ninja
2  Monkey     2   Ninja
3  Ninja      3   Ninja
4  Spaghetti  4   Ninja

Данная конструкция, по причине своей не нужности, не поддерживается почти ни в одной БД

TableA