Большинство начинающих веб программистов начинает свое изучение MySQL с простейших операторов SELECT, UPDATE и DELETE. Данными операторами вполне можно описать весь необходимый функционал простого сайта, но, как можно догадаться, на этом возможности языка SQL далеко не заканчиваются. В процессе разработки обязательно потребуется объединение данных из нескольких таблиц. И для этих целей существует оператор JOIN. Данный оператор является основным оператором стандарта SQL92 и поддерживается большинством СУБД.
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
INNER JOIN
Выбор по первичному ключу и индексу положительно сказывается на скорости выборки.
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
LEFT OUTER JOIN
Если дополнить предыдущий запрос условием на проверку несуществования, то можно получить список записей, которые не имеют пары в таблице 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
LEFT OUTER JOIN
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
FULL OUTER JOIN
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
FULL OUTER JOIN
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