При разработке веб-проектов с участием базы данных нам часто необходимо в запросах объединять таблицы базы, чтобы получить необходимые данные.
В статье рассмотрены принципы объединений таблиц и, для наглядности, они изображены на круговых диаграммах (диаграммах Венна). Первоисточником статьи является статья Джеффа Этвуда, но я (Kreker, в смысле), её перевел, подрихтовал и добавил неописанные типы объединений для MySQL (изначально статья была для другой СУБД).
Предупреждение: в статье в условии объединения таблиц используется как ON, так и USING, для разнообразия. Напоминаю, что если столбцы, по которым происходит объединение, имеют одинаковые имена, то необходимо использовать USING (`имя_столбца`), в противном случае используется ON `TableA`.`имя_столбца_из_TableA` = `TableB`.`имя_столбца_из_TableB`
Итак, предположим, что у нас есть два стола. Стол А (TableA) слева, а стол Б (TableB) справа. Мы заселим каждый четырьмя персонажами, имена которых могут присутствовать на обеих столах.
TableA TableB
id name id name
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja
В СУБД MySQL существуют следующие операторы объединения:
CROSS JOIN, он же INNER JOIN, он же JOIN
Аналоги FULL OUTER JOIN для MySQL
LEFT JOIN
RIGHT JOIN
NATURAL JOIN
STRAIGHT JOIN
Если не указать USING или ON в объединении (Декартова выборка)
INNER JOIN
— INNER JOIN производит выборку записей, которые только существуют в TableA и TableB одновременно.
— CROSS JOIN — это эквивалент INNER JOIN.
— INNER JOIN можно заменить условием объединения в WHERE.
Запрос:
SELECT * FROM `TableA`
INNER JOIN `TableB`
ON `TableA`.`name` = `TableB`.`name`
Идеентичный запрос:
SELECT * FROM `TableA`,`TableB`
WHERE `TableA`.`name` = `TableB`.`name`
Результат:
TableA TableB
id name id name
1 Pirate 2 Pirate
3 Ninja 4 Ninja
Диаграмма INNER (CROSS) JOIN
FULL OUTER JOIN*
*Не доступно в MySQL
FULL OUTER JOIN производит выборку всех записей из TableA и TableB, вне зависимости есть ли соответствующая запись в соседней таблице. Если таковой нет, то недостающая сторона будет содержать пустой указатель и результатом будет выводится NULL.
Запрос:
SELECT * FROM `TableA`
FULL OUTER JOIN `TableB`
ON `TableA`.`name` = `TableB`.`name`
Результат:
TableA TableB
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
В MySQL нечто похожее можно получить запросом:
SELECT `TableA`.*, `TableB`.* FROM `TableA`
LEFT JOIN `TableB` USING (`name`)
UNION SELECT `TableA`.*, `TableB`.* FROM `TableB`
LEFT JOIN `TableA`
USING (`name`)
WHERE `TableA`.`name` IS NULL
Результат:
TableA TableB
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, указав, что NULL может быть как в результате одной таблицы, так и в результате другой.
Запрос:
SELECT * FROM `TableA`
FULL OUTER JOIN `TableB`
ON `TableA`.`name` = `TableB`.`name`
WHERE `TableA`.`id` IS NULL OR `TableB`.`id` IS NULL
Результат:
TableA TableB
id name id name
2 Monkey NULL NULL
4 Spaghetti NULL NULL
NULL NULL 1 Rutabaga
NULL NULL 3 Darth Vader
Диаграмма FULL OUTER JOIN (уникальные записи из двух таблиц)
В MySQL нечто похожее можно получить запросом:
SELECT `TableA`.*, `TableB`.* FROM
TableA LEFT JOIN `TableB`
USING (`name`)
WHERE `TableB`.`name` IS NULL
UNION SELECT `TableA`.*, `TableB`.* FROM `TableB `
LEFT JOIN `TableA` USING (`name`)
WHERE `TableA`.`name` IS NULL
Результат:
TableA TableB
id name id name
2 Monkey NULL NULL
4 Spaghetti NULL NULL
NULL NULL 1 Rutabaga
NULL NULL 3 Darth Vader
LEFT JOIN
LEFT OUTER JOIN (LEFT JOIN) указывает, что левая таблица управляющая (в нашем случае TableA) и производит из нее полную выборку, осуществляя поиск соответствующих записей в таблице TableB. Если таких соответствий не найдено, то база вернет пустой указатель - NULL. Указание OUTER - не обязательно.
Запрос:
SELECT * FROM `TableA`
LEFT JOIN `TableB`
ON `TableA`.`name` = `TableB`.`name`
Результат:
TableA TableB
id name id name
1 Pirate 2 Pirate
2 Monkey NULL NULL
3 Ninja 4 Ninja
4 Spaghetti NULL NULL
Диаграмма LEFT JOIN
Чтобы произвести выборку записей из таблицы TableA, которых не существует в таблице TableB, мы выполняем LEFT JOIN, но затем из результата исключаем записи, которые не хотим видеть, путем указания, что TableB.id является нулем (указывая, что записи нет в таблице TableB).
Запрос:
SELECT * FROM `TableA`
LEFT JOIN `TableB`
ON `TableA`.`name` = `TableB`.`name`
WHERE `TableB`.`id` IS NULL
Результат:
TableA TableB
id name id name
2 Monkey NULL NULL
4 Spaghetti NULL NULL
Диаграмма LEFT JOIN (Записи существуют только в левой таблице)
RIGHT JOIN
RIGHT JOIN выполняет те же самые функции, что и LEFT JOIN, за исключением того, что правая таблица будет прочитана первой. Таким образом, если в запросах из предыдущей главы LEFT заменить на RIGHT, то таблица результатов, грубо говоря, отразится по вертикали. То есть, в результате вместо значений TableA будут записи TableB и наоборот.
NATURAL JOIN
Суть этой конструкции в том, что база сама выбирает, по каким столбцам сравнивать и объединять таблицы. А выбор этот падает на столбцы с одинаковыми именами. В этом кроется засада &mdash база может выбрать совершенно не те столбцы для объединения и запрос будет работать совершенно не так, как вы предполагали.
Запрос:
SELECT * FROM `TableA`
NATURAL JOIN `TableB`
В этом случае СУБД выбирает для объединения таблиц столбцы id и name, так как они присутствуют в обеих таблицах и превращает исходный запрос в запрос следующего вида:
SELECT * FROM `TableA`
INNER JOIN `TableB`
USING (`id`, `name`)
Но так как у нас нет записей с одинаковым id и name одновременно в обеих таблицах, то запрос вернет пустой результат.
Если же сделать управляющей левую таблицу и изменить запрос:
SELECT `TableA`.*, `TableB`.* FROM `TableA`
NATURAL LEFT JOIN `TableB`
Такой запрос приводится СУБД к следующему:
SELECT `TableA`.*, `TableB`.* FROM `TableA`
LEFT JOIN `TableB`
USING (`id`, `name`)
То результат будет таким:
TableA TableB
id name id name
1 Pirate NULL NULL
2 Monkey NULL NULL
3 Ninja NULL NULL
4 Spaghetti NULL NULL
Происходит это так: так как левая таблица управляющая, то она читается первой и полностью выбирается, независимо от правой таблицы; когда начинается поиск соответствующих записей в правой таблице, то СУБД не находит ни одной записи, которая была бы идентична по name и id одновременно, поэтому возвращаются пустые указатели.
Для более подробного понимания работы NATURAL JOIN изменим name в первой записи в таблице TableB на Pirate.
UPDATE `TableB` SET `name`='Pirate' WHERE `id`=1
Таким образом, у нас получилось:
TableA TableB
id name id name
1 Pirate 1 Pirate
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja
А теперь выполним те же запросы с NATURAL JOIN, что использованы выше.
Запрос:
SELECT * FROM `TableA`
NATURAL JOIN `TableB`
Результат:
id name
1 Pirate
Так как теперь запись с одинаковым id и name присутствует в обеих таблицах, то она и будет выведена.
Запрос:
SELECT `TableA`.*, `TableB`.* FROM `TableA`
NATURAL LEFT JOIN `TableB`
Возвращает результат:
TableA TableB
id name id name
1 Pirate 1 Pirate
2 Monkey NULL NULL
3 Ninja NULL NULL
4 Spaghetti NULL NULL
Таким образом, база сама выбирает по каким столбцам и каким способом объединять таблицы. С одной стороны это весьма удобно, с другой — несет неразбериху: где гарантия того, что столбцы с одинаковыми именами в таблицах будут именно ключевыми и предназначены для объединения? NATURAL JOIN ухудшает читаемость кода, так как разработчик не сможет по запросу определить, как объединяются таблицы. Поэтому, обращая внимание на такие факторы, NATURAL JOIN использовать не рекомендуется.
STRAIGHT JOIN
STRAIGHT JOIN выполняет те же функции, что и обычный INNER JOIN, за исключением того, что левая таблица читается раньше правой.
Запрос:
SELECT * FROM `TableA`
STRAIGHT JOIN `TableB` USING(`name`)
Вернет результат:
TableA TableB
name id id
Pirate 1 1
Pirate 1 2
Ninja 3 4
Запрос:
SELECT * FROM `TableB`
STRAIGHT JOIN `TableA` USING(`name`)
Вернет результат:
TableA TableB
name id id
Pirate 1 1
Pirate 2 1
Ninja 4 3
Декартова выборка
Если при объединении таблиц не указать условие объединения через ON или USING, то база произведет так называемую Декартову выборку, когда значению одной таблицы приравнивается каждое значение другой. Таким образом, СУБД, в нашем случае, возвращает 4x4 = 16 строк.
Запрос:
SELECT * FROM `TableA`
JOIN `TableB`
Результат:
TableA TableB
id name id name
1 Pirate 1 Rutabaga
2 Monkey 1 Rutabaga
3 Ninja 1 Rutabaga
4 Spaghetti 1 Rutabaga
1 Pirate 2 Pirate
2 Monkey 2 Pirate
3 Ninja 2 Pirate
4 Spaghetti 2 Pirate
1 Pirate 3 Darth Vader
2 Monkey 3 Darth Vader
3 Ninja 3 Darth Vader
4 Spaghetti 3 Darth Vader
1 Pirate 4 Ninja
2 Monkey 4 Ninja
3 Ninja 4 Ninja
4 Spaghetti 4 Ninja