JOIN (SQL) — что это за оператор, примеры использования
JOIN — это команда в языке запросов SQL, необходимом для работы с базами данных. Объединяет данные из двух разных таблиц в базе. Цель использования команды — получить нужное подмножество данных.
Командой JOIN в SQL пользуются очень часто, она одна из наиболее важных. В реляционных базах данных информация распределена по таблицам, а большая часть работы с базами подразумевает поиск в них нужных сведений. Для этого используются разные команды, и JOIN — одна из них.
Применение команды можно сравнить с использованием фильтра по товарам в интернет-магазине: разработчик выбирает определенное подмножество с помощью настроенных запросов. Обычно JOIN используется в блоках SELECT, которые «выбирают» из базы таблицы и записи, отвечающие нужным критериям.
Кто пользуется оператором JOIN
В основном SQL-запросы используют администраторы баз данных и бэкенд-разработчики — люди, работа которых подразумевает получение сведений из базы.
Но понимание работы JOIN важно и представителям других профессий, так как базовые знания SQL необходимы в любом проекте, — например, сотрудникам технической поддержки, контент-менеджерам, SEO-специалистам и другим.
Для чего нужен оператор JOIN
- Для «умного» поиска по таблицам в рамках одной базы данных. Использование разных режимов работы оператора помогает отфильтровать сведения по определенным критериям.
- Для быстрого отсечения информации, которая не нужна в рамках конкретного запроса.
- Для соединения двух таблиц в одну, например, чтобы отправить полученную информацию единым блоком.
Как работает JOIN
Изначально JOIN — бинарный оператор, то есть он работает с двумя переданными объектами. На практике современные реализации могут воспринимать и больше двух таблиц, просто операция в таком случае выполняется несколько раз.
Оператору передаются таблицы, которые нужно объединить, и критерий для объединения — логическое выражение, которое называется ключом.
В процессе работы JOIN таблицы проверяются на соответствие этому критерию. Например, значение поля ID в одной таблице должно соответствовать полю ID в другом. Оператор проверит строки обеих таблиц и выберет пары строк, где ID совпадают.
Найденные результаты объединяются в одну таблицу. Две соответствующие друг другу разные строки преобразуются в одну — это важнейшее условие работы JOIN. Строки чаще всего из разных таблиц, но это не обязательно. Главная особенность JOIN — объединение двух объектов в один.
Варианты работы команды JOIN
Выше мы написали, что у JOIN есть разные режимы работы. Они дают разные результаты; различаются и ситуации, в которых они могут пригодиться. Чаще всего выделяют четыре режима SQL JOIN: Inner, Outer, Self и Cross.
Особенности работы легче всего объяснить с помощью диаграмм Венна, которые также называют кругами Эйлера. На них множества представляются как круги, а объекты, которые относятся к обоим множествам, — как пересечения этих кругов.
Это самый простой и часто используемый вариант команды — внутреннее объединение. Если режим работы операции не указан вручную, то SQL автоматически воспримет любой JOIN как внутренний.
Оператору передаются две таблицы, и он возвращает их внутреннее пересечение по какому-либо критерию. Результатом будут записи, которые соответствуют обеим таблицам, — их перед отправкой объединят.
Inner JoinНапример, если в одной таблице будут перечислены черные животные, а в другой — собаки, то Inner Join вернет одну таблицу с перечислением черных собак. Столбцы будут «склеены» друг с другом, несмотря на то что в базе данные хранятся в разных таблицах. Это похоже на бинарное «и» из алгебры логики.
Внутреннее соединение используется чаще всего.
Outer JoinВторой распространенный вариант — внешнее соединение. Если внутреннее объединение имеет сходство с бинарным «и», то внешнее — несколько вариаций бинарного «или». Такой JOIN более гибкий, он возвращает не только строгое пересечение между двумя таблицами, но и отдельные элементы, которые принадлежат только одному из множеств.
Какому — зависит от типа.
Left Join. Возвращает пересечение множеств и все элементы из левой таблицы. Например, человек хочет посмотреть кино, но на русский фильм согласен, только если это боевик. Фильтр вернет ему все фильмы из множества «боевики», фильмы из подмножества «русские боевики», но других фильмов из множества «русские» там не будет.
Left JoinRight Join. Работает по тому же принципу, но вместо левой таблицы — правая. То есть человек получит в результатах боевики, только если они русские.
Right JoinJoin Full. Возвращает обе таблицы, объединенные в одну. Например, человек хочет увидеть список из всех боевиков и всех русских фильмов, без исключений.
Join FullOuter Join с NULLЭто не отдельный метод, мы описали его отдельно от остальных только для наглядности. Это тот же самый Outer Join, но с дополнительным параметром, который убирает из результатов поиска пересечение категорий. Это противоположность Inner Join.
Left Join с NULL. Возвращает данные из левой таблицы, но без пересечений с правой. Человеку покажутся все боевики, но русского кино и в частности русских боевиков среди них не будет.
Left Join с NULLRight Join с NULL. Соответственно, работает так же, но по отношению к «правой», второй таблице.
Right Join с NULLJoin Full с NULL. Работает как исключающее «или». Он тоже возвращает результат из обеих таблиц, кроме пересечений. Покажутся все русские фильмы и все боевики, а вот кино из подмножества «русские боевики» в результате не будет.
Join Full с NULLКак и с любым JOIN, результаты перед отправкой объединяются в одну таблицу.
Cross JoinЕго еще называют перекрестным. Это своеобразный вариант соединения, который нужен не так часто, но важен для понимания. Он возвращает декартово произведение — собираются все возможные пары из обеих таблиц. В отличие от остальных режимов, Cross Join не требует указания дополнительной информации.
Стандартное обращение к двум таблицам тоже покажет декартово произведение. Cross Join отличается от простого вызова двух таблиц тем, что они объединяются в одну.
В виде диаграммы Венна это соединение представить невозможно. Скорее можно объяснить его на примере математики. Например, в одном наборе десять чисел, в другом — пять. Cross Join между этими наборами — это все возможные комбинации сумм двух чисел из разных наборов. Результат будет выглядеть как таблица 11×5, где в каждой ячейке лежит своя сумма.
Cross JoinВ разработке Cross Join может использоваться при создании тех же фильтров в интернет-магазине. Например, человек ищет обувь по характеристикам «тип» и «размер» — должны быть выведены все возможные комбинации типа с размером.
Self JoinЭто «самосоединение», объединение внутри одной таблицы. Оно используется тогда, когда у разных полей одной таблицы могут быть одинаковые значения. Например, один и тот же участник музыкальной группы может быть и вокалистом, и, например, клавишником.
Если из базы музыкальных групп понадобится извлечь те, где вокалист и клавишник — одно лицо, потребуется Self Join.
Эта вариация может быть и внутренней, и внешней. Ее отличие в том, что таблица при таком режиме присоединяется сама к себе. Без практики это может быть непривычно, но в процессе использования логику работы легко понять.
Чтобы Self Join работал правильно, могут потребоваться псевдонимы таблиц: они помогают называть одну и ту же таблицу разными именами. В результате оператор «воспринимает» переданные сущности как разные.
Как начать работать с SQL-запросами
Для начала работы с SQL-запросами требуется СУБД — система для управления базами данных. Начинающие обычно выбирают MySQL: она простая в освоении, мало весит и распространяется бесплатно.
С помощью СУБД можно создать базу и управлять ей, модифицировать данные и пр. Можно работать с разными операциями, включая JOIN. Удаленный сервер не обязателен: есть программные решения, позволяющие «поднять» серверную часть на том же устройстве.
На «сервере» находится хранилище данных, а запросы поступают от «клиента».
Многотабличные запросы, оператор JOIN
В предыдущих статьях описывалась работа только с одной таблицей базы данных. В реальности же очень часто приходится делать выборку из нескольких таблиц, каким-то образом объединяя их. В данной статье вы узнаете основные способы соединения таблиц.
Например, если мы хотим получить информацию о тратах на покупки, мы можем её получить следующим образом:
SELECT family_member, amount * unit_price AS price FROM Payments
| family_member | price |
|---|---|
| 1 | 2000 |
| 2 | 2100 |
| 100 | |
| 4 | 350 |
| 4 | 300 |
| 5 | 100 |
| 2 | 120 |
| 2 | 5500 |
| 5 | 230 |
| 3 | 2200 |
| 2 | 66000 |
| 1 | 40 |
| 3 | 100 |
| 3 | 1200 |
В поле family_member полученной выборки отображаются идентификаторы записей из таблицы FamilyMembers, но для нас они мало что значат.
Вместо этих идентификаторов было бы гораздо нагляднее выводить имена тех, кто покупал (поле member_name из таблицы FamilyMember). Ровно для этого и существует объединение таблиц и оператор JOIN.
SELECT поля_таблиц
FROM таблица_1
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2
ON условие_соединения
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_n
ON условие_соединения]
Как можно увидеть по структуре, соединение бывает:
- внутренним INNER (по умолчанию)
- внешним OUTER, при этом внешнее соединение делится на левое LEFT, правое RIGHT и полное FULL
С более подробными деталями, чем отличается внутреннее соединение от внешнего и как они работают, мы познакомимся в следующих статьях.
Пока нам достаточно лишь знать, что для вышеописанного примера с запросом на покупки нам понадобится именно запрос с внутренним соединением, который будет выглядеть следующим образом:
SELECT family_member, member_name, amount * unit_price AS price FROM Payments INNER JOIN FamilyMembers ON Payments.family_member = FamilyMembers.member_id
family_member = FamilyMembers.member_id
| family_member | member_name | price |
|---|---|---|
| 1 | Headley Quincey | 2000 |
| 2 | Flavia Quincey | 2100 |
| 3 | Andie Quincey | 100 |
| 4 | Lela Quincey | 350 |
| 4 | Lela Quincey | 300 |
| 5 | Annie Quincey | 100 |
| 2 | Flavia Quincey | 120 |
| 2 | Flavia Quincey | 5500 |
| 5 | Annie Quincey | 230 |
| 3 | Andie Quincey | 2200 |
| 2 | Flavia Quincey | 66000 |
| 1 | Headley Quincey | 40 |
| 3 | Andie Quincey | 100 |
| 3 | Andie Quincey | 1200 |
В данном запросе мы сопоставляем записи из таблицы Payments и записи из таблицы FamilyMembers.
Чтобы сопоставление работало, мы указываем как именно записи из двух разных таблиц должны находить друг друга. Это условие указывается после ON:
ON Payments.family_member = FamilyMembers.member_id
В нашем случае поле family_member указывает на идентификатор в таблице FamilyMembers и таким образом помогает однозначному сопоставлению.
В большинстве случаев условием соединения является равенство столбцов таблиц (таблица_1.поле = таблица_2.поле), однако точно так же можно использовать и другие операторы сравнения.
SQL ВНУТРЕННЕЕ СОЕДИНЕНИЕ Запрос
Запрос INNER JOIN используется для извлечения совпадающих записей из двух или более таблиц на основе заданного условия.
Синтаксис:
ВЫБРАТЬ table1.column_name(s), table2.column_name(s) ИЗ таблицы1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ таблица2 ON table1.column_name = table2.column_name;
Для демонстрационных целей мы будем использовать следующие Сотрудник
. Таблицы отдела во всех примерах.
Таблицы отдела Таблица сотрудников
| Эмпид | Имя | Фамилия | Электронная почта | Зарплата | ИД отдела |
|---|---|---|---|---|---|
| 1 | ‘Джон’ | ‘Король’ | ‘[электронная почта защищена]’ | 33000 | 1 |
| 2 | ‘Джеймс’ | ‘Бонд’ | |||
| 3 | ‘Нина’ | ‘Кочхар’ | ‘[электронная почта защищена]’ | 17000 | 2 |
| 4 | ‘Лекс’ | ‘Де Хаан’ | ‘[электронная почта защищена]’ | 15000 | 1 |
| 5 | ‘Амит’ | ‘Патель’ | 18000 | 3 | |
| 6 | ‘Абдул’ | ‘Калам’ | ‘[электронная почта защищена]’ | 25000 | 2 |
Таблица отдела
| ИД отдела | Имя |
|---|---|
| 1 | ‘Финансы’ |
| 2 | «HR» |
Рассмотрим следующий запрос внутреннего соединения.
SELECT Сотрудник.EmpId, Сотрудник.Имя, Сотрудник.Фамилия, Отдел.Имя ОТ Сотрудника ВНУТРЕННЕЕ СОЕДИНЕНИЕ ON Сотрудник.ОтделId = Отдел.ОтделId;
Приведенный выше внутренний запрос на объединение объединяет таблицы Employee и Department и извлекает записи из обеих таблиц, где Employee.DeptId = Department.DeptId .
Он извлекает записи только из обеих таблиц, где DeptId в таблице Сотрудник соответствует DeptId
Отдел .
Если DeptId имеет значение NULL или не соответствует, то эти записи не будут получены. Ниже приведен результат вышеуказанного запроса.Таблица сотрудников
| Эмпид | Имя | Фамилия | Имя |
|---|---|---|---|
| 1 | ‘Джон’ | ‘Король’ | ‘Финансы’ |
| 3 | ‘Нина’ | ‘Кочхар’ | «HR» |
| 4 | ‘Лекс’ | ‘Де Хаан’ | ‘Финансы’ |
| 6 | ‘Абдул’ | «HR» |
Обратите внимание, что отображаются только те записи, чей DeptId соответствует, а не те, DeptId которых является нулевым или не соответствует.
Неважно, какую таблицу вы возьмете первой в запросе. Следующий запрос отобразит тот же результат, что и выше.
SELECT Сотрудник.EmpId, Сотрудник.Имя, Сотрудник.Фамилия, Отдел.Имя ОТ Департамента ВНУТРЕННЕЕ СОЕДИНЕНИЕ Сотрудник ON Department.DeptId = Employee.DeptId;
Использование фразы INNER JOIN не обязательно. Вы можете использовать предложение WHERE для достижения того же результата, как показано ниже.
SELECT emp.EmpId, emp.FirstName, emp.LastName, dept.Name ОТ Отдела отдела, Сотрудник emp ГДЕ dept.DeptId = emp.DeptId;
Псевдонимы можно использовать как сокращения имен таблиц, как показано ниже.
SELECT emp.EmpId, emp.FirstName, emp.LastName, dept.Name ОТ Отдела отдела ВНУТРЕННЕЕ СОЕДИНЕНИЕ Сотрудник ON dept.DeptId = emp.DeptId;
Повторите оператор INNER JOIN.. ON , чтобы включить в запрос еще одну таблицу. Например, следующий запрос внутреннего соединения объединяет три таблицы.
SELECT Сотрудник.EmpId, Сотрудник.Имя, Сотрудник.Фамилия, Консультант.Имя, Отдел.Имя ОТ Сотрудника ВНУТРЕННЕЕ СОЕДИНЕНИЕ ON Сотрудник.DeptId = Отдел.DeptId ВНУТРЕННЕЕ СОЕДИНЕНИЕ Консультант ON Consultant.DeptId = Department.DeptId;
Внутреннее соединение SQL — javatpoint
следующий → ← предыдущая ВНУТРЕННЕЕ СОЕДИНЕНИЕ в SQL — наиболее распространенный и важный тип соединения, который позволяет пользователям получать доступ к совпадающим данным из двух или более таблиц базы данных. При выполнении условия соединения между таблицами возвращает все общие строки из них. Диаграмма Венна INNER JOIN показана на следующем рисунке. Заштрихованная область диаграммы Венна показывает значения пересечения двух таблиц: Синтаксис ВНУТРЕННЕГО СОЕДИНЕНИЯ в SQL ВЫБРАТЬ Имя_столбца1, Имя_столбца2, ….., Имя_столбцаN
ОТ Table_Name1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ Table_Name2
ON Имя_таблицы1.Имя_столбца = Имя_таблицы2. Пример внутреннего соединения в SQLДавайте возьмем две таблицы с именами Employee_Details и Department , чтобы понять концепцию INNER JOIN. Таблица Employee_Details содержит столбцы Emp_ID, Emp_Name, Dept_ID и Emp_Salary. Таблица Department содержит столбцы Dept_Id и Dept_Name. Мы можем проверить данные таблицы Employee_Details и Department , используя следующие два разных запроса: ВЫБЕРИТЕ * ОТ отдела; Вывод:
ВЫБЕРИТЕ * ОТ Employee_Details; Вывод:
Следующий запрос объединяет эти две таблицы с помощью INNER JOIN в структурированном языке запросов: ВЫБИРАТЬ
Employee_Details. Оставить комментарий
|
Имя_столбца;