Mysql select join примеры. Простые примеры JOIN. Виды конструкций для объединения
В этом учебном пособии вы узнаете, как использовать JOINS (INNER и OUTER)
в MySQL с синтаксисом, рисунками и примерами.
MySQL JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.
Существуют различные типы соединений MySQL:
Рассмотрим синтаксис MySQL JOIN, а также изучим примеры MySQL JOIN.
INNER JOIN (простое соединение)Скорее всего, вы уже писали запросы в которых используются MySQL INNER JOIN. Это наиболее распространенный тип соединения. MySQL INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.
СинтаксисСинтаксис INNER JOIN в MySQL:
SELECT columns
FROM table1
INNER JOIN table2
В этом рисунке, MySQL INNER JOIN возвращает затененную область:
MySQL INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.
ПримерНиже приведен пример MySQL INNER JOIN:
MySQL
У нас есть еще одна таблица orders с тремя полями (order_id , supplier_id и order_date ). Она содержит следующие данные:
| 500125 | 10000 | 05.05.2015 |
| 500126 | 10001 | 08.02.2016 |
| 500127 | 10004 | 06.01.2017 |
Если мы выполним MySQL оператор SELECT (который содержит INNER JOIN) ниже:
MySQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Строки для Microsoft и NVIDIA из таблицы suppliers будут опущены, так как значения supplier_id 10002 и 10003 не существует в обеих таблицах. Строка order_id 500127 из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers .
Старый СинтаксисВ качестве последнего примечания, стоит отметить, что приведенный выше пример MySQL INNER JOIN можно переписать, используя старый неявный синтаксис следующим образом (но рекомендуется использовать синтаксис INNER JOIN):
Другой тип соединения называется MySQL LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
СинтаксисСинтаксис для LEFT OUTER JOIN в MySQL:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
В некоторых базах данных LEFT OUTER JOIN заменяется на LEFT JOIN.
На этом рисунке, MySQL LEFT OUTER JOIN возвращает затененную область:
MySQL LEFT OUTER JOIN возвратит все записи из table1 и только те записи из table2 , которые пересекаются с table1 .
ПримерMySQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT suppliers.supplier_id,suppliers.supplier_name,orders.order_date FROM suppliers LEFT JOIN orders ON suppliers.supplier_id=orders.supplier_id; |
Этот пример LEFT OUTER JOIN возвратит все строки из таблицы suppliers , и только те строки из таблицы orders , где объединяемые поля равны.
Если значение supplier_id в таблице suppliers не существует в таблице orders , все поля таблицы orders будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает LEFT OUTER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name ) которая содержит следующие данные:
Если мы выполним MySQL оператор SELECT (который содержит LEFT OUTER JOIN) ниже:
MySQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Строки для Microsoft и NVIDIA будут включены, так как был использован LEFT OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.
Другой тип соединения называется MySQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
СинтаксисСинтаксиRIGHT OUTER JOIN в MySQL:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
В некоторых базах данных, RIGHT OUTER JOIN заменяется на RIGHT JOIN.
На этом рисунке, MySQL RIGHT OUTER JOIN возвращает затененную область:
MySQL RIGHT OUTER JOIN возвратит все записи из table2 и только те записи из table1 , которые пересекаются с table2 .
ПримерНиже приведен пример MySQL RIGHT OUTER JOIN:
MySQL
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Оператор языка SQL JOIN предназначен для соединения двух или более таблиц базы данных по совпадающему условию. Этот оператор существует только в реляционных базах данных. Именно благодаря JOIN реляционные базы данных обладают такой мощной функциональностью, которая позволяет вести не только хранение данных, но и их, хотя бы простейший, анализ с помощью запросов. Разберём основные нюансы написания SQL-запросов с оператором JOIN, которые являются общими для всех СУБД (систем управления базами данных). Для соединения двух таблиц оператор SQL JOIN имеет следующий синтаксис:
SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ
После одного или нескольких звеньев с оператором JOIN может следовать необязательная секция WHERE или HAVING, в которой, также, как в простом SELECT-запросе, задаётся условие выборки. Общим для всех СУБД является то, что в этой конструкции вместо JOIN может быть указано INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN (или, как вариант, запятая).
INNER JOIN (внутреннее соединение)Запрос с оператором INNER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON.
То же самое делает и просто JOIN. Таким образом, слово INNER - не обязательное.
Пример 1. Есть база данных портала объявлений. В ней есть таблица Categories (категории объявлений) и Parts (части, или иначе - рубрики, которые и относятся к категориям). Например, части Квартиры, Дачи относятся к категории Недвижимость, а части Автомобили, Мотоциклы - к категории Транспорт. Эти таблицы с заполненными данными имеют следующий вид.
Таблица Parts:
Заметим, что в таблице Parts Книги имеют Cat - ссылку на категорию, которой нет в таблице Categories, а в таблице Categories Техника имеет Cat_ID - первичный ключ, ссылки на который нет в таблице Parts. Требуется соединить данные этих двух таблиц так, чтобы в результирующей таблице были поля Part (Часть), Cat (Категория) и Price (Цена подачи объявления) и чтобы данные полностью пересекались по условию. Условие - совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts. Для этого пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS INNER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
В результирующей таблице нет Книг, так как эта запись ссылается на категорию, которой нет в таблице Categories, и Техники, так как эта запись имеет внешний ключ в таблице Categories, на который нет ссылки в таблице Parts.
В ряде случаев при соединениях таблиц составить менее громоздкие запросы можно с помощью предиката EXISTS и без использования JOIN.
Есть база данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Пример 3. Вывести список актеров, которые в одном спектакле играют более одной роли, и количество их ролей.
Оператор JOIN использовать 1 раз. Использовать HAVING, GROUP BY .
Подсказка. Оператор HAVING применяется к числу ролей, подсчитанных агрегатной функцией COUNT.
LEFT OUTER JOIN (левое внешнее соединение)Запрос с оператором LEFT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой по порядку (левой) таблицы, даже если они не соответствуют условию. У записей левой таблицы, которые не соответствуют условию, значение столбца из правой таблицы будет NULL (неопределённым).
Пример 4. База данных и таблицы - те же, что и в примере 1.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Parts, которые не соответствуют условию, пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS LEFT OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| Книги | 160 | NULL |
В результирующей таблице, в отличие от таблицы из примера 1, есть Книги, но значение столбца Цены (Price) у них - NULL, так как эта запись имеет идентификатор категории, которой нет в таблице Categories.
RIGHT OUTER JOIN (правое внешнее соединение)Запрос с оператором RIGHT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из второй по порядку (правой) таблицы, даже если они не соответствуют условию. У записей правой таблицы, которые не соответствуют условию, значение столбца из левой таблицы будет NULL (неопределённым).
Пример 5.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS RIGHT OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| NULL | 45 | 65,00 |
В результирующей таблице, в отличие от таблицы из примера 1, есть запись с категорией 45 и ценой 65,00, но значение столбца Части (Part) у неё - NULL, так как эта запись имеет идентификатор категории, на которую нет ссылок в таблице Parts.
FULL OUTER JOIN (полное внешнее соединение)Запрос с оператором FULL OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой (левой) и второй (правой) таблиц, даже если они не соответствуют условию. У записей, которые не соответствуют условию, значение столбцов из другой таблицы будет NULL (неопределённым).
Пример 6. База данных и таблицы - те же, что и в предыдущих примерах.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными как из таблицы Parts, так и из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS FULL OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| Книги | 160 | NULL |
| NULL | 45 | 65,00 |
В результирующей таблице есть записи Книги (из левой таблицы) и с категорией 45 (из правой таблицы), причём у первой из них неопределённая цена (столбец из правой таблицы), а у второй - неопределённая часть (столбец из левой таблицы).
Псевдонимы соединяемых таблицВ предыдущих запросах мы указывали с названиями извлекаемых столбцов из разных таблиц полные имена этих таблиц. Такие запросы выглядят громоздко: одно и то же слово повторяется несколько раз. Нельзя ли как-то упростить конструкцию? Оказывается, можно. Для этого следует использовать псевдонимы таблиц - их сокращённые имена. Псевдоним может состоять и из одной буквы. Возможно любое количество букв в псевдониме, главное, чтобы запрос после сокращения был понятен Вам самим. Общее правило: в секции запроса, определяющей соединение, то есть вокруг слова JOIN нужно указать полные имена таблиц, а за каждым именем должен следовать псевдоним таблицы.
Пример 7. Переписать запрос из примера 1 с использованием псевдонимов соединяемых таблиц.
Запрос будет следующим:
SELECT P.Part, C.Cat_ID AS Cat, C.Price FROM PARTS P INNER JOIN CATEGORIES C ON P.Cat = C.Cat_ID
Запрос вернёт то же самое, что и запрос в примере 1, но он гораздо компактнее.
JOIN и соединение более двух таблицРеляционные базы данных должны подчиняться требованиям целостности и неизбыточности данных, в связи с чем данные об одном бизнес-процессе могут содержаться не только в одной, двух, но и в трёх и более таблицах. В этих случаях для анализа данных используются цепочки соединённых таблиц: например, в одной (первой) таблице содержится некоторый количественный показатель, вторую таблицу с первой и третьей связывают внешние ключи - данные пересекаются, но только третья таблица содержит условие, в зависимости от которого может быть выведен количественный показатель из первой таблицы. И таблиц может быть ещё больше. При помощи оператора SQL JOIN в одном запросе можно соединить большое число таблиц. В таких запросах за одной секцией соединения следует другая, причём каждый следующий JOIN соединяет со следующей таблицей таблицу, которая была второй в предыдущем звене цепочки. Таким образом, синтаксис SQL запроса для соединения более двух таблиц следующий:
SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ JOIN ИМЯ_ТАБЛИЦЫ_3 ON УСЛОВИЕ... JOIN ИМЯ_ТАБЛИЦЫ_M ON УСЛОВИЕ
Пример 8. База данных - та же, что и в предыдущих примерах. К таблицам Categories и Parts в этом примере добавится таблица Ads, содержащая данные об опубликованных на портале объявлениях. Приведём фрагмент таблицы Ads, в котором среди записей есть записи о тех объявлениях, срок публикации которых истекает 2018-04-02.
| A_Id | Part_ID | Date_start | Date_end | Text |
| 21 | 1 | "2018-02-11" | "2018-04-20" | "Продаю..." |
| 22 | 1 | "2018-02-11" | "2018-05-12" | "Продаю..." |
| ... | ... | ... | ... | ... |
| 27 | 1 | "2018-02-11" | "2018-04-02" | "Продаю..." |
| 28 | 2 | "2018-02-11" | "2018-04-21" | "Продаю..." |
| 29 | 2 | "2018-02-11" | "2018-04-02" | "Продаю..." |
| 30 | 3 | "2018-02-11" | "2018-04-22" | "Продаю..." |
| 31 | 4 | "2018-02-11" | "2018-05-02" | "Продаю..." |
| 32 | 4 | "2018-02-11" | "2018-04-13" | "Продаю..." |
| 33 | 3 | "2018-02-11" | "2018-04-12" | "Продаю..." |
| 34 | 4 | "2018-02-11" | "2018-04-23" | "Продаю..." |
Представим, что сегодня "2018-04-02", то есть это значение принимает функция CURDATE() - текущая дата . Требуется узнать, к каким категориям принадлежат объявления, срок публикации которых истекает сегодня. Названия категорий есть только в таблице CATEGORIES, а даты истечения срока публикации объявлений - только в таблице ADS. В таблице PARTS - части категорий (или проще, подкатегории) опубликованных объявлений. Но внешним ключом Cat_ID таблица PARTS связана с таблицей CATEGORIES, а таблица ADS связана внешним ключом Part_ID с таблицей PARTS. Поэтому соединяем в одном запросе три таблицы и этот запрос можно с максимальной корректностью назвать цепочкой.
Запрос будет следующим:
Результат запроса - таблица, содержащая названия двух категорий - "Недвижимость" и "Транспорт":
| Cat_name |
| Недвижимость |
| Транспорт |
Использование оператора SQL CROSS JOIN в наиболее простой форме - без условия соединения - реализует операцию декартова произведения в реляционной алгебре . Результатом такого соединения будет сцепление каждой строки первой таблицы с каждой строкой второй таблицы. Таблицы могут быть записаны в запросе либо через оператор CROSS JOIN, либо через запятую между ними.
Пример 9. База данных - всё та же, таблицы - Categories и Parts. Реализовать операцию декартова произведения этих двух таблиц.
Запрос будет следующим:
SELECT (*) Categories CROSS JOIN Parts
Или без явного указания CROSS JOIN - через запятую:
SELECT (*) Categories , Parts
Запрос вернёт таблицу из 5 * 5 = 25 строк, фрагмент которой приведён ниже:
| Cat_ID | Cat_name | Price | Part_ID | Part | Cat |
| 10 | Стройматериалы | 105,00 | 1 | Квартиры | 505 |
| 10 | Стройматериалы | 105,00 | 2 | Автомашины | 205 |
| 10 | Стройматериалы | 105,00 | 3 | Доски | 10 |
| 10 | Стройматериалы | 105,00 | 4 | Шкафы | 30 |
| 10 | Стройматериалы | 105,00 | 5 | Книги | 160 |
| ... | ... | ... | ... | ... | ... |
| 45 | Техника | 65,00 | 1 | Квартиры | 505 |
| 45 | Техника | 65,00 | 2 | Автомашины | 205 |
| 45 | Техника | 65,00 | 3 | Доски | 10 |
| 45 | Техника | 65,00 | 4 | Шкафы | 30 |
| 45 | Техника | 65,00 | 5 | Книги | 160 |
Как видно из примера, если результат такого запроса и имеет какую-либо ценность, то это, возможно, наглядная ценность в некоторых случаях, когда не требуется вывести структурированную информацию, тем более, даже самую простейшую аналитическую выборку. Кстати, можно указать выводимые столбцы из каждой таблицы, но и тогда информационная ценность такого запроса не повысится.
Но для CROSS JOIN можно задать условие соединения! Результат будет совсем иным. При использовании оператора "запятая" вместо явного указания CROSS JOIN условие соединения задаётся не словом ON, а словом WHERE.
Пример 10. Та же база данных портала объявлений, таблицы Categories и Parts. Используя перекрестное соединение, соединить таблицы так, чтобы данные полностью пересекались по условию. Условие - совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts.
Запрос будет следующим:
Запрос вернёт то же самое, что и запрос в примере 1:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
И это совпадение не случайно. Запрос c перекрестным соединением по условию соединения полностью аналогичен запросу с внутренним соединением - INNER JOIN - или, учитывая, что слово INNER - не обязательное, просто JOIN.
Таким образом, какой вариант запроса использовать - вопрос стиля или даже привычки специалиста по работе с базой данных. Возможно, перекрёстное соединение с условием для двух таблиц может представляться более компактным. Но преимущество перекрестного соединения для более чем двух таблиц (это также возможно) весьма спорно. В этом случае WHERE-условия пересечения перечисляются через слово AND. Такая конструкция может быть громоздкой и трудной для чтения, если в конце запроса есть также секция WHERE с условиями выборки.
Реляционные базы данных и язык SQL
9.5KВ этой статье мы расскажем об операторе LEFT JOIN MySQL и том, как применять его для запроса данных из двух или более таблиц базы данных.
Введение в LEFT JOIN MySQLОператор MySQL LEFT JOIN позволяет запрашивать данные из двух или более таблиц базы данных. Он является необязательной частью оператора SELECT , которая указывается после FROM .
Предположим, что вы собираетесь запрашивать данные из таблиц t1 и t2 . На примере приведенного ниже запроса мы проиллюстрируем синтаксис LEFT JOIN , объединяя две таблицы:
SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
Когда вы подключаетесь к таблицам t1 и t2 , используя оператор LEFT JOIN , если строка из левой таблицы t1 соответствует строке из правой таблицы t2 на основе условия соединения (t1.c1 = t2.c1 ), эта строка будет включена в результирующий набор.
Если значение в левой таблице не соответствует значению в правой таблице, строка в левой таблице также выбирается и объединяется с «условной » строкой из правой таблицы. «Условная » строка содержит NULL для всех соответствующих столбцов в операторе SELECT .
Другими словами, условие MySQL LEFT JOIN позволяет выбирать строки обеих таблиц, которые совпадают, плюс все строки из левой таблицы (t1 ) даже без совпадения со строками правой таблицы (t2 ).
Следующая диаграмма поможет представить, как работает условие LEFT JOIN . Пересечение двух кругов — это строки, которые соответствуют в обеих таблицах, а оставшаяся часть левого круга — это строки в таблице t1 , которые не имеют соответствующей строки в таблице t2 . Следовательно, все строки в левой таблице включены в результирующий набор.
Обратите внимание, что возвращаемые значения также должны соответствовать условиям в операторах WHERE и HAVING , если эти операторы доступны в запросе. Примеры использования LEFT JOIN Использование оператора MySQL LEFT JOIN для объединения двух таблиц
Возьмем две таблицы клиентов и заказов из демонстрационной базы данных :
В базе данных, описанной на диаграмме:
- Каждый заказ в таблице заказов должен принадлежать клиенту в таблице клиентов;
- Каждый клиент в таблице клиентов может иметь ноль или более заказов в таблице заказов.
Чтобы найти заказы, принадлежащие каждому клиенту, можно использовать MySQL LEFT JOIN пример:
SELECT c.customerNumber, c.customerName, orderNumber, o.status FROM customers c LEFT JOIN orders o ON c.customerNumber = o.customerNumber
Посмотреть пример
Левая таблица — это клиенты, поэтому все клиенты включены в результирующий набор. Но в нем есть строки, которые имеют данные клиента, но не имеют данных заказа, например. 168, 169 и т. д. Данные заказов в этих строках равны NULL . Это означает, что у этих клиентов нет заказов в соответствующей таблице.
Поскольку мы использовали одно и то же имя столбца (orderNumber ) для объединения двух таблиц, можно сделать запрос короче, используя приведенный ниже синтаксис:
SELECT c.customerNumber, customerName, orderNumber, status FROM customers c LEFT JOIN orders USING (customerNumber);
Если вы замените оператор MySQL SELECT LEFT JOIN оператором INNER JOIN , вы получите только клиентов, которые оформили хотя бы один заказ.
Использование оператора MySQL LEFT JOIN для поиска несовпадающих строкОператор LEFT JOIN может оказаться полезен, если вы хотите найти строки в левой таблице, которые не соответствуют строкам в правой. Чтобы найти несовпадающие строки двух таблиц, нужно добавить оператор WHERE в выборку SELECT для запроса только строк, значения столбцов которых в правой таблице содержат значение NULL .
Например, чтобы найти всех клиентов, которые не оформили ни одного заказа, используется следующий запрос:
Посмотреть пример
Условие в операторе WHERE или условие в операторе ONРассмотрим следующий пример использования MySQL LEFT JOIN WHERE :
В этом примере мы использовали оператор LEFT JOIN для запроса данных из таблиц orders и orderDetails . Запрос возвращает заказ и его данные, если они есть, для заказа 10123.
На протяжении немалого времени, в начале своей карьеры веб-разработчика, я работал с базой данный как умел, а умел я не многое. Составлял простые примитивные запросы, а порою даже вставлял запросы в циклы. На тот момент мне к сожалению не попалась в руки правильная книжка по mySQL и учить приходилось методом проб и ошибок. Множество статей в интернете как-то не сразу донесли до меня замечательный mySQL запрос — JOIN.
В этой публикации я расскажу о всех возможных вариантах работы с JOIN и более того, представлю принцип работы каждой команды — визуально.
Отдельно стоит отметить пункты 5,6 и 7. На самом деле эти запросы не соединяют две таблицы, а наоборот исключают из одной таблицы столбцы присутствующие в другой. На деле это может оказать очень полезным.
Inner JOIN
Один из самых распространенных запросов, встречается предельно часто. Этот запрос вернет все записи из левой таблицы (таб. А) и записи из (таб. В), но при этом возвращены будут только совпадающие столбцы.
Пример запроса:
Просмотр кода SQL
| SELECT < select_list> FROM Table_A A INNER JOIN Table_B B ON A. Key = B. Key |
Left JOIN
Данный запрос вернет все столбцы из левой таблицы (таб. А), а также все столбцы из правой таблицы (таб. В) но только которые совпадают со столбцами из левой таблицы.
Пример запроса:
Просмотр кода SQL
| SELECT < select_list> FROM Table_A A LEFT JOIN Table_B B ON A. Key = B. Key |
Right JOIN
Аналогичен предыдущему запросу, но уже вернет все столбцы из правой таблицы (таб. В), а также все столбцы из левой таблицы (таб. А) которые совпадают со столбцами из правой таблицы.
Пример запроса:
Просмотр кода SQL
| SELECT < select_list> FROM Table_A A RIGHT JOIN Table_B B ON A. Key = B. Key |
Outer JOIN
Часто данный запрос записывают как FULL OUTER JOIN или FULL JOIN, все вариации выполняют одно действие, а именно возвращают все столбцы из обоих таблиц, при этом совпадающие столбцы будут перекрыты столбцами из левой таблицы.
Пример запроса:
Просмотр кода SQL
| SELECT < select_list> FROM Table_A A FULL OUTER JOIN Table_B B ON A. Key = B. Key |
Left Excluding JOIN
Этот запрос вернет все столбцы из левой таблицы (таб. А), которые не совпадают со столбцами из правой таблицы (таб. В).
Пример запроса:
Просмотр кода SQL
Поводом для написания данной статьи послужили некоторые дебаты в одной из групп linkedin, связанной с MySQL, а также общение с коллегами и хабролюдьми:-)
В данной статье хотел написать что такое вообще JOINы в MySQL и как можно оптимизировать запросы с ними.
Что такое JOINы в MySQLВ MySQL термин JOIN используется гораздо шире, чем можно было бы предположить. Здесь JOINом может называться не только запрос объединяющий результаты из нескольких таблиц, но и запрос к одной таблице, например, SELECT по одной таблице - это тоже джоин.
Все потому, что алгоритм выполнения джоинов в MySQL реализован с использованием вложенных циклов. Т.е. каждый последующий JOIN это дополнительный вложенный цикл. Чтобы выполнить запрос и вернуть все записи удовлетворяющие условию MySQL выполняет цикл и пробегает по записям первой таблицы параллельно проверяя соответствия условиям описанных в теле запроса, когда находятся записи, удовлетворяющие условиям - во вложенном цикле по второй таблице ищутся записи соответствующие первым и удовлетворяющие условиям проверки и т.д.
Прмер обычного запроса с INNER JOIN
SELECT*
FROM
Table1
INNER JOIN
Table2 ON P1(Table1,Table2)
INNER JOIN
Table3 ON P2(Table2,Table3)
WHERE
P(Table1,Table2,Table3).
Где Р - условия склейки таблиц и фильтры в WHERE условии.
Можно представить такой псевдокод выполнения такого запроса.
FOR each row t1 in Table1 {IF(P(t1)) {
FOR each row t2 in Table2 {
IF(P(t2)) {
FOR each row t3 in Table3 {
IF P(t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
}
}
* This source code was highlighted with Source Code Highlighter .
Где конструкция t1||t2||t3 означает конкатенацию столбцов из разных таблиц.
Если в запросе встречаются OUTER JOINs, например, LEFT OUTER JOIN
SELECT*
FROM
Table1
LEFT JOIN
Table2 LEFT JOIN Table3 ON P2(Table2,Table3)
ON P1(Table1,Table2)
WHERE
P(Table1,Table2,Tabke3)
* This source code was highlighted with Source Code Highlighter .
То алгоритм выполнения этого запроса MySQL будет выглядеть как-то так
FOR each row t1 in T1 {BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
BOOL f2:=FALSE;
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF P(t1,t2,NULL) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
* This source code was highlighted with Source Code Highlighter .
Итак, как мы видим, JOINы это просто группа вложенных циклов. Так почему же в MySQL и UNION и SELECT и запросы с SUBQUERY тоже джоины?
MySQL оптимизатор старается приводить запросы к тому виду к которому ему удобней обрабатывать и выполнять запросы по стандартной схеме.
С SELECT все понятно - просто цикл без вложенных циклов. Все UNION выполняются как отдельные запросы и результаты складываются во временную таблицу, и потом MySQL работает уже с этой таблицей, т.е. проходясь циклом по записям в ней. С Subquery та же история.
Приводя все к одному шаблону, например, МySQL переписывает все RIGHT JOIN запросы на LEFT JOIN эквиваленты.
Но стратегия выполнения запросов через вложенные циклы накладывает некоторые ограничения, например, в связи с такой схемой MySQL не поддерживает выполнение FULL OUTER JOIN запросов.
Но результат такого запроса можно получить с помощью UNION двух запросов на LEFT JOIN и на RIGHT JOIN
Пример самого запроса можно посмотреть по ссылке на вики.
В отличии от других СУРБД MySQL не генерирует байткод для выполнения запроса, вместо этого MySQL генерирует список инструкций в древовидной форме, которых придерживается engine выполнения запроса выполняя запрос.
Это дерево имеет следующий вид и имеет название «left-deep tree»
В отличии от сбалансированных деревьев (Bushy plan), которые применяются в других СУБД (например Oracle)
Теперь перейдем к самому интересному - к оптимизации джоинов.
MySQL оптимизатор, а именно та его часть, которая отвечает за оптимизацию JOIN-ов выбирает порядок в котором будет производиться склейка имеющихся таблиц, т.к. можно получить один и тот же результат (датасет) при различном порядке таблиц в склейке. MySQL оптимизатор оценивает стоимость различных планов и выбирает с наименьшей стоимостью. Единицей оценки является операция единичного чтения страницы данных размером в 4 килобайта из произвольного места на диске.
Для выбранного плана можно узнать стоимость путем выполнения команды
SHOW SESSION STATUS LIKE "Last_query_cost";
Оценка основана на статистике: количество страниц памяти, занимаемое таблицей и/или индексами для этой таблицы, cardinality (число уникальных значений) индексов, длинна записей и индексов, их распределение и т.д. Во время своей оценки оптимизатор не рассчитывает на то, что какие-то части попадут в кеш, оптимизатор предполагает, что каждая операция чтения это обращение к диску.
Иногда анализатор-оптимизатор не может проанализировать все возможные планы выполнения и выбирает неправильный. Например, если у нас INNER JOIN по 3м таблицам, то возможных вариантов у анализатора - 3! = 6, а если у нас склейка по 10 таблицам, то тут возможных вариантов уже 10! = 3628800… MySQL не может проанализировать столько вариантов, поэтому в таком случае он использует алгоритм "жадного " поиска.
Но не стоит применять этот хак ко всем запросам, расчитывая произвести оптимизацию на спичках и сэкономить время на составление плана выполнения запроса оптимизатором и добавлять STRAIGH_JOIN ко всем запросам с джоинами , т.к. данные меняются и склейка, которая оптимальна сейчас может перестать быть оптимальной со временем, и тогда запросы начнуть очень сильно лагать.
Также, как уже говорилось выше, результаты джоинов помещаются во временные таблицы, поэтому зачастую уместно применять «derived table» в котором мы накладываем все необходимые нам условия на выборку, а также указываем LIMIT и порядок сортировки. В данном случае мы избавимся от избыточности данных во временной таблице, а также проведем сортировку на раннем этапе (по результату одной выборки, а не финальной склейки, что уменьшит размеры записей которые будут сортироваться).
Стандартный пример подхода описанного выше. Простая выборка для отношения много к многим: новости и теги к ним.
SELECTt.tid, t.description, n.nid, n.title, n.extract , n.modtime
FROM
SELECT
n.nid
FROM
news n
WHERE
n.type = 1321
AND n.published = 1
AND status = 1
ORDER BY
n.modtime DESC
LIMIT
200
) as news
INNER JOIN
news n ON n.nid = news.nid
INNER JOIN
news_tag nt ON n.nid = nt.nid
INNER JOIN
tags t ON nt.tid = t.tid
* This source code was highlighted with Source Code Highlighter .
Ну и на последок небольшая задачка, которую я иногда задаю на собеседованиях:-)
Есть новостной блоггерный сайт. Есть такие сущности как новости и комментарии к ним.
Задача - нужно написать запрос, который выводит список из 10 новостей определенного типа (задается пользователем) отсортированные по времени издания в хронологическом порядке, а также к каждой из этих новостей показать не более 10 последних коментариев, т.е. если коментариев больше - показываем только последние 10.
Все нужно сделать одним запросом. Да, это, может, и не самый лучший способ, и вы вольны предложить другое решение:-)
Теги:
- MySQL
- mysql performance
- JOIN