Как джойнить таблицу саму на себя
До этого все наши запросы обращались только к одной таблице. Однако запросы могут также обращаться сразу к нескольким таблицам или обращаться к той же таблице так, что одновременно будут обрабатываться разные наборы её строк. Запрос, обращающийся к разным наборам строк одной или нескольких таблиц, называется соединением (JOIN). Например, мы захотели перечислить все погодные события вместе с координатами соответствующих городов. Для этого мы должны сравнить столбец city каждой строки таблицы weather со столбцом name всех строк таблицы cities и выбрать пары строк, для которых эти значения совпадают.
Примечание
Это не совсем точная модель. Обычно соединения выполняются эффективнее (сравниваются не все возможные пары строк), но это скрыто от пользователя.
Это можно сделать с помощью следующего запроса:
SELECT * FROM weather, cities WHERE city = name;
city |temp_lo|temp_hi| prcp| date | name | location --------------+-------+-------+-----+-----------+--------------+---------- San Francisco| 46| 50| 0.25| 1994-11-27| San Francisco| (-194,53) San Francisco| 43| 57| 0| 1994-11-29| San Francisco| (-194,53) (2 rows)
Обратите внимание на две особенности полученных данных:
В результате нет строки с городом Хейуорд (Hayward). Так получилось потому, что в таблице cities нет строки для данного города, а при соединении все строки таблицы weather , для которых не нашлось соответствие, опускаются. Вскоре мы увидим, как это можно исправить.
Название города оказалось в двух столбцах. Это правильно и объясняется тем, что столбцы таблиц weather и cities были объединены. Хотя на практике это нежелательно, поэтому лучше перечислить нужные столбцы явно, а не использовать * :
SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
Упражнение: Попробуйте определить, что будет делать этот запрос без предложения WHERE .
Так как все столбцы имеют разные имена, анализатор запроса автоматически понимает, к какой таблице они относятся. Если бы имена столбцов в двух таблицах повторялись, вам пришлось бы дополнить имена столбцов, конкретизируя, что именно вы имели в виду:
SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location FROM weather, cities WHERE cities.name = weather.city;
Вообще хорошим стилем считается указывать полные имена столбцов в запросе соединения, чтобы запрос не поломался, если позже в таблицы будут добавлены столбцы с повторяющимися именами.
Запросы соединения, которые вы видели до этого, можно также записать в другом виде:
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
Эта запись не так распространена, как первый вариант, но мы показываем её, чтобы вам было проще понять следующие темы.
Сейчас мы выясним, как вернуть записи о погоде в городе Хейуорд. Мы хотим, чтобы запрос просканировал таблицу weather и для каждой её строки нашёл соответствующую строку в таблице cities . Если же такая строка не будет найдена, мы хотим, чтобы вместо значений столбцов из таблицы cities были подставлены « пустые значения » . Запросы такого типа называются внешними соединениями. (Соединения, которые мы видели до этого, называются внутренними.) Эта команда будет выглядеть так:
SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name); city |temp_lo|temp_hi| prcp| date | name | location --------------+-------+-------+-----+-----------+--------------+---------- Hayward | 37| 54| | 1994-11-29| | San Francisco| 46| 50| 0.25| 1994-11-27| San Francisco| (-194,53) San Francisco| 43| 57| 0| 1994-11-29| San Francisco| (-194,53) (3 rows)
Этот запрос называется левым внешним соединением, потому что из таблицы в левой части оператора будут выбраны все строки, а из таблицы справа только те, которые удалось сопоставить каким-нибудь строкам из левой. При выводе строк левой таблицы, для которых не удалось найти соответствия в правой, вместо столбцов правой таблицы подставляются пустые значения (NULL).
Упражнение: Существуют также правые внешние соединения и полные внешние соединения. Попробуйте выяснить, что они собой представляют.
В соединении мы также можем замкнуть таблицу на себя. Это называется замкнутым соединением. Например, представьте, что мы хотим найти все записи погоды, в которых температура лежит в диапазоне температур других записей. Для этого мы должны сравнить столбцы temp_lo и temp_hi каждой строки таблицы weather со столбцами temp_lo и temp_hi другого набора строк weather . Это можно сделать с помощью следующего запроса:
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi >W2.temp_hi; city | low | high | city | low | high ---------------+-----+------+---------------+-----+------ San Francisco | 43 | 57 | San Francisco | 46 | 50 Hayward | 37 | 54 | San Francisco | 46 | 50 (2 rows)
Здесь мы ввели новые обозначения таблицы weather: W1 и W2 , чтобы можно было различить левую и правую стороны соединения. Вы можете использовать подобные псевдонимы и в других запросах для сокращения:
SELECT * FROM weather w, cities c WHERE w.city = c.name;
Вы будете встречать сокращения такого рода довольно часто.
| Пред. | Наверх | След. |
| 2.5. Выполнение запроса | Начало | 2.7. Агрегатные функции |
JOIN
JOIN — это команда в языке запросов SQL (Structured Query Language), необходимом для работы с базами данных. Объединяет данные из двух разных таблиц в базе. Цель использования команды — получить нужное подмножество данных.
Освойте профессию
«Fullstack-разработчик на Python»
Командой JOIN в SQL пользуются очень часто, она одна из наиболее важных. В реляционных базах данных информация распределена по таблицам, а большая часть работы с базами подразумевает поиск в них нужных сведений. Для этого используются разные команды, и JOIN — одна из них.
Применение команды можно сравнить с использованием фильтра по товарам в интернет-магазине: разработчик выбирает определенное подмножество с помощью настроенных запросов. Обычно JOIN используется в блоках SELECT, которые «выбирают» из базы таблицы и записи, отвечающие нужным критериям.
Науки о данных
Онлайн-магистратура совместно с МФТИ. Погрузитесь в мир Data Science и постройте карьеру в Big Data, Artificial Intelligence или Machine Learning. Получите опыт на реальных проектах и выйдите на новый уровень в профессии и карьере.
Кто пользуется оператором JOIN
В основном SQL-запросы используют администраторы баз данных и бэкенд-разработчики — люди, работа которых подразумевает получение сведений из базы. Но понимание работы JOIN важно и представителям других профессий, так как базовые знания SQL необходимы в любом проекте, — например, сотрудникам технической поддержки, контент-менеджерам, SEO-специалистам и другим.
Читайте также Востребованные IT-профессии 2023 года: на кого учиться онлайн
Для чего нужен оператор JOIN
- Для «умного» поиска по таблицам в рамках одной базы данных. Использование разных режимов работы оператора помогает отфильтровать сведения по определенным критериям.
- Для быстрого отсечения информации, которая не нужна в рамках конкретного запроса.
- Для соединения двух таблиц в одну, например, чтобы отправить полученную информацию единым блоком.
Как работает JOIN
Изначально JOIN — бинарный оператор, то есть он работает с двумя переданными объектами. На практике современные реализации могут воспринимать и больше двух таблиц, просто операция в таком случае выполняется несколько раз.
Оператору передаются таблицы, которые нужно объединить, и критерий для объединения — логическое выражение, которое называется ключом. В процессе работы JOIN таблицы проверяются на соответствие этому критерию. Например, значение поля ID в одной таблице должно соответствовать полю ID в другом. Оператор проверит строки обеих таблиц и выберет пары строк, где ID совпадают.
Найденные результаты объединяются в одну таблицу. Две соответствующие друг другу разные строки преобразуются в одну — это важнейшее условие работы JOIN. Строки чаще всего из разных таблиц, но это не обязательно. Главная особенность JOIN — объединение двух объектов в один.
Начните карьеру в Data Science.
Онлайн-магистратура МФТИ с практикой на реальных проектах
Варианты работы команды JOIN
Выше мы написали, что у JOIN есть разные режимы работы. Они дают разные результаты; различаются и ситуации, в которых они могут пригодиться. Чаще всего выделяют четыре режима SQL JOIN: Inner, Outer, Self и Cross.
Особенности работы легче всего объяснить с помощью диаграмм Венна, которые также называют кругами Эйлера. На них множества представляются как круги, а объекты, которые относятся к обоим множествам, — как пересечения этих кругов.
Inner Join
Это самый простой и часто используемый вариант команды — внутреннее объединение. Если режим работы операции не указан вручную, то SQL автоматически воспримет любой JOIN как внутренний.
Оператору передаются две таблицы, и он возвращает их внутреннее пересечение по какому-либо критерию. Результатом будут записи, которые соответствуют обеим таблицам, — их перед отправкой объединят.
Например, если в одной таблице будут перечислены черные животные, а в другой — собаки, то Inner Join вернет одну таблицу с перечислением черных собак. Столбцы будут «склеены» друг с другом, несмотря на то что в базе данные хранятся в разных таблицах. Это похоже на бинарное «и» из алгебры логики.
Внутреннее соединение используется чаще всего.
Станьте дата-сайентистом и решайте амбициозные задачи с помощью нейросетей
Outer Join
Второй распространенный вариант — внешнее соединение. Если внутреннее объединение имеет сходство с бинарным «и», то внешнее — несколько вариаций бинарного «или». Такой JOIN более гибкий, он возвращает не только строгое пересечение между двумя таблицами, но и отдельные элементы, которые принадлежат только одному из множеств. Какому — зависит от типа.
Left Join. Возвращает пересечение множеств и все элементы из левой таблицы. Например, человек хочет посмотреть кино, но на русский фильм согласен, только если это боевик. Фильтр вернет ему все фильмы из множества «боевики», фильмы из подмножества «русские боевики», но других фильмов из множества «русские» там не будет.
Right Join. Работает по тому же принципу, но вместо левой таблицы — правая. То есть человек получит в результатах боевики, только если они русские.
Join Full. Возвращает обе таблицы, объединенные в одну. Например, человек хочет увидеть список из всех боевиков и всех русских фильмов, без исключений.
Outer Join с NULL
Это не отдельный метод, мы описали его отдельно от остальных только для наглядности. Это тот же самый Outer Join, но с дополнительным параметром, который убирает из результатов поиска пересечение категорий. Это противоположность Inner Join.
Left Join с NULL. Возвращает данные из левой таблицы, но без пересечений с правой. Человеку покажутся все боевики, но русского кино и в частности русских боевиков среди них не будет.
Right Join с NULL. Соответственно, работает так же, но по отношению к «правой», второй таблице.
Join Full с NULL. Работает как исключающее «или». Он тоже возвращает результат из обеих таблиц, кроме пересечений. Покажутся все русские фильмы и все боевики, а вот кино из подмножества «русские боевики» в результате не будет.
Как и с любым JOIN, результаты перед отправкой объединяются в одну таблицу.
Cross Join
Его еще называют перекрестным. Это своеобразный вариант соединения, который нужен не так часто, но важен для понимания. Он возвращает декартово произведение — собираются все возможные пары из обеих таблиц. В отличие от остальных режимов, Cross Join не требует указания дополнительной информации.
Стандартное обращение к двум таблицам тоже покажет декартово произведение. Cross Join отличается от простого вызова двух таблиц тем, что они объединяются в одну.
В виде диаграммы Венна это соединение представить невозможно. Скорее можно объяснить его на примере математики. Например, в одном наборе десять чисел, в другом — пять. Cross Join между этими наборами — это все возможные комбинации сумм двух чисел из разных наборов. Результат будет выглядеть как таблица 11×5, где в каждой ячейке лежит своя сумма.
В разработке Cross Join может использоваться при создании тех же фильтров в интернет-магазине. Например, человек ищет обувь по характеристикам «тип» и «размер» — должны быть выведены все возможные комбинации типа с размером.
Self Join
Это «самосоединение», объединение внутри одной таблицы. Оно используется тогда, когда у разных полей одной таблицы могут быть одинаковые значения. Например, один и тот же участник музыкальной группы может быть и вокалистом, и, например, клавишником. Если из базы музыкальных групп понадобится извлечь те, где вокалист и клавишник — одно лицо, потребуется Self Join.
Эта вариация может быть и внутренней, и внешней. Ее отличие в том, что таблица при таком режиме присоединяется сама к себе. Без практики это может быть непривычно, но в процессе использования логику работы легко понять.
Чтобы Self Join работал правильно, могут потребоваться псевдонимы таблиц: они помогают называть одну и ту же таблицу разными именами. В результате оператор «воспринимает» переданные сущности как разные.
Как начать работать с SQL-запросами
Для начала работы с SQL-запросами требуется СУБД — система для управления базами данных. Начинающие обычно выбирают MySQL: она простая в освоении, мало весит и распространяется бесплатно.
С помощью СУБД можно создать базу и управлять ей, модифицировать данные и пр. Можно работать с разными операциями, включая JOIN. Удаленный сервер не обязателен: есть программные решения, позволяющие «поднять» серверную часть на том же устройстве. На «сервере» находится хранилище данных, а запросы поступают от «клиента».
Fullstack-разработчик на Python
Fullstack-разработчики могут в одиночку сделать IT-проект от архитектуры до интерфейса. Их навыки востребованы у работодателей, особенно в стартапах. Научитесь программировать на Python и JavaScript и создавайте сервисы с нуля.
Статьи по теме:
- База данных
- Вышел Python 3.12 — что добавили и удалили новой версии
Соединение таблиц – операция JOIN и ее виды
Говоря про соединение таблиц в SQL, обычно подразумевают один из видов операции JOIN. Не стоит путать с объединением таблиц через операцию UNION. В этой статье я постараюсь простыми словами рассказать именно про соединение, чтобы после ее прочтения Вы могли использовать джойны в работе и не допускать грубых ошибок.
Соединение – это операция, когда таблицы сравниваются между собой построчно и появляется возможность вывода столбцов из всех таблиц, участвующих в соединении.
Придумаем 2 таблицы, на которых будем тренироваться.
Таблица «Сотрудники», содержит поля:
Таблица «Отделы», содержит поля:
Давайте уже быстрее что-нибудь покодим.
INNER JOIN
Самый простой вид соединения INNER JOIN – внутреннее соединение. Этот вид джойна выведет только те строки, если условие соединения выполняется (является истинным, т.е. TRUE). В запросах необязательно прописывать INNER – если написать только JOIN, то СУБД по умолчанию выполнить именно внутреннее соединение.
Давайте соединим таблицы из нашего примера, чтобы ответить на вопрос, в каких отделах работают сотрудники (читайте комментарии в запросе для понимания синтаксиса).
SELECT -- Перечисляем столбцы, которые хотим вывести Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел -- выводим наименование отдела и переименовываем столбец через as FROM -- таблицы для соединения перечисляем в предложении from Сотрудники -- обратите внимание, что мы не указали вид соединения, поэтому выполнится внутренний (inner) джойн JOIN Отделы -- условия соединения прописываются после ON -- условий может быть несколько, записанных через and, or и т.п. ON Сотрудники.Отдел = Отделы.id
Получим следующий результат:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 4 | Светлана | Бар |
Из результатов пропал сотрудник Алексей (id = 3), потому что условие «Сотрудники.Отдел = Отделы.id» не будет истинно для этой сроки из таблицы «Сотрудники» с каждой строкой из таблицы «Отделы». По той же логике в результате нет отдела «Администрация». Попробую это визуализировать (зеленные линии – условие TRUE, иначе линия красная):
Если не углубляться в то, как внутреннее соединение работает под капотом СУБД, то происходит примерно следующее:
- Каждая строка из одной таблицы сравнивается с каждой строкой из другой таблицы
- Строка возвращается, если условие сравнения является истинным
Если для одной или нескольких срок из левой таблицы (в рассмотренном примере левой таблицей является «Сотрудники», а правой «Отделы») истинным условием соединения будут являться одна или несколько срок из правой таблицы, то строки умножат друг друга (повторятся). В нашем примере так произошло для отдела с поэтому строка из таблицы «Отделы» повторилась дважды для Федора и Светланы.
Перемножение таблиц проще ощутить на таком примере, где условие соединения будет всегда возвращать TRUE, например 1=1:
SELECT * FROM Сотрудники JOIN Отделы ON 1=1
В результате получится 12 строк (4 сотрудника * 3 отдела), где для каждого сотрудника подтянется каждый отдел.
Также хочу сразу отметить, что в соединении может участвовать сколько угодно таблиц, можно таблицу соединить даже саму с собой (в аналитических задачах это не редкость). Какая из таблиц будет правой или левой не имеется значения для INNER JOIN (для внешних соединений типа LEFT JOIN или RIGHT JOIN это важно. Читайте далее). Пример соединения 4-х таблиц:
SELECT * FROM Table_1 JOIN Table_2 ON Table_1.Column_1 = Table_2.Column_1 JOIN Table_3 ON Table_1.Column_1 = Table_3.Column_1 AND Table_2.Column_1 = Table_3.Column_1 JOIN Table_1 AS Tbl_1 -- Задаем алиас для таблицы, чтобы избежать неоднозначности -- Если в Table_1.Column_1 хранится порядковый номер какого-то объекта, -- то так можно присоединить следующий по порядку объект ON Table_1.Column_1 = Tbl_1.Column_1 + 1
Как видите, все просто, прописываем новый джойн после завершения условий предыдущего соединения. Обратите внимание, что для Table_3 указано несколько условий соединения с двумя разными таблицами, а также Table_1 соединяется сама с собой по условию с использованием сложения.
Строки, которые выведутся запросом, должны совпасть по всем условиям. Например:
- Строка из Table_1 соединилась со строкой из Table_2 по условию первого JOIN. Давайте назовем ее «объединенной строкой» из двух таблиц;
- Объединенная строка успешно соединилась с Table_3 по условию второго JOIN и теперь состоит из трех таблиц;
- Для объединенной строки не нашлось строки из Table_1 по условию третьего JOIN, поэтому она не выводится вообще.
На этом про внутреннее соединение и логику соединения таблиц в SQL – всё. Если остались неясности, то спрашивайте в комментариях.
Далее рассмотрим отличия остальных видов джойнов.
LEFT JOIN и RIGHT JOIN
Левое и правое соединения еще называют внешними. Главное их отличие от внутреннего соединения в том, что строка из левой (для LEFT JOIN) или из правой таблицы (для RIGHT JOIN) попадет в результаты в любом случае. Давайте до конца определимся с тем, какая таблица левая, а какая правая.
Левая таблица та, которая идет перед написанием ключевых слов [LEFT | RIGHT| INNER] JOIN, правая таблица – после них:
SELECT * FROM Левая_таблица AS lt LEFT JOIN Правая_таблица AS rt ON lt.c = rt.c
Теперь изменим наш SQL-запрос из самого первого примера так, чтобы ответить на вопрос «В каких отделах работают сотрудники, а также показать тех, кто не распределен ни в один отдел?»:
SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Сотрудники LEFT JOIN Отделы -- добавляем только left ON Сотрудники.Отдел = Отделы.id
Результат запроса будет следующим:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 3 | Алексей | NULL |
| 4 | Светлана | Бар |
Как видите, запрос вернул все строки из левой таблицы «Сотрудники», дополнив их значениями из правой таблицы «Отделы». А вот строка для отдела «Администрация» не показана, т.к. для нее не нашлось совпадений слева.
Это мы рассмотрели пример для левого внешнего соединения. Для RIGHT JOIN будет все тоже самое, только вернутся все строки из таблицы «Отделы»:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 4 | Светлана | Бар |
| NULL | NULL | Администрация |
Алексей «потерялся», Администрация «нашлась».
Вопрос для Вас. Что надо изменить в последнем приведенном SQL-запросе, чтобы результат остался тем же, но вместо LEFT JOIN, использовался RIGHT JOIN?
Ответ. Нужно поменять таблицы местами:
SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Отделы RIGHT JOIN Сотрудники ON Сотрудники.Отдел = Отделы.id
В одном запросе можно применять и внутренние соединения, и внешние одновременно, главное соблюдать порядок таблиц, чтобы не потерять часть записей (строк).
FULL JOIN
Еще один вид соединения, который осталось рассмотреть – полное внешнее соединение.
Этот вид джойна вернет все строки из всех таблиц, участвующих в соединении, соединив между собой те, которые подошли под условие ON.
Давайте посмотрим всех сотрудников и все отделы из наших тестовых таблиц:
SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Сотрудники FULL JOIN Отделы ON Сотрудники.Отдел = Отделы.id
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 3 | Алексей | NULL |
| 4 | Светлана | Бар |
| NULL | NULL | Администрация |
Теперь мы видим все, даже Алексея без отдела и Администрацию без сотрудников.
Вместо заключения
Помните о порядке выполнения соединений и порядке таблиц, если используете несколько соединений и используете внешние соединения. Можно выполнять LEFT JOIN для сохранения всех строк из самой первой таблицы, а последним внутренним соединением потерять часть данных. На маленьких таблицах косяк заметить легко, на огромных очень тяжело, поэтому будьте внимательны.
Рассмотрим последний пример и введем еще одну таблицу «Банки», в которой обслуживаются наши придуманные сотрудники:
| id | Наименование |
|---|---|
| 1 | Банк №1 |
| 2 | Лучший банк |
| 3 | Банк Лидер |
В таблицу «Сотрудники» добавим столбец «Банк»:
| id | Имя | Отдел | Банк |
|---|---|---|---|
| 1 | Юлия | 1 | 2 |
| 2 | Федор | 2 | 2 |
| 3 | Алексей | NULL | 3 |
| 4 | Светлана | 2 | 4 |
Теперь выполним такой запрос:
SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел, Банки.Наименование AS Банк FROM Сотрудники LEFT JOIN Отделы ON Сотрудники.Отдел = Отделы.id INNER JOIN Банки ON Сотрудники.Банк = Банки.id
В результате потеряли информацию о Светлане, т.к. для нее не нашлось банка с (такое происходит из-за неправильной проектировки БД):
| id | Имя | Отдел | Банк |
|---|---|---|---|
| 1 | Юлия | Кухня | Лучший банк |
| 2 | Федор | Бар | Лучший банк |
| 3 | Алексей | NULL | Банк Лидер |
Хочу обратить внимание на то, что любое сравнение с неизвестным значением никогда не будет истинным (даже NULL = NULL). Эту грубую ошибку часто допускают начинающие специалисты. Подробнее читайте в статье про значение NULL в SQL.
Пройдите мой тест на знание основ SQL. В нем есть задания на соединения таблиц, которые помогут закрепить материал.
Дополнить Ваше понимание соединений в SQL могут схемы, изображенные с помощью кругов Эйлера. В интернете много примеров в виде картинок.
Если какие нюансы джойнов остались не раскрытыми, или что-то описано не совсем понятно, что-то надо дополнить, то пишите в комментариях. Буду только рад вопросам и предложениям.
Привожу простыню запросов, чтобы Вы могли попрактиковаться на легких примерах, рассмотренных в статье:
-- Создаем CTE для таблиц из примеров WITH Сотрудники AS( SELECT 1 AS id, 'Юлия' AS Имя, 1 AS Отдел, 2 AS Банк UNION ALL SELECT 2, 'Федор', 2, 2 UNION ALL SELECT 3, 'Алексей', NULL, 3 UNION ALL SELECT 4, 'Светлана', 2, 4 ), Отделы AS( SELECT 1 AS id, 'Кухня' AS Наименование UNION ALL SELECT 2, 'Бар' UNION ALL SELECT 3, 'Администрация' ), Банки AS( SELECT 1 AS id, 'Банк №1' AS Наименование UNION ALL SELECT 2, 'Лучший банк' UNION ALL SELECT 3, 'Банк Лидер' ) -- Если надо выполнить другие запросы, то сначала закоментируй это запрос с помощью /**/, -- а нужный запрос расскоментируй или напиши свой. -- Это пример внутреннего соединения SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Сотрудники JOIN Отделы ON Сотрудники.Отдел = Отделы.id /* -- Пример левого джойна SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Сотрудники LEFT JOIN Отделы ON Сотрудники.Отдел = Отделы.id */ /* -- Результат этого запроса будет аналогичен результату запроса выше, хотя соединение отличается SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Отделы RIGHT JOIN Сотрудники ON Сотрудники.Отдел = Отделы.id */ /* -- Правое соединение SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Сотрудники RIGHT JOIN Отделы ON Сотрудники.Отдел = Отделы.id */ /* -- Пример с использованием разных видов JOIN SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Отделы RIGHT JOIN Сотрудники ON Сотрудники.Отдел = Отделы.id LEFT JOIN Банки ON Банки.id = Сотрудники.Банк */ /* -- Полное внешние соединение SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Сотрудники FULL JOIN Отделы ON Сотрудники.Отдел = Отделы.id */ /* -- Пример с потерей строки из-за последнего внутреннего соединения SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел, Банки.Наименование AS Банк FROM Сотрудники LEFT JOIN Отделы ON Сотрудники.Отдел = Отделы.id INNER JOIN Банки ON Сотрудники.Банк = Банки.id */ /* -- Запрос с условием, которое всегда будет True SELECT * FROM Сотрудники JOIN Отделы ON 1=1 */
- Объединение таблиц – UNION
- Соединение таблиц – операция JOIN и ее виды
- Тест на знание основ SQL
Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.
Объединение таблиц при запросе (JOIN) в SQL
С помощью команды SELECT можно выбрать данные не только из одной таблицы, но и нескольких. Такая задача появляется довольно часто, потому что принято разносить данные по разным таблицам в зависимости от хранимой в них информации. К примеру, в одной таблице хранится информация о пользователях, во второй таблице о должностях компании, а в третьей и четвёртой о поставщиках и клиентах. Данные разбивают на таблицы так, чтобы с одной стороны получить самую высокую скорость выполнения запроса, а с другой стороны избежать ненужных объединений, которые снижают производительность.
Чем больше столбцов в таблице — тем сильнее падает скорость выборки из неё. Поэтому стараются делать в каждой таблице не больше 5-10 столбцов. Но чем сильнее данные разбиваются на разные таблицы, тем больше придётся делать объединений внутри запросов, что тоже снизит скорость получения выборки и увеличит нагрузку на базу.
Приведём пример запроса с объединением данных из двух таблиц. Для этого предположим, что существует две таблицы. Первая таблица будет иметь название USERS и будет иметь два столбца: ID и именами пользователей:
+-----------+ | USERS | +-----------+ | ID | NAME | +----+------+ | 1 | Мышь | +----+------+ | 2 | Кот | +----+------+
Вторая таблица будет называться FOOD и будет содержать два столбца: USER_ID и NAME. В этой таблице будет содержаться список любимых блюд пользователей из первой таблицы. В столбце USER_ID содержится ID пользователя, а в столбце PRODUCT находится название любимого блюда.
+-------------------+ | FOOD | +-------------------+ | USER_ID | PRODUCT | +---------+---------+ | 1 | Сыр | +---------+---------+ | 2 | Молоко | +---------+---------+
Условимся что поле ID в таблице USERS и поле USER_ID в таблице FOOD являются первичными ключами (то есть имеют уникальные значения, которые не повторяются). Теперь попробуем использовать логику и найти любимое блюдо пользователя «Мышь», используя обе таблицы. Для этого мы сначала посмотрим в первую таблицу и найдём ID пользователя под именем «Мышь», а затем найдём название продукта под таким же ID во второй таблице. Объединяющие SQL запросы работают по такой же логике: нужен столбец, в по которому таблицы могут быть объединены.
Продемонстрируем запрос, объединяющий таблицы по столбцам ID и USER_ID:
SELECT * FROM `USERS` INNER JOIN `FOOD` ON `USERS`.`ID`=`FOOD`.`USER_ID`;Разберём команду по словам. Начинается она как обычная выборка из одной таблицы со слов «SELECT * FROM USERS». Но затем идёт слово INNER, которое означает тип объединения. Существует три типа объединения таблиц: INNER, LEFT, RIGHT. Все они связаны с тем, что некоторым строкам в одной таблице может не найтись соответствующей строки во второй таблице. В таком случае при использовании «INNER» из результатов запроса будет удалены все строки, которым не нашлась соответствующая пара в другой таблице. Если же использовать вместо «INNER» слово «LEFT» или «RIGHT», то будут удалены строки, которые не нашли совпадение из первой (левой) или второй (правой) таблицы.
После слова «INNER» стоит слово «JOIN» (которое переводится с английского как «ПРИСОЕДИНИТЬ»). После слова «JOIN» стоит название таблицы, которая будет присоединена. В нашем случае это таблица FOOD. После названия таблицы стоит слово «ON» и равенство USERS.ID=FOOD.USER_ID, которое задаёт правило присоединения. При выполнении выборки будут объединены две таблицы так, чтобы значения в столбце ID таблицы USERS равнялось значению USER_ID таблицы FOOD.
В результате выполнения этого SQL запроса мы получим таблицу с четырьмя столбцами:
+----+------+---------+---------+ | ID | NAME | USER_ID | PRODUCT | +----+------+---------+---------+ | 1 | Мышь | 1 | Сыр | +----+------+---------+---------+ | 2 | Кот | 2 | Молоко | +----+------+---------+---------+
Предлагаем модифицировать запрос, потому что нам не нужны все четыре столбца. Уберём столбцы ID и USER_ID. Для этого вместо * в команде SELECT поставим название столбцов. Но необходимо сделать это, ставя сначала название таблицы и через точку название столбца. Чтобы получилось так:
SELECT `USERS`.`NAME`, `FOOD`.`PRODUCT` FROM `USERS` INNER JOIN `FOOD` ON `USERS`.`ID`=`FOOD`.`USER_ID`;Теперь результат будет компактнее. И благодаря уменьшенному количеству запрашиваемых данных, результат будет получаться из базы быстрее:
+------+---------+ | NAME | PRODUCT | +------+---------+ | Мышь | Сыр | +------+---------+ | Кот | Молоко | +------+---------+
Если в двух таблицах имеются столбцы с одинаковыми названиями, то будет показан только последний столбце с таким названием. Чтобы этого не происходило, выбирайте определённый столбцы и используйте команду «AS» с помощью которой можно переименовать столбец в результатах выборки.
Давайте теперь решим логическую задачу, которую поставили в начале статьи. Попробуем выбрать в этой объединённой таблице только одну строку, которая соответствует пользователю «Мышь». Для этого используем условие WHERE в SQL запросе:
SELECT `USERS`.`NAME`, `FOOD`.`PRODUCT` FROM `USERS` INNER JOIN `FOOD` ON `USERS`.`ID`=`FOOD`.`USER_ID` WHERE `USERS`.`NAME` LIKE 'Мышь';Обратите внимание, что в условии WHERE название полей так же необходимо ставить вместе с названием таблицы через точку: USERS.NAME. В результате выполнения этого запроса появится такой результат:
+------+---------+ | NAME | PRODUCT | +------+---------+ | Мышь | Сыр | +------+---------+