Объединение таблиц – UNION
Операция UNION позволяет объединить несколько результатов выборки в один набор строк. Именно объединение строк отличает эту операцию от соединения таблиц через JOIN, которая присоединяет столбцы.
Рассмотрим пример.
Сначала придумаем две таблицы: с участниками забега; с работниками забега.
| Имя_участника | Место |
|---|---|
| Светлана | 2 |
| Алексей | 4 |
| Александр | 1 |
| Екатерина | 5 |
| Илья | 3 |
| Имя_работника | Функция |
|---|---|
| Петр | Менеджер |
| Иван | Промоутер |
| Екатерина | Врач |
| Кирилл | Человек с водичкой |
Теперь получим из таблиц уникальные имена всех людей.
--Сформируем первый набор из таблицы участников select Имя_участника as Имя from Участники union --Сформируем второй набор из таблицы работников select Имя_работника from Работники
| Имя |
|---|
| Александр |
| Алексей |
| Екатерина |
| Иван |
| Илья |
| Кирилл |
| Петр |
| Светлана |
Важно знать про UNION
Самый первый SELECT задает названия столбцам итоговой выборки.
Обратите внимание на то, что в вышеприведенном примере для уникальности имен не потребовалось использовать ключевое слово DISTINCT, т.к. операция UNION уникализирует строки после их объединения.
Если нужно получить все записи, в том числе одинаковые, то нужно использовать операцию UNION ALL вместо просто UNION.
Можно объединять более 2 наборов строк – просто продолжать писать «UNION SELECT …».
Количество столбцов должны совпадать во всех объединяемых наборах.
Типы данных для объединяемых столбцов должны совпадать или хотя бы иметь возможность неявно преобразовываться друг к другу. Например:
- для набора №1 первый столбец имеет строковый тип VARCHAR, второй столбец числовой тип FLOAT;
- для набора №2 первый столбец – VARCHAR, второй столбец – числовой тип INT.
Несмотря на то, что второй столбец для разных наборов отличается по типу данных, запрос все равно выполнится, т.к. INT будет преобразован к FLOAT.
Если требуется сделать сортировку полученной выборки, то предложение ORDER BY записывается в самом последнем селекте.
select col1, col2, col3 from Table1 union all select col1, col2, col3 from Table2 union all select col1, col2, col3 from Table3 order by col1 -- Сортируем весь результат
- Объединение таблиц – UNION
- Соединение таблиц – операция JOIN и ее виды
- Тест на знание основ SQL
Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.
SQL-Урок 10. Сочетание таблиц (INNER JOIN)
Наиболее мощной особенностью языка SQL является возможность сочетания различных таблиц в оперативной памяти СУБД при выполнении запросов. Сочетания очень часто используются для анализа данных. Как правило, данные находятся в разных таблицах, что позволяет их более эффективно хранить (поскольку информация не дублируется), упрощает обработку данных и позволяет масштабировать базу данных (возможно добавлять новые таблицы с дополнительной информацией).
Таблицы баз данных, используемых в СУБД MS Access являются реляционными таблицами, то есть все таблицы можно связать между собой по общим полям.
1. Создание сочетания таблиц (JOINS)
Сочетание таблиц очень простая процедура. Следует указать все таблицы, которые будут включены в сочетание и «объяснить» СУБД, как они будут связаны между собой. Соединение производится с помощью слова WHERE, например:
Run SQLSELECT DISTINCT Seller_name, Product FROM Sellers, Sumproduct WHERE Sellers.City = Sumproduct.City
Try it Yourself
Соединив две таблицы, мы смогли увидеть какие товары реализует каждый продавец. Рассмотрим код запроса более подробно, поскольку он немного отличается от обычного запроса. Оператор SELECT начинается с указанием столбцов, которые мы хотим вывести, однако эти поля находятся в разных таблицах, предложение FROM содержит две таблицы, которые мы хотим соединить в операторе SELECT, таблицы сочетаются с помощью слова WHERE. Обязательно нужно указывать полное название поля (Таблица.Поле), поскольку поле City имеется в обеих таблицах.
2. Внутреннее соединение (INNER JOIN)
В предыдущем примере для сочетания таблиц мы использовали слово WHERE, которое проверяет на основе эквивалентности двух таблиц. Сочетание такого типа называется также «внутренним сочетанием». Существует также и другой способ сочетания таблиц, явно указывающий на тип сочетания. Рассмотрим следующий пример:
Run SQLSELECT DISTINCT Seller_name, Product FROM Sellers INNER JOIN Sumproduct ON Sellers.City = Sumproduct.City
Try it Yourself
В этом запросе вместо WHERE мы использовали конструкцию INNER JOIN. ON. которая дала аналогичный результат. Несмотря на то, что сочетание с предложением WHERE является короче, все же лучше использовать INNER JOIN, поскольку она является более гибкой, о чем будет подробнее рассказано в следующих разделах.
- Изменение регистра букв в тексте
- Сумма прописью на украинском языке
- Поиск латиницы в кириллице и наоборот
- Транслитерация с украинского на английский
Соединение двух таблиц
SQL имел бы очень маленькое практическое применение, если бы позволял получать данные только из одной таблицы.
При проектировании структуры данных стараются исключать дублирование данных (см нормальные формы).
Посмотрим на таблицу городов ( city )
SELECT * FROM city | city_id | name | timezone_id |
|---|---|---|
| 1 | Москва | 2 |
| 2 | Санкт-Петербург | 2 |
| 3 | Новосибирск | 5 |
| . | . | . |
В ней нет никакой информации o часовом поясе, только его идентификатор. Чтобы получить информацию о часовом поясе, нужно соединить таблицу городов ( city ) с таблицей часовых поясов ( timezone ) по идентификатору ( timezone_id ), т.е. для каждой записи из таблицы городов нужно получить ровно одну запись из таблицы часовых поясов с соответствующим идентификатором.
Рассмотрим запрос соединения таблиц городов и часовых поясов:
SELECT * FROM city JOIN timezone ON timezone.timezone_id = city.timezone_id | city_id | name | timezone_id | timezone_id | time_offset |
|---|---|---|---|---|
| 1 | Москва | 2 | 2 | UTC+3 |
| 2 | Санкт-Петербург | 2 | 2 | UTC+3 |
| 3 | Новосибирск | 5 | 5 | UTC+6 |
| . | . | . | . | . |
SELECT * -- выбрать все поля FROM city -- из таблицы city JOIN timezone -- присоединить таблицу timezone ON timezone.timezone_id = city.timezone_id -- для каждой записи из city сопоставить запись -- из timezone с совпадающим timezone_id Обрати внимание на столбцы полученного результата, состоящего из 5 столбцов
- city_id — идентификатор города из таблицы city ;
- name — название города из таблицы city ;
- timezone_id — идентификатор часового пояса из таблицы city ;
- timezone_id — идентификатор часового пояса из таблицы timezone ;
- time_offset — время относительно UTC из таблицы timezone .
Столбец timezone_id встретился дважды в результате, потому что он есть в каждой из таблиц.
Синтаксис соединения таблиц:
SELECT список_полей FROM таблица1 JOIN таблица2 ON условия_присоединения_таблицы2 JOIN таблица3 ON условия_присоединения_таблицы3 . Объединение нескольких таблиц — Основы аналитики на SQL
Не всегда аналитику достаточно самых базовых возможностей SQL. Иногда нужно составлять более сложные аналитические запросы, объединять информацию из разных источников, проводить анализ данных на основе связанных данных.
В таких случаях аналитики используют оператор JOIN — один из ключевых инструментов для объединения таблиц в SQL. Именно его мы изучим в этом уроке.
Оператор JOIN
Оператор JOIN комбинирует строки из двух или более таблиц на основе заданных условий связи между ними. Он использует значения ключевых столбцов, чтобы найти связи между таблицами и создать новую результирующую таблицу.
В SQL есть несколько видов объединений. Для наглядности возьмем таблицы, которые мы планируем объединить и представим в виде кругов на листе бумаги. В таком случае все виды объединений можно графически изобразить так:
На схеме выше мы видим четыре оператора, которые используются для объединения данных разными способами:
- INNER JOIN — возвращает строки, которые имеют совпадения в обеих таблицах по заданным условиям. Это самый распространенный тип объединения
- LEFT JOIN или LEFT OUTER JOIN — возвращает все строки из левой таблицы, а также соответствующие строки из правой таблицы по заданным условиям. Если в правой таблице нет соответствующих строк, то в результате будут отображаться NULL -значения. Это аналогично области левой таблицы
- RIGHT JOIN или RIGHT OUTER JOIN — возвращает все строки из правой таблицы, а также соответствующие строки из левой таблицы по заданным условиям. Если в левой таблице нет соответствующих строк, то в результирующем наборе будут отображаться NULL -значения. Это аналогично области правой таблицы
- 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 студентов
Наши выпускники работают в компаниях: