Что такое inner join в sql

Осмысляем работу джойнов в SQL: от реляционной алгебры до наглядных картинок

Выбираем, какие фильмы посмотреть, с помощью соединения данных в SQL.

скриншот из игры team fortress 2 / valve

Опять эта проблема — выбрать кино на вечер. Благодаря стриминговым сервисам доступны едва ли не все фильмы мира: это бесконечное полотно с постерами и фильтры, фильтры, фильтры…

МОЗГ: Поставлю-ка я фильтр по стране: пусть будет Дания, и добавлю ограничение по жанру — триллер… Ну вот — другое дело, относительно небольшой список.

— Мозг, а знаешь почему? Да потому что здесь только фильмы, которые сняты в Дании И помечены как триллеры.

— Да не знаю я, как задать такие критерии в этом сервисе. Вот если бы можно было писать на SQL — тут бы решение нашлось для любой комбинации признаков.

— Легко! Ещё и картинки будут. У меня и база фильмов уже спарсена — тренируйся не хочу.

Фулстек-разработчик. Любимый стек: Java + Angular, но в хорошей компании готова писать хоть на языке Ада.

Договоримся об обозначениях

Назовём множество датских фильмов — D, а множество триллеров — T. У каждого фильма будет уникальный номер, он же ключ. Раз ключ — пусть зовётся Key.

Заодно вспомним, как на SQL пишется простой запрос для связывания данных из двух таблиц:

INNER JOIN

Если не уточнить тип соединения ( JOIN), то по умолчанию применяется INNER JOIN — как раз тот вариант, который сработал в нашем кинофильтре. Это он выбирает и триллеры, и датские фильмы одновременно.

Источник

INNER JOIN простыми словами

Перевод статьи «SQL INNER JOIN Explained in Simple Words».

Как известно, база данных хранит данные в таблицах. Скорее всего вы уже умеете писать запросы к одной таблице. Но что если требуется поработать с несколькими таблицами? Чтобы комбинировать данные из двух и более таблиц, нужно воспользоваться оператором JOIN. Сегодня на примерах разберемся, как работает INNER JOIN.

JOIN для двух таблиц

Наша тренировочная база данных будет состоять из двух таблиц: TRAVEL_PACK и CITIES:

TRAVEL_PACK

CITIES

Оператор JOIN объединяет записи таблиц по общему полю или колонке (т.е. такая колонка должна быть в каждой из таблиц). В нашем случае у нас есть колонка PACK_NAME в таблице TRAVEL_PACK и точно такая же — в таблице CITIES.

Легко заметить, что в обеих таблицах эта колонка называется одинаково. Но это не обязательно, и в других базах данных может быть иначе. Другими словами, при использовании оператора INNER JOIN названия общей колонки в таблицах могут совпадать, но могут и отличаться.

Первый JOIN — найдем путевки по приморским городам

Залог хорошего SQL-запроса — четкое понимание того, где хранятся нужные данные. В нашем случае очевидно, что для поиска названий путёвок понадобится TRAVEL_PACK, а проверить, находится ли город у моря, можно в таблице CITIES.

Комбинировать данные из двух таблиц можно с помощью конструкции JOIN. Она создает связь между таблицами и помогает отобразить данные из них единовременно. Условно говоря, из двух строк отдельных таблиц JOIN создает одну целую. Такой тип соединения называется INNER JOIN (впрочем, он является синонимом для JOIN). Наряду с этим видом часто используют LEFT, RIGHT, OUTER и много других типов JOIN.

Вот как JOIN создаёт соединённые строки с данными:

Синтаксис запроса с JOIN:

Подставим в эту схему названия таблиц и колонок из тренировочной базы данных:

После секции FROM с INNER JOIN можно переходить к написанию SELECT с WHERE :

Обратите внимание, что обращение к колонке PACK_NAME сделано с добавлением названия таблицы (TRAVEL_PACK). Это хорошая практика для случаев, когда в запросе задействовано больше одной таблицы.

При помощи оператора WHERE отфильтруем результаты, чтобы остались только приморские города:

В результате должен получится вот такой запрос:

А результатом его выполнения будет такой датасет:

По образу и подобию предыдущего запроса можно обратиться к двум известным таблицам CITIES и TRAVEL_PACK и соединить их с помощью INNER JOIN (далее JOIN и INNER JOIN будут использоваться взаимозаменяемо).

В фильтрации WHERE ограничим набор данных по стоимости:

Полностью запрос будет выглядеть так:

А вернёт он такие данные:

Глубокое погружение: INNER JOIN для трёх таблиц

JOIN можно использовать не только с двумя, но и с большим числом таблиц. Для соединения каждой пары таблиц необходим один JOIN. Схематический пример:

Обратимся к практическому примеру. Добавим в базу данных новую таблицу STATES, в которой будем хранить данные о государствах, регионах и провинциях разных стран.

TRAVEL_PACK

STATES

CITIES

Запросим все туристические города из регионов, говорящих на испанском и португальском. Понятно, что нужно соединить таблицы CITIES и STATES, а затем приджоинить к ним TRAVEL_PACK. Приступим к решению задачи и используем знания из предыдущих частей этой статьи.

Во-первых, сделаем JOIN для таблиц CITIES и STATES по колонкам CITIES.STATE и STATE.NAME :

Сделаем JOIN с третьей таблицей TRAVEL_PACK:

Отполируем запрос с помощью SELECT и WHERE :

Итоги
В этой статье мы рассмотрели объединение записей в SQL-таблицах. Оператор JOIN открывает перед вами множество новых возможностей в использовании SQL.

От редакции Techrocks: Если хотите изучить не только INNER JOIN, но и другие виды объединений, обратите внимание на статью “SQL JOIN: руководство по объединению таблиц”.

Источник

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

SQL оператор JOINS

В этом учебном материале вы узнаете, как использовать SQL JOINS с синтаксисом и примерами.

Описание

SQL JOINS используются для извлечения данных из нескольких таблиц. SQL JOIN выполняется всякий раз, когда две или более таблицы перечислены в операторе SQL.

Существует 4 различных типа соединений SQL:

Итак, давайте обсудим синтаксис SQL JOIN, рассмотрим наглядные иллюстрации SQL JOINS и рассмотрим несколько примеров.

SQL INNER JOIN (простое соединение)

Скорее всего, вы уже писали SQL запрос, который использует SQL INNER JOIN. Это наиболее распространенный тип соединения SQL. INNER JOIN возвращает все строки из нескольких таблиц, где выполняется условие соединения.

Синтаксис

Синтаксис INNER JOIN в SQL:

Рисунок.

Пример

Давайте рассмотрим пример использования INNER JOIN в запросе.

В этом примере у нас есть таблица customer и следующими данными:

И таблица orders со следующими данными:

Выполним следующий SQL оператор:

Будет выбрано 4 записи. Вот результаты, которые вы должны получить:

SQL LEFT OUTER JOIN

Другой тип соединения называется LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).

Синтаксис

Синтаксис для LEFT OUTER JOIN в SQL:

В некоторых базах данных ключевое слово OUTER опущено и записывается просто как LEFT JOIN.

Рисунок

Пример

Теперь давайте рассмотрим пример, который показывает, как использовать LEFT OUTER JOIN в операторе SELECT.

И таблицу orders со следующими данными:

Введите следующий SQL оператор:

Будет выбрано 6 записей. Вот результаты, которые вы получите:

SQL RIGHT OUTER JOIN JOIN

Другой тип соединения называется SQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).

Синтаксис

Синтаксис для RIGHT OUTER JOIN в SQL:

В некоторых базах данных ключевое слово OUTER опущено и записывается просто как RIGHT JOIN.

Рисунок

На этом рисунке SQL RIGHT OUTER JOIN возвращает затененную область:

Пример

Теперь давайте рассмотрим пример, который показывает, как использовать RIGHT OUTER JOIN в операторе SELECT.

И таблицу orders со следующими данными:

Введите следующий SQL оператор:

Будет выбрано 5 записей. Вот результаты, которые вы должны получить:

SQL FULL OUTER JOIN

Другой тип объединения называется SQL FULL OUTER JOIN. Этот тип объединения возвращает все строки из LEFT таблицы и RIGHT таблицы со значениями NULL в месте, где условие соединения не выполняется.

Синтаксис

Синтаксис для SQL FULL OUTER JOIN:

В некоторых базах данных ключевое слово OUTER опускается и записывается просто как FULL JOIN.

Рисунок

Пример

Давайте рассмотрим пример, который показывает, как использовать FULL OUTER JOIN в операторе SELECT.

И таблицу orders со следующими данными:

Введите следующий SQL оператор:
SELECT customers.customer_id,
orders.order_id,
orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

Будет выбрано 7 записей. Вот результаты, которые вы получите:

Как видите, строки, где customer_id равен 6000 и 9000, будут включены, но поля order_id и order_date для этих записей содержат значение NULL. Строка, где order_id равен 5, также будет включена, но поле customer_id для этой записи имеет значение NULL.

Источник

SQL Внутреннее соединение

SQL INNER JOIN

Ключевое слово INNER JOIN выбирает записи, имеющие совпадающие значения в обеих таблицах.

Синтаксис INNER JOIN

Демо база данных

В этом уроке мы будем использовать хорошо известный пример базы данных Northwind.

Ниже приведен выбор из таблицы «Orders»:

И выбор из таблицы «Customers»:

Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico

Пример SQL INNER JOIN

Следующая инструкция SQL выбирает все заказы с информацией о клиенте:

Пример

Примечание: Ключевое слово INNER JOIN выбирает все строки из обеих таблиц до тех пор, пока существует соответствие между столбцами. Если в таблице «Orders» есть записи, которые не имеют совпадений в разделе «Customers», то эти заказы не будут показаны!

Три таблицы JOIN

Следующая инструкция SQL выбирает все заказы с информацией о клиенте и грузоотправителе:

Источник

Соединение таблиц – операция JOIN и ее виды

Говоря про соединение таблиц в SQL, обычно подразумевают один из видов операции JOIN. Не стоит путать с объединением таблиц через операцию UNION. В этой статье я постараюсь простыми словами рассказать именно про соединение, чтобы после ее прочтения Вы могли использовать джойны в работе и не допускать грубых ошибок.

Соединение – это операция, когда таблицы сравниваются между собой построчно и появляется возможность вывода столбцов из всех таблиц, участвующих в соединении.

Придумаем 2 таблицы, на которых будем тренироваться.

Таблица «Сотрудники», содержит поля:

Таблица «Отделы», содержит поля:

Давайте уже быстрее что-нибудь покодим.

INNER JOIN

Самый простой вид соединения INNER JOIN – внутреннее соединение. Этот вид джойна выведет только те строки, если условие соединения выполняется (является истинным, т.е. TRUE). В запросах необязательно прописывать INNER – если написать только JOIN, то СУБД по умолчанию выполнить именно внутреннее соединение.

Давайте соединим таблицы из нашего примера, чтобы ответить на вопрос, в каких отделах работают сотрудники (читайте комментарии в запросе для понимания синтаксиса).

Получим следующий результат:

Из результатов пропал сотрудник Алексей (id = 3), потому что условие «Сотрудники.Отдел = Отделы.id» не будет истинно для этой сроки из таблицы «Сотрудники» с каждой строкой из таблицы «Отделы». По той же логике в результате нет отдела «Администрация». Попробую это визуализировать (зеленные линии – условие TRUE, иначе линия красная):

Если не углубляться в то, как внутреннее соединение работает под капотом СУБД, то происходит примерно следующее:

Если для одной или нескольких срок из левой таблицы (в рассмотренном примере левой таблицей является «Сотрудники», а правой «Отделы») истинным условием соединения будут являться одна или несколько срок из правой таблицы, то строки умножат друг друга (повторятся). В нашем примере так произошло для отдела с поэтому строка из таблицы «Отделы» повторилась дважды для Федора и Светланы.
Перемножение таблиц проще ощутить на таком примере, где условие соединения будет всегда возвращать TRUE, например 1=1:

В результате получится 12 строк (4 сотрудника * 3 отдела), где для каждого сотрудника подтянется каждый отдел.

Также хочу сразу отметить, что в соединении может участвовать сколько угодно таблиц, можно таблицу соединить даже саму с собой (в аналитических задачах это не редкость). Какая из таблиц будет правой или левой не имеется значения для INNER JOIN (для внешних соединений типа LEFT JOIN или RIGHT JOIN это важно. Читайте далее). Пример соединения 4-х таблиц:

Как видите, все просто, прописываем новый джойн после завершения условий предыдущего соединения. Обратите внимание, что для Table_3 указано несколько условий соединения с двумя разными таблицами, а также Table_1 соединяется сама с собой по условию с использованием сложения.
Строки, которые выведутся запросом, должны совпасть по всем условиям. Например:

На этом про внутреннее соединение и логику соединения таблиц в SQL – всё. Если остались неясности, то спрашивайте в комментариях.
Далее рассмотрим отличия остальных видов джойнов.

LEFT JOIN и RIGHT JOIN

Левое и правое соединения еще называют внешними. Главное их отличие от внутреннего соединения в том, что строка из левой (для LEFT JOIN) или из правой таблицы (для RIGHT JOIN) попадет в результаты в любом случае. Давайте до конца определимся с тем, какая таблица левая, а какая правая.
Левая таблица та, которая идет перед написанием ключевых слов [LEFT | RIGHT| INNER] JOIN, правая таблица – после них:

Теперь изменим наш SQL-запрос из самого первого примера так, чтобы ответить на вопрос «В каких отделах работают сотрудники, а также показать тех, кто не распределен ни в один отдел?»:

Результат запроса будет следующим:

Как видите, запрос вернул все строки из левой таблицы «Сотрудники», дополнив их значениями из правой таблицы «Отделы». А вот строка для отдела «Администрация» не показана, т.к. для нее не нашлось совпадений слева.

Это мы рассмотрели пример для левого внешнего соединения. Для RIGHT JOIN будет все тоже самое, только вернутся все строки из таблицы «Отделы»:

Алексей «потерялся», Администрация «нашлась».

Вопрос для Вас. Что надо изменить в последнем приведенном SQL-запросе, чтобы результат остался тем же, но вместо LEFT JOIN, использовался RIGHT JOIN?

Ответ. Нужно поменять таблицы местами:

В одном запросе можно применять и внутренние соединения, и внешние одновременно, главное соблюдать порядок таблиц, чтобы не потерять часть записей (строк).

FULL JOIN

Еще один вид соединения, который осталось рассмотреть – полное внешнее соединение.
Этот вид джойна вернет все строки из всех таблиц, участвующих в соединении, соединив между собой те, которые подошли под условие ON.

Давайте посмотрим всех сотрудников и все отделы из наших тестовых таблиц:

Теперь мы видим все, даже Алексея без отдела и Администрацию без сотрудников.

Вместо заключения

Помните о порядке выполнения соединений и порядке таблиц, если используете несколько соединений и используете внешние соединения. Можно выполнять LEFT JOIN для сохранения всех строк из самой первой таблицы, а последним внутренним соединением потерять часть данных. На маленьких таблицах косяк заметить легко, на огромных очень тяжело, поэтому будьте внимательны.
Рассмотрим последний пример и введем еще одну таблицу «Банки», в которой обслуживаются наши придуманные сотрудники:

В таблицу «Сотрудники» добавим столбец «Банк»:

Теперь выполним такой запрос:

В результате потеряли информацию о Светлане, т.к. для нее не нашлось банка с (такое происходит из-за неправильной проектировки БД):

Хочу обратить внимание на то, что любое сравнение с неизвестным значением никогда не будет истинным (даже NULL = NULL). Эту грубую ошибку часто допускают начинающие специалисты. Подробнее читайте в статье про значение NULL в SQL.

Пройдите мой тест на знание основ SQL. В нем есть задания на соединения таблиц, которые помогут закрепить материал.

Дополнить Ваше понимание соединений в SQL могут схемы, изображенные с помощью кругов Эйлера. В интернете много примеров в виде картинок.

Если какие нюансы джойнов остались не раскрытыми, или что-то описано не совсем понятно, что-то надо дополнить, то пишите в комментариях. Буду только рад вопросам и предложениям.

Привожу простыню запросов, чтобы Вы могли попрактиковаться на легких примерах, рассмотренных в статье:

Источник