Как объединить таблицы в sql

Объединение нескольких таблиц — Основы аналитики на SQL

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

В таких случаях аналитики используют оператор JOIN — один из ключевых инструментов для объединения таблиц в SQL. Именно его мы изучим в этом уроке.

Оператор JOIN

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

В SQL есть несколько видов объединений. Для наглядности возьмем таблицы, которые мы планируем объединить и представим в виде кругов на листе бумаги. В таком случае все виды объединений можно графически изобразить так:

На схеме выше мы видим четыре оператора, которые используются для объединения данных разными способами:

1. INNER JOIN — возвращает строки, которые имеют совпадения в обеих таблицах по заданным условиям. Это самый распространенный тип объединения
2. LEFT JOIN или LEFT OUTER JOIN — возвращает все строки из левой таблицы, а также соответствующие строки из правой таблицы по заданным условиям. Если в правой таблице нет соответствующих строк, то в результате будут отображаться NULL -значения. Это аналогично области левой таблицы
3. RIGHT JOIN или RIGHT OUTER JOIN — возвращает все строки из правой таблицы, а также соответствующие строки из левой таблицы по заданным условиям. Если в левой таблице нет соответствующих строк, то в результирующем наборе будут отображаться NULL -значения. Это аналогично области правой таблицы
4. FULL JOIN или FULL OUTER JOIN — возвращает все строки из обеих таблиц и соответствующие строки по заданным условиям. Если в одной из таблиц нет соответствующих строк, то в результирующем наборе будут отображаться NULL -значения.

Такая связь с теорией множеств помогает понять логику различных типов объединений и их результатов.

Синтаксис объединений и INNER JOIN

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

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

Синтаксис объединений в SQL выглядит так:

SELECT название_колонки> FROM таблица1> INNER JOIN таблица2> ON условия_объединения>; 

В этом запросе после оператора FROM мы указываем название левой таблицы — то есть той таблицы, к которой присоединяем данные. После INNER JOIN указываем название правой таблицы — то есть присоединяемой таблицы. В качестве условий объединения указываем показатели, которые связывают обе эти таблицы.

Для примера возьмем нашу базу данных , в которую мы добавили новую таблицу orders .

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

SELECT customers.customer_name, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id; 

Итоговая таблица будет выглядеть так:

Этот запрос соединяет таблицы customers и orders с помощью INNER JOIN . Оператор INNER JOIN возвращает только те строки, где значения в столбце customer_id совпадают в обеих таблицах.

В результате этого запроса получим:

• Имена клиентов — customer_name
• Даты заказов — order_date

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

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

Допишем в этот запрос оператор AND после ON . Таким образом мы добавим дополнительное условие для объединения таблиц. Оно должно выполняться одновременно с условием объединения таблиц в операторе ON :

SELECT customers.customer_name, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id AND orders.order_date > '2023-01-02'; 

В этом случае мы:

• Проверяем равенство customer_id в таблицах customers и orders
• Добавляем условие orders.order_date > ‘2023-01-02’ с помощью оператора AND

Таким образом, запрос вернет имена клиентов customer_name и даты заказов order_date после указанной даты.

С помощью оператора AND можно добавить дополнительные условия для фильтрации данных при объединении таблиц — и таким образом получить более точный и специфический результат.

Отношения в таблицах

В SQL существуют три основных типа связей между таблицами:

• «Один-ко-многим» (One-to-Many)
• «Один-к-одному» (One-to-One)
• «Многие–ко–многим» (Many-to-Many)

Умение отличать эти связи друг от друга помогает правильно использовать оператор JOIN при объединении таблиц в SQL.

Для начала рассмотрим самый распространенный тип связи — «Один-ко-многим» (One-to-Many). При таком типе связи одна запись в одной таблице связана с несколькими записями в другой таблице.

Например, у нас на складе есть десять ноутбуков одной и той же модели. Если мы продадим все ноутбуки разным людям, то эта модель будет входить в десять разных заказов.

Это означает, что каждая запись в таблице products может быть связана с несколькими записями в таблице sales .

Посмотрим на примере такого запроса:

SELECT products.product_name, sales.quantity FROM products INNER JOIN sales ON products.product_id = sales.product_id; 

Выполнив этот запрос, мы увидим список товаров и количество продаж:

Рассмотрим еще один вид связи — «Один-к-одному» (One-to-One). При такой связи каждая запись в одной таблице имеет только одну связанную запись в другой таблице.

Например, каждый клиент в таблице customers может иметь только один заказ в таблице orders . Пример SQL-запроса с такой связью выглядит так:

SELECT customers.customer_id, customers.customer_name, orders.order_id, orders.order_date FROM customers JOIN orders ON customers.customer_id = orders.customer_id; 

И вот такой результат мы получим:

Рассмотрим третий вид связи — «Многие–ко–многим» (Many-to-Many). Этот тип связи возникает, когда множество записей в одной таблице связано с множеством записей в другой таблице. Для реализации такой связи нужна таблица-связь — это вспомогательная таблица, которая содержит связи между записями из обеих таблиц.

Рассмотрим эту связь в наших данных. Именно таким образом связаны товары и магазины — ведь множество товаров может быть связано с множеством магазинов. Для этого используется таблица-связь sales , которая содержит информацию о связи между продуктами и магазинами.

SQL-запрос может выглядеть так:

SELECT products.product_id, products.product_name, sales.sale_id, sales.quantity, stores.store_name FROM products JOIN sales ON products.product_id = sales.product_id JOIN stores ON sales.store_id = stores.store_id; 

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

Выводы

Сделаем краткие выводы:

• Чтобы работать с аналитическими запросами в SQL, часто нужно объединять данные из нескольких таблиц
• Оператор JOIN позволяет объединять строки из разных таблиц на основе заданных условий связи
• В SQL существуют различные типы объединений — INNER JOIN , LEFT JOIN , RIGHT JOIN и FULL JOIN
• Связь «Один-ко-многим» (One-to-Many) означает, что одна запись в одной таблице может быть связана с несколькими записями в другой таблице
• Связь «Один-к-одному» (One-to-One) означает, что каждая запись в одной таблице имеет только одну связанную запись в другой таблице

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно

• 130 курсов, 2000+ часов теории
• 1000 практических заданий в браузере
• 360 000 студентов

Наши выпускники работают в компаниях:

SQL — Урок 6. Объединение таблиц (внутреннее объединение)

Предположим, мы хотим узнать, какие темы, и какими авторами были созданы. Для этого проще всего обратиться к таблице Темы (topics):

Но, что если нам необходимо, чтобы в ответе на запрос были не идентификаторы авторов, а их имена? Вложенные запросы нам не помогут, т.к. в конечном итоге они выдают данные из одной таблицы. А нам надо получить данные из двух таблиц (Темы и Пользователи) и объединить их в одну. Запросы, которые позволяют это сделать, в SQL называются Объединениями.

Синтаксис самого простого объединения следующий:

SELECT имена_столбцов_таблицы_1, имена_столбцов_таблицы_2 FROM имя_таблицы_1, имя_таблицы_2;

Давайте создадим простое объединение:

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

Чтобы результирующая таблица выглядела так, как мы хотели, необходимо указать условие объединения. Мы связываем наши таблицы по идентификатору автора, это и будет нашим условием. Т.е. мы укажем в запросе, что необходимо выводить только те строки, в которых значения поля id_author таблицы topics совпадают со значениями поля id_user таблицы users:

На схеме будет понятнее:

Т.е. мы в запросе сделали следующее условие: если в обеих таблицах есть одинаковые идентификаторы, то строки с этим идентификатором необходимо объединить в одну результирующую строку.

Если в одной из объединяемых таблиц есть строка с идентификатором, которого нет в другой объединяемой таблице, то в результирующей таблице строки с таким идентификатором не будет. В нашем примере есть пользователь Oleg (id=5), но он не создавал тем, поэтому в результате запроса его нет.

Вообще, корректный синтаксис объединения с условием выглядит так:

SELECT имя_таблицы_1.имя_столбца1_таблицы_1, имя_таблицы_1.имя_столбца2_таблицы_1, имя_таблицы_2.имя_столбца1_таблицы_2, имя_таблицы_2.имя_столбца2_таблицы_2 FROM имя_таблицы_1, имя_таблицы_2 WHERE имя_таблицы_1.имя_столбца_по_которому_объединяем = имя_таблицы_2.имя_столбца_по_которому_объединяем;

Если имя столбца уникально, то название таблицы можно опустить (как мы делали в примере), но делать это не рекомендуется.

Как вы понимаете, объединения дают возможность выбирать любую информацию из любых таблиц, причем объединяемых таблиц может быть и три, и четыре, да и условие для объединения может быть не одно.

Для примера давайте создадим запрос, который покажет нам все сообщения, к каким темам они относятся и авторов этих сообщений. Конечно, вся эта информация хранится в таблице Сообщения (posts):

Но чтобы вместо идентификаторов отображались имена и названия, нам придется сделать объединение трех таблиц:

Т.е. мы объединили таблицы Сообщения и Пользователи условием posts.id_author=users.id_user, а таблицы Сообщения и Темы — условием posts.id_topic=topics.id_topic

Объединения, которые мы сегодня рассматривали, называются Внутренними объединениями. Такие объединения связывают строки одной таблицы со строками другой таблицы (а может еще и третьей таблицы). Но бывают ситуации, когда необходимо, чтобы в результат были включены строки, не имеющие связанных. Например, когда мы создавали запрос, какие темы и какими авторами были созданы, пользователь Oleg в результирующую таблицу не попал, т.к. тем не создавал, а потому и связанной строки в объединяемой таблице не имел.

Поэтому, если нам потребуется составить несколько иной запрос — вывести всех пользователей и темы, которые они создавали, если таковые имеются — то нам придется воспользоваться Внешним объединением, позволяющим выводить все строки одной таблицы и имеющиеся связанные с ними строки из другой таблицы. О таких объединениях мы и будем говорить в следующем уроке.

Онлайн-курс. Освойте востребованную профессию с зарплатой от 70 000 руб в месяц!

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

Как соединить 3 таблицы в sql

Чтобы соединить три таблицы в SQL, вы можете использовать оператор JOIN . Оператор JOIN объединяет две таблицы на основе общих столбцов, а при необходимости вы можете объединить несколько таблиц.

Для объединения трех таблиц вам нужно выполнить три операции JOIN . Рассмотрим пример:

SELECT t1.column1, t2.column2, t3.column3 FROM table1 t1 JOIN table2 t2 ON t1.column1 = t2.column1 JOIN table3 t3 ON t2.column2 = t3.column2; 

Здесь мы объединяем три таблицы: table1, table2 и table3. Мы выбираем определенные столбцы из каждой таблицы, а затем используем оператор JOIN для объединения таблицы table1 и table2, а затем таблицы table2 и table3.

Обратите внимание, что для успешного объединения таблиц необходимо наличие общих столбцов в этих таблицах. Кроме того, если таблицы содержат дублирующиеся строки, то результатом объединения могут быть дублирующиеся строки. Чтобы исключить дубли, можно использовать оператор DISTINCT .

Объединение таблиц при запросе (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 | +------+---------+ | Мышь | Сыр | +------+---------+