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 и создавайте сервисы с нуля.
Статьи по теме:
Артём Боровой — о работе руководителем отдела аналитики, профессиональном комьюнити и творческом подходе к задачам
#7 — Объединение данных. Join (Inner, Left, Right)
Для объединения данных из нескольких таблиц используются Joint’ы. В ходе урока вы изучите несколько способов объединения данных: Inner Join, Left Join и Right Join.
Видеоурок
Для объединения данных с нескольких таблиц используются Join’ы. Существует несколько форматов объединения данных: Inner, Left, Right и Outer. Принцип их работы представлен на фото ниже:
Наиболее часто используется формат Inner Join, рассмотрим его:
SELECT поле(или же поля) FROM таблички(table1) INNER JOIN объединение_с_табличкой(table2) ON table1.поле = table2.поле;Исправление ошибки
Если при работе с джоинтами возникает ошибка, то в таком случае вам нужно создать таблицу orders как показано на фото ниже:
Здесь главное указать в качестве движка таблицы значение InnoDb.
Задание к уроку
Необходимо оформить подписку на проект, чтобы получить доступ ко всем домашним заданиям
Большое задание по курсу
Вам необходимо оформить подписку на сайте, чтобы иметь доступ ко всем большим заданиям. В задание входит методика решения, а также готовый проект с ответом к заданию.
PS: подобные задания доступны при подписке от 1 месяца
Какая разница между правым и левым JOIN’ом?
Этот вопрос любят задавать на собеседованиях на позицию джуниора в IT-компаниях. Это, если хотите, классика жанра. Что же, давайте разбираться, в чём разница между SQL-запросами RIGHT и LEFT JOIN. Заодно, вспомним и запрос INNER JOIN.
Примечание: в статье описываются базовые случаи для общего понимания. В зависимости от конкретной базы данных возможны нюансы.
Внутреннее соединение INNER JOIN
С помощью этого запроса вы возвратите все записи из таблиц table_1 и table_2, которые связаны с помощью первичного (primary) и внешнего (foreign) ключей, а также отвечающие условию WHERE для таблицы table_1.
Если в какой-нибудь из вышеописанных таблиц отсутствует запись, которая соответствует соседней, эта пара не будет включена в общую выдачу. Таким образом, мы получим лишь те записи, которые существует как в первой, так и во второй таблицах. По сути, выборка осуществляется по наличию связи (ключу), то есть выдаются лишь записи, связанные между собой. Если у вас есть «одинокие» записи (записи без пары), то они выданы не будут.
SELECT * FROM table_1 INNER JOIN table_2 ON table_1.primary_key = table_2.foreign_key WHERE table_1.column_1 = ‘value’Внешнее соединение LEFT JOIN
С помощью этого запроса вы вернёте все данные из «левой» таблицы даже в том случае, если не будет найдено соответствий в «правой» таблице. Подразумевается, что «левая» таблица в запросе находится левее знака равно, а «правая», соответственно, правее (стандартная логика правой и левой руки).
Говоря иначе, когда мы присоединяем «правую» таблицу к «левой», происходит выборка всех записей согласно условиям WHERE для «левой» таблицы. Если в «правой» таблице у нас отсутствуют соответствующие записи по ключам, они вернутся как NULL. В результате главной выступает именно «левая» таблица, и именно относительно неё осуществляется выдача. При этом в условии ON «левая» таблица прописывается первой по порядку (table_1), а «правая» – второй (table_2):
SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.primary_key = table_2.foreign_key WHERE table_1.column_1 = ‘value’Внешнее соединение RIGHT JOIN
Используя этот запрос, вы вернёте все данные из «правой» таблицы даже в том случае, если не будут найдены соответствия в «левой» таблице. То есть всё происходит по аналогии с LEFT JOIN, однако NULL вернётся для полей «левой» таблицы. Иными словами, главной выступает именно правая «таблица» и выдача осуществляется относительно неё. Также обратите внимание на WHERE, т. к. условие выборки теперь затрагивает «правую» таблицу:
SELECT * FROM table_1 RIGHT JOIN table_2 ON table_1.primary_key = table_2.foreign_key WHERE table_2.column_1 = ‘value’В двух словах:
- LEFT JOIN — это абсолютно всё из левой таблицы, плюс то, что нашлось в правой (то, что удовлетворяет выражению ON). Если не нашлось в правой, то напротив записи из левой будет NULL;
- RIGHT JOIN — наоборот;
- INNER JOIN — только те записи из левой и правой таблиц, которые удовлетворяют выражению ON (в обеих таблицах NULL недопустим);
- FULL JOIN — всё вместе.
Совет
Лучше всего разбираться с этими запросами на практике. Для этого создайте соответствующую базу данных и таблицы в ней. Это не займёт много времени. К примеру, вы можете попрактиковаться с помощью этого видеоурока. Только «потрогав» всё руками, вы действительно поймёте, какова разница между правым и левым JOIN’ом.
Осмысляем работу джойнов в SQL: от реляционной алгебры до наглядных картинок
Выбираем, какие фильмы посмотреть, с помощью соединения данных в SQL.
скриншот из игры team fortress 2 / valve
Екатерина Степанова
Фулстек-разработчик. Любимый стек: Java + Angular, но в хорошей компании готова писать хоть на языке Ада.
Опять эта проблема — выбрать кино на вечер. Благодаря стриминговым сервисам доступны едва ли не все фильмы мира: это бесконечное полотно с постерами и фильтры, фильтры, фильтры…
МОЗГ: Поставлю-ка я фильтр по стране: пусть будет Дания, и добавлю ограничение по жанру — триллер… Ну вот — другое дело, относительно небольшой список.
— Мозг, а знаешь почему? Да потому что здесь только фильмы, которые сняты в Дании И помечены как триллеры.
— Но если мне нужно не И, а ИЛИ? Или я хочу выбирать из датских фильмов, но только НЕ триллеры? Как выставить такой фильтр?
— Да не знаю я, как задать такие критерии в этом сервисе. Вот если бы можно было писать на SQL — тут бы решение нашлось для любой комбинации признаков.
— Пруфы будут?
— Легко! Ещё и картинки будут. У меня и база фильмов уже спарсена — тренируйся не хочу.
Договоримся об обозначениях
Назовём множество датских фильмов — D, а множество триллеров — T. У каждого фильма будет уникальный номер, он же ключ. Раз ключ — пусть зовётся Key.
Заодно вспомним, как на SQL пишется простой запрос для связывания данных из двух таблиц: