Оператор join sql: SQL JOIN — соединение таблиц базы данных
SQL JOIN — соединение таблиц базы данных
Оператор языка SQL JOIN предназначен для соединения двух или более таблиц базы данных по совпадающему
условию. Этот оператор существует только в реляционных базах данных. Именно благодаря JOIN реляционные
базы данных обладают такой мощной функциональностью, которая позволяет вести не только хранение данных,
но и их, хотя бы простейший, анализ с помощью запросов. Разберём основные нюансы написания SQL-запросов с оператором JOIN,
которые являются общими для всех СУБД (систем управления базами данных). Для соединения двух таблиц оператор SQL JOIN имеет следующий синтаксис:
SELECT ИМЕНА_СТОЛБЦОВ (1..N)
FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2
ON УСЛОВИЕ
После одного или нескольких звеньев с оператором JOIN может следовать необязательная секция WHERE или HAVING, в которой,
также, как в простом SELECT-запросе, задаётся условие выборки. Общим для всех СУБД является то,
что в этой конструкции вместо JOIN может быть указано INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN,
FULL OUTER JOIN, CROSS JOIN (или, как вариант, запятая).
Запрос с оператором INNER JOIN предназначен для соединения таблиц и вывода результирующей таблицы,
в которой данные полностью пересекаются по условию, указанному после ON.
То же самое делает и просто
JOIN. Таким образом, слово INNER — не обязательное.
Есть база данных портала объявлений — 2. В ней есть таблица
Categories (категории объявлений) и Parts (части, или иначе — рубрики, которые и относятся к категориям).
Например, части Квартиры, Дачи относятся к категории Недвижимость, а части Автомобили, Мотоциклы — к категории
Транспорт.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД
не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.
Скрипт для создания базы данных портала объявлений — 2, её таблиц и заполения таблиц данными —
в файле по этой ссылке.
Таблицы этой базы данных с заполненными данными имеют следующий вид.
Таблица Categories:
Catnumb | Cat_name | Price |
10 | Стройматериалы | 105,00 |
505 | Недвижимость | 210,00 |
205 | Транспорт | 160,00 |
30 | Мебель | 77,00 |
45 | Техника | 65,00 |
Таблица Parts:
Part_ID | Part | Cat |
1 | Квартиры | 505 |
2 | Автомашины | 205 |
3 | Доски | 10 |
4 | Шкафы | 30 |
5 | Книги | 160 |
Заметим, что в таблице Parts Книги имеют Cat — ссылку на категорию, которой нет в
таблице Categories, а в таблице Categories Техника имеет номер категории Catnumb — значение, ссылки на которое
нет в таблице Parts.
Пример 1. Требуется соединить данные этих двух таблиц так, чтобы в результирующей таблице
были поля Part (Часть), Cat (Категория) и Price (Цена подачи объявления) и чтобы данные полностью
пересекались по условию. Условие — совпадение номера категории (Catnumb) в таблице Categories и ссылки
на категорию в таблице Parts. Для этого пишем следующий запрос:
SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price
FROM Parts INNER JOIN Categories
ON Parts.Cat = Categories.Catnumb
Результатом выполнения запроса будет следующая таблица:
Part | Cat | Price |
Квартиры | 505 | 210,00 |
Автомашины | 205 | 160,00 |
Доски | 10 | 105,00 |
Шкафы | 30 | 77,00 |
В результирующей таблице нет Книг, так как эта запись ссылается на категорию, которой
нет в таблице Categories, и Техники, так как эта запись имеет внешний ключ в таблице Categories, на
который нет ссылки в таблице Parts.
В ряде случаев при соединениях таблиц составить менее громоздкие запросы
можно с помощью предиката EXISTS и без использования JOIN.
Есть база данных «Театр». Таблица Play содержит данные о постановках. Таблица Team —
о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные
и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Пример 2. Определить самого востребованного актёра за последние
5 лет.
Оператор JOIN использовать 2 раза. Использовать COUNT(), CURDATE(), LIMIT 1.
Правильное решение и ответ.
Пример 3. Вывести список актеров, которые
в одном спектакле играют более одной роли, и количество их ролей.
Оператор JOIN использовать 1 раз. Использовать HAVING, GROUP BY.
Подсказка. Оператор HAVING применяется к числу ролей, подсчитанных агрегатной
функцией COUNT.
Правильное решение и ответ.
Запрос с оператором LEFT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы,
в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями
из первой по порядку (левой) таблицы, даже если они не соответствуют условию. У записей левой таблицы,
которые не соответствуют условию, значение столбца из правой таблицы будет NULL (неопределённым).
Пример 4. База данных и таблицы —
те же, что и в примере 1.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью
пересекаются по условию и дополняются всеми данными из таблицы Parts, которые не соответствуют условию,
пишем следующий запрос:
SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price
FROM Parts LEFT OUTER JOIN Categories
ON Parts.Cat = Categories.Catnumb
Результатом выполнения запроса будет следующая таблица:
Part | Cat | Price |
Квартиры | 505 | 210,00 |
Автомашины | 205 | 160,00 |
Доски | 10 | 105,00 |
Шкафы | 30 | 77,00 |
Книги | 160 | NULL |
В результирующей таблице, в отличие от таблицы из примера 1, есть Книги, но значение
столбца Цены (Price) у них — NULL, так как эта запись имеет идентификатор категории, которой нет в таблице
Categories.
Запрос с оператором RIGHT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы,
в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями
из второй по порядку (правой) таблицы, даже если они не соответствуют условию. У записей правой таблицы,
которые не соответствуют условию, значение столбца из левой таблицы будет NULL (неопределённым).
Пример 5. База данных и таблицы —
те же, что и в предыдущих примерах.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью
пересекаются по условию и дополняются всеми данными из таблицы Categories, которые не соответствуют условию,
пишем следующий запрос:
SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price
FROM Parts RIGHT OUTER JOIN Categories
ON Parts.Cat = Categories.Catnumb
Результатом выполнения запроса будет следующая таблица:
Part | Cat | Price |
Квартиры | 505 | 210,00 |
Автомашины | 205 | 160,00 |
Доски | 10 | 105,00 |
Шкафы | 30 | 77,00 |
NULL | 45 | 65,00 |
В результирующей таблице, в отличие от таблицы из примера 1, есть запись с категорией
45 и ценой 65,00, но значение
столбца Части (Part) у неё — NULL, так как эта запись имеет идентификатор категории, на которую нет
ссылок в таблице Parts.
Запрос с оператором FULL OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы,
в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями
из первой (левой) и второй (правой) таблиц, даже если они не соответствуют условию. У записей,
которые не соответствуют условию, значение столбцов из другой таблицы будет NULL (неопределённым).
Пример 6. База данных и таблицы —
те же, что и в предыдущих примерах.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью
пересекаются по условию и дополняются всеми данными как из таблицы Parts, так и из таблицы Categories, которые не соответствуют условию,
пишем следующий запрос:
SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price
FROM Parts FULL OUTER JOIN Categories
ON Parts.Cat = Categories.Catnumb
Результатом выполнения запроса будет следующая таблица:
Part | Cat | Price |
Квартиры | 505 | 210,00 |
Автомашины | 205 | 160,00 |
Доски | 10 | 105,00 |
Шкафы | 30 | 77,00 |
Книги | 160 | NULL |
NULL | 45 | 65,00 |
В результирующей таблице есть записи Книги (из левой таблицы) и с категорией 45
(из правой таблицы), причём у первой из них неопределённая цена (столбец из правой таблицы), а у второй —
неопределённая часть (столбец из левой таблицы).
В предыдущих запросах мы указывали с названиями извлекаемых столбцов из разных таблиц
полные имена этих таблиц. Такие запросы выглядят громоздко: одно и то же слово повторяется несколько раз. Нельзя
ли как-то упростить конструкцию? Оказывается, можно. Для этого следует использовать псевдонимы таблиц —
их сокращённые имена. Псевдоним может состоять и из одной буквы. Возможно любое количество букв в псевдониме,
главное, чтобы запрос после сокращения был понятен Вам самим. Общее правило: в секции запроса, определяющей
соединение, то есть вокруг слова JOIN нужно указать полные имена таблиц, а за каждым именем должен
следовать псевдоним таблицы.
Пример 7. Переписать запрос из примера 1 с использованием псевдонимов
соединяемых таблиц.
Запрос будет следующим:
SELECT P.Part, C.Catnumb AS Cat, C.Price
FROM Parts P INNER JOIN Categories C
ON P.Cat = C.Catnumb
Запрос вернёт то же самое, что и запрос в примере 1, но он гораздо компактнее.
Реляционные базы данных должны подчиняться требованиям целостности и неизбыточности данных,
в связи с чем данные об одном бизнес-процессе могут содержаться не только в одной, двух, но и в трёх и более
таблицах. В этих случаях для анализа данных используются цепочки соединённых таблиц: например, в одной (первой)
таблице содержится некоторый количественный показатель, вторую таблицу с первой и третьей связывают
внешние ключи — данные пересекаются, но только третья таблица содержит условие, в зависимости от которого
может быть выведен количественный показатель из первой таблицы. И таблиц может быть ещё больше. При помощи оператора
SQL JOIN в одном запросе можно соединить большое число таблиц. В таких запросах за одной секцией соединения
следует другая, причём каждый следующий JOIN соединяет со следующей таблицей таблицу, которая была второй
в предыдущем звене цепочки. Таким образом, синтаксис SQL запроса для соединения более двух таблиц следующий:
SELECT ИМЕНА_СТОЛБЦОВ (1..N)
FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2
ON УСЛОВИЕ
JOIN ИМЯ_ТАБЛИЦЫ_3
ON УСЛОВИЕ
…
JOIN ИМЯ_ТАБЛИЦЫ_M
ON УСЛОВИЕ
Пример 8. База данных — та же, что и в предыдущих примерах. К таблицам
Categories и Parts в этом примере добавится таблица Ads, содержащая данные об опубликованных на портале объявлениях.
Приведём фрагмент таблицы Ads, в котором среди записей есть записи о тех объявлениях, срок публикации которых
истекает 2018-04-02.
A_Id | Part_ID | Date_start | Date_end | Text |
21 | 1 | ‘2018-02-11’ | ‘2018-04-20’ | «Продаю…» |
22 | 1 | ‘2018-02-11’ | ‘2018-05-12’ | «Продаю…» |
… | … | … | … | … |
27 | 1 | ‘2018-02-11’ | ‘2018-04-02’ | «Продаю…» |
28 | 2 | ‘2018-02-11’ | ‘2018-04-21’ | «Продаю…» |
29 | 2 | ‘2018-02-11’ | ‘2018-04-02’ | «Продаю…» |
30 | 3 | ‘2018-02-11’ | ‘2018-04-22’ | «Продаю…» |
31 | 4 | ‘2018-02-11’ | ‘2018-05-02’ | «Продаю…» |
32 | 4 | ‘2018-02-11’ | ‘2018-04-13’ | «Продаю…» |
33 | 3 | ‘2018-02-11’ | ‘2018-04-12’ | «Продаю…» |
34 | 4 | ‘2018-02-11’ | ‘2018-04-23’ | «Продаю…» |
Представим, что сегодня ‘2018-04-02’, то есть это значение принимает функция CURDATE() —
текущая дата. Требуется узнать, к каким категориям принадлежат объявления, срок публикации которых
истекает сегодня. Названия категорий есть только в таблице CATEGORIES, а даты истечения срока публикации объявлений
— только в таблице ADS. В таблице PARTS — части категорий (или проще, подкатегории) опубликованных объявлений.
Но внешним ключом Cat_ID таблица PARTS связана с таблицей CATEGORIES, а таблица ADS связана внешним
ключом Part_ID с таблицей PARTS. Поэтому соединяем в одном запросе три таблицы и этот запрос можно
с максимальной корректностью назвать цепочкой.
Запрос будет следующим:
SELECT C.Cat_name FROM Categories C JOIN Parts P
ON P.Cat=C.Catnumb JOIN ads A ON A.Part_id=P.Part_id
WHERE A.Date_end=CURDATE()
Результат запроса — таблица, содержащая названия двух категорий — «Недвижимость» и
«Транспорт»:
Cat_name |
Недвижимость |
Транспорт |
Использование оператора SQL CROSS JOIN в наиболее простой форме — без условия соединения —
реализует операцию декартова произведения в реляционной алгебре.
Результатом такого соединения будет сцепление каждой строки первой таблицы с каждой строкой второй таблицы. Таблицы
могут быть записаны в запросе либо через оператор CROSS JOIN, либо через запятую между ними.
Пример 9. База данных — всё та же, таблицы — Categories и Parts.
Реализовать операцию декартова произведения этих двух таблиц.
Запрос будет следующим:
SELECT (*) Categories CROSS JOIN Parts
Или без явного указания CROSS JOIN — через запятую:
SELECT (*) Categories, Parts
Запрос вернёт таблицу из 5 * 5 = 25 строк, фрагмент которой приведён ниже:
Catnumb | Cat_name | Price | Part_ID | Part | Cat |
10 | Стройматериалы | 105,00 | 1 | Квартиры | 505 |
10 | Стройматериалы | 105,00 | 2 | Автомашины | 205 |
10 | Стройматериалы | 105,00 | 3 | Доски | 10 |
10 | Стройматериалы | 105,00 | 4 | Шкафы | 30 |
10 | Стройматериалы | 105,00 | 5 | Книги | 160 |
… | … | … | … | … | … |
45 | Техника | 65,00 | 1 | Квартиры | 505 |
45 | Техника | 65,00 | 2 | Автомашины | 205 |
45 | Техника | 65,00 | 3 | Доски | 10 |
45 | Техника | 65,00 | 4 | Шкафы | 30 |
45 | Техника | 65,00 | 5 | Книги | 160 |
Как видно из примера, если результат такого запроса и имеет какую-либо ценность, то
это, возможно, наглядная ценность в некоторых случаях, когда не требуется вывести структурированную информацию,
тем более, даже самую простейшую аналитическую выборку. Кстати, можно указать выводимые столбцы из каждой
таблицы, но и тогда информационная ценность такого запроса не повысится.
Но для CROSS JOIN можно задать условие соединения! Результат будет совсем иным. При
использовании оператора «запятая» вместо явного указания CROSS JOIN условие соединения задаётся не
словом ON, а словом WHERE.
Пример 10. Та же база данных портала объявлений, таблицы Categories и Parts.
Используя перекрестное соединение, соединить таблицы так, чтобы данные полностью пересекались по
условию. Условие — совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts.
Запрос будет следующим:
SELECT P.Part, C.Catnumb AS Cat, C.Price
FROM Parts P, Categories C
WHERE P.Cat = C.Cat_ID
Запрос вернёт то же самое, что и запрос в примере 1:
Part | Cat | Price |
Квартиры | 505 | 210,00 |
Автомашины | 205 | 160,00 |
Доски | 10 | 105,00 |
Шкафы | 30 | 77,00 |
И это совпадение не случайно. Запрос c перекрестным соединением по условию соединения полностью
аналогичен запросу с внутренним соединением — INNER JOIN — или, учитывая, что слово INNER — не обязательное,
просто JOIN.
Таким образом, какой вариант запроса использовать — вопрос стиля или даже привычки
специалиста по работе с базой данных. Возможно, перекрёстное соединение с условием для двух таблиц
может представляться более компактным. Но преимущество перекрестного соединения для более чем двух
таблиц (это также возможно) весьма спорно. В этом случае WHERE-условия пересечения перечисляются через
слово AND. Такая конструкция может быть громоздкой и трудной для чтения, если в конце запроса есть
также секция WHERE с условиями выборки.
Поделиться с друзьями
Реляционные базы данных и язык SQL
SQL оператор JOINS — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite
В этом учебном материале вы узнаете, как использовать 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:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Рисунок.
На этом рисунке SQL INNER JOIN возвращает затененную область:
SQL INNER JOIN будет возвращать записи, где пересекаются table1 и table2.
Пример
Давайте рассмотрим пример использования INNER JOIN в запросе.
В этом примере у нас есть таблица customer и следующими данными:
customer_id | first_name | last_name | favorite_website |
---|---|---|---|
4000 | Justin | Bieber | google.com |
5000 | Selena | Gomez | bing.com |
6000 | Mila | Kunis | yahoo.com |
7000 | Tom | Cruise | oracle.com |
8000 | Johnny | Depp | NULL |
9000 | Russell | Crowe | google.com |
И таблица orders со следующими данными:
order_id | customer_id | order_date |
---|---|---|
1 | 7000 | 2019/06/18 |
2 | 5000 | 2019/06/18 |
3 | 8000 | 2019/06/19 |
4 | 4000 | 2019/06/20 |
5 | NULL | 2019/07/01 |
Выполним следующий SQL оператор:
SELECT customers.customer_id,
orders.order_id,
orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id; |
Будет выбрано 4 записи. Вот результаты, которые вы должны получить:
customer_id | order_id | order_date |
---|---|---|
4000 | 4 | 2019/06/20 |
5000 | 2 | 2019/06/18 |
7000 | 1 | 2019/06/18 |
8000 | 3 | 2019/06/19 |
В этом примере будут возвращены все строки из таблиц customers и orders, в которых совпадают значения поля customer_id в обоих таблицах.
Строки, где значение customer_id равен 6000 и 9000 в таблице customers, будут опущены, поскольку они не существуют в обеих таблицах. Строка, в которой значение order_id равно 5 из таблицы orders, будет опущена, поскольку customer_id со значением NULL не существует в таблице customers.
SQL LEFT OUTER JOIN
Другой тип соединения называется LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).
Синтаксис
Синтаксис для LEFT OUTER JOIN в SQL:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных ключевое слово OUTER опущено и записывается просто как LEFT JOIN.
Рисунок
На этом рисунке SQL LEFT OUTER JOIN возвращает затененную область:
SQL LEFT OUTER JOIN возвращает все записи из table1 и только те записи из table2, которые пересекаются с table1.
Пример
Теперь давайте рассмотрим пример, который показывает, как использовать LEFT OUTER JOIN в операторе SELECT.
Используя ту же таблицу customers, что и в предыдущем примере:
customer_id | first_name | last_name | favorite_website |
---|---|---|---|
4000 | Justin | Bieber | google.com |
5000 | Selena | Gomez | bing.com |
6000 | Mila | Kunis | yahoo.com |
7000 | Tom | Cruise | oracle.com |
8000 | Johnny | Depp | NULL |
9000 | Russell | Crowe | google.com |
И таблицу orders со следующими данными:
order_id | customer_id | order_date |
---|---|---|
1 | 7000 | 2019/06/18 |
2 | 5000 | 2019/06/18 |
3 | 8000 | 2019/06/19 |
4 | 4000 | 2019/06/20 |
5 | NULL | 2019/07/01 |
Введите следующий SQL оператор:
SELECT customers.customer_id,
orders.order_id,
orders.order_date
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id; |
Будет выбрано 6 записей. Вот результаты, которые вы получите:
customer_id | order_id | order_date |
---|---|---|
4000 | 4 | 2019/06/20 |
5000 | 2 | 2019/06/18 |
6000 | NULL | NULL |
7000 | 1 | 2019/06/18 |
8000 | 3 | 2019/06/19 |
9000 | NULL | NULL |
Этот пример LEFT OUTER JOIN вернул бы все строки из таблицы customers и только те строки из таблицы orders, в которых объединенные поля равны.
Если значение customer_id в таблице customers не существует в таблице orders, все поля таблицы orders будут отображаться как NULL в наборе результатов. Как вы можете видеть, строки, где customer_id равен 6000 и 9000, будут включены в LEFT OUTER JOIN, но поля order_id и order_date отображают NULL.
SQL RIGHT OUTER JOIN JOIN
Другой тип соединения называется SQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).
Синтаксис
Синтаксис для RIGHT OUTER JOIN в SQL:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных ключевое слово OUTER опущено и записывается просто как RIGHT JOIN.
Рисунок
На этом рисунке SQL RIGHT OUTER JOIN возвращает затененную область:
SQL RIGHT OUTER JOIN возвращает все записи из table2 и только те записи из table1, которые пересекаются с table2.
Пример
Теперь давайте рассмотрим пример, который показывает, как использовать RIGHT OUTER JOIN в операторе SELECT.
customer_id | first_name | last_name | favorite_website |
---|---|---|---|
4000 | Justin | Bieber | google.com |
5000 | Selena | Gomez | bing.com |
6000 | Mila | Kunis | yahoo.com |
7000 | Tom | Cruise | oracle.com |
8000 | Johnny | Depp | NULL |
9000 | Russell | Crowe | google.com |
И таблицу orders со следующими данными:
order_id | customer_id | order_date |
---|---|---|
1 | 7000 | 2019/06/18 |
2 | 5000 | 2019/06/18 |
3 | 8000 | 2019/06/19 |
4 | 4000 | 2019/06/20 |
5 | NULL | 2019/07/01 |
Введите следующий SQL оператор:
SELECT customers.customer_id,
orders.order_id,
orders.order_date
FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id; |
Будет выбрано 5 записей. Вот результаты, которые вы должны получить:
customer_id | order_id | order_date |
---|---|---|
NULL | 5 | 2019/07/01 |
4000 | 4 | 2019/06/20 |
5000 | 2 | 2019/06/18 |
7000 | 1 | 2019/06/18 |
8000 | 3 | 2019/06/19 |
Этот пример RIGHT OUTER JOIN вернул бы все строки из таблицы orders и только те строки из таблицы customers, где объединенные поля равны.
Если значение customer_id в таблице orders не существует в таблице customers, то все поля в таблице customers будут отображаться как NULL в наборе результатов. Как видите, строка, где order_id равен 5, будет включена в RIGHT OUTER JOIN, но в поле customer_id отображается NULL.
SQL FULL OUTER JOIN
Другой тип объединения называется SQL FULL OUTER JOIN. Этот тип объединения возвращает все строки из LEFT таблицы и RIGHT таблицы со значениями NULL в месте, где условие соединения не выполняется.
Синтаксис
Синтаксис для SQL FULL OUTER JOIN:
SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных ключевое слово OUTER опускается и записывается просто как FULL JOIN.
Рисунок
На этом рисунке SQL FULL OUTER JOIN возвращает затененную область:
SQL FULL OUTER JOIN возвращает все записи из таблиц table1 и table2.
Пример
Давайте рассмотрим пример, который показывает, как использовать FULL OUTER JOIN в операторе SELECT.
Используя ту же таблицу customers, что и в предыдущем примере:
customer_id | first_name | last_name | favorite_website |
---|---|---|---|
4000 | Justin | Bieber | google.com |
5000 | Selena | Gomez | bing.com |
6000 | Mila | Kunis | yahoo.com |
7000 | Tom | Cruise | oracle.com |
8000 | Johnny | Depp | NULL |
9000 | Russell | Crowe | google.com |
И таблицу orders со следующими данными:
order_id | customer_id | order_date |
---|---|---|
1 | 7000 | 2019/06/18 |
2 | 5000 | 2019/06/18 |
3 | 8000 | 2019/06/19 |
4 | 4000 | 2019/06/20 |
5 | NULL | 2019/07/01 |
Введите следующий 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 | order_id | order_date |
---|---|---|
NULL | 5 | 2019/07/01 |
4000 | 4 | 2019/06/20 |
5000 | 2 | 2019/06/18 |
6000 | NULL | NULL |
7000 | 1 | 2019/06/18 |
8000 | 3 | 2019/06/19 |
9000 | NULL | NULL |
Это пример FULL OUTER JOIN будет возвращать все строки из таблицы orders и все строки из таблицы customers. Всякий раз, когда условие соединения не выполняется, значение NULL будет распространяться на эти поля в наборе результатов. Это означает, что если значение customer_id в таблице customers не существует в таблице orders, то все поля в таблице orders будут отображаться в наборе результатов как NULL Кроме того, если значение customer_id в таблице orders не существует в таблице customers, то все поля в таблице customers будут отображаться в наборе результатов как NULL.
Как видите, строки, где customer_id равен 6000 и 9000, будут включены, но поля order_id и order_date для этих записей содержат значение NULL. Строка, где order_id равен 5, также будет включена, но поле customer_id для этой записи имеет значение NULL.
Соединение таблиц или действие оператора SQL JOIN на примерах : WEBCodius
Оператор JOIN используется для соединения двух или нескольких таблиц. Соединение таблиц может быть внутренним (INNER) или внешним (OUTER), причем внешнее соединение может быть левым (LEFT), правым (RIGHT) или полным (FULL). Далее на примере двух таблиц рассмотрим различные варианты их соединения.
Синтаксис соединения таблиц оператором JOIN имеет вид:
FROM <таблица 1> [INNER] {{LEFT | RIGHT | FULL } [OUTER]} JOIN <таблица 2> [ON <предикат>]
Предикат в этой конструкции определяет условие соединения строк из разных таблиц.
Допустим есть две таблицы (Auto слева и Selling справа), в каждой по четыре записи. Одна таблица содержит названия марок автомобилей (Auto), вторая количество проданных автомобилей (Selling):
id name id sum -- ---- -- ---- 1 bmw 1 250 2 opel 5 450 3 kia 3 300 4 audi 6 400
Далее соединим эти таблицы по полю id несколькими различными способами. Совпадающие значения выделены красным для лучшего восприятия.
1. Внутреннее соединение (INNER JOIN) означает, что в результирующий набор попадут только те соединения строк двух таблиц, для которых значение предиката равно TRUE. Обычно используется для объединения записей, которые есть и в первой и во второй таблице, т. е. получения пересечения таблиц:
Красным выделена область, которую мы должны получить.
Итак, сам запрос:
SELECT * FROM 'Auto' INNER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id
И результат:
id name id sum -- ---- -- ---- 1 bmw 1 250 3 kia 3 300
Ключевое слово INNER в запросе можно опустить.
В итоге запрос отбирает и соединяет те записи, у которых значение поля id в обоих таблицах совпадает.
2. Внешнее соединение (OUTER JOIN) бывает нескольких видов. Первым рассмотрим полное внешнее объединение (FULL OUTER JOIN), которое объединяет записи из обоих таблиц (если условие объединения равно true) и дополняет их всеми записями из обоих таблиц, которые не имеют совпадений. Для записей, которые не имеют совпадений из другой таблицы, недостающее поле будет иметь значение NULL. Граф выборки записей будет иметь вид:
Переходим к запросу:
SELECT * FROM 'Auto' FULL OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id
Результат:
id name id sum -- ---- -- ---- 1 bmw 1 250 2 opel NULL NULL 3 kia 3 300 4 audi NULL NULL NULL NULL 5 450 NULL NULL 6 400
То есть мы получили все записи, которые есть в обоих таблицах. Записи у которых значение поля id совпадает соединяются, а у записей для которых совпадений не найдено недостающие поля заполняются значением NULL.
Ключевое слово OUTER можно опустить.
3. Левое внешнее объединение (LEFT OUTER JOIN). В этом случае получаем все записи удовлетворяющие условию объединения, плюс все оставшиеся записи из внешней таблицы, которые не удовлетворяют условию объединения. Граф выборки:
Запрос:
SELECT * FROM 'Auto' LEFT OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id
Результат:
id name id sum -- ---- -- ---- 1 bmw 1 250 2 opel NULL NULL 3 kia 3 300 4 audi NULL NULL
Запрос также можно писать без ключевого слова OUTER.
В итоге здесь мы получили все записи таблицы Auto. Записи для которых были найдены совпадения по полю id в таблице Selling соединяются, для остальных недостающие поля заполняются значением NULL.
Еще существует правое внешнее объединение (RIGHT OUTER JOIN). Оно работает точно также как и левое объединение, только в качестве внешней таблицы будет использоваться правая (в нашем случае таблица Selling или таблица Б на графе).
Далее рассмотрим остальные возможные выборки с использованием объединения двух таблиц.
4. Получить все записи из таблицы А, которые не имеют объединения из таблицы Б. Граф:
То есть в нашем случае, нам надо получить все автомобили из таблицы Auto, которые не имеют продаж в таблице Selling.
Запрос:
SELECT * FROM 'Auto' LEFT OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id WHERE 'Selling'.id IS null
Результат:
id name id sum -- ---- -- ---- 2 opel NULL NULL 4 audi NULL NULL
5. И последний вариант, получить все записи из таблицы А и Таблицы Б, которые не имеют объединений. Граф:
В нашем случае мы должны получить все записи из таблицы Auto, которые не связаны с таблицей Selling, и все записи из таблицы Selling, которые не имеют сопоставления из таблицы Auto.
Запрос:
SELECT * FROM 'Auto' FULL OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id WHERE 'Auto'.id IS null OR 'Selling'.id IS null
Результат:
id name id sum -- ---- -- ---- 2 opel NULL NULL 4 audi NULL NULL NULL NULL 5 450 NULL NULL 6 400
На этом все, до новых встреч на страницах блога.
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
Содержание:
Говоря про соединение таблиц в SQL, обычно подразумевают один из видов операции JOIN. Не стоит путать с объединением таблиц через операцию UNION. В этой статье я постараюсь простыми словами рассказать именно про соединение, чтобы после ее прочтения Вы могли использовать джойны в работе и не допускать грубых ошибок.
Соединение – это операция, когда таблицы сравниваются между собой построчно и появляется возможность вывода столбцов из всех таблиц, участвующих в соединении.
Придумаем 2 таблицы, на которых будем тренироваться.
Таблица «Сотрудники», содержит поля:
- id – идентификатор сотрудника
- Имя
- Отдел – идентификатор отдела, в котором работает сотрудник
id | Имя | Отдел |
---|---|---|
1 | Юлия | 1 |
2 | Федор | 2 |
3 | Алексей | NULL |
4 | Светлана | 2 |
Таблица «Отделы», содержит поля:
- id – идентификатор отдела
- Наименование
id | Наименование |
---|---|
1 | Кухня |
2 | Бар |
3 | Администрация |
Давайте уже быстрее что-нибудь покодим.
INNER JOIN
Самый простой вид соединения INNER JOIN – внутреннее соединение. Этот вид джойна выведет только те строки, если условие соединения выполняется (является истинным, т.е. TRUE). В запросах необязательно прописывать INNER – если написать только JOIN, то СУБД по умолчанию выполнить именно внутреннее соединение.
Давайте соединим таблицы из нашего примера, чтобы ответить на вопрос, в каких отделах работают сотрудники (читайте комментарии в запросе для понимания синтаксиса).
SELECT -- Перечисляем столбцы, которые хотим вывести Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел -- выводим наименование отдела и переименовываем столбец через as FROM -- таблицы для соединения перечисляем в предложении from Сотрудники -- обратите внимание, что мы не указали вид соединения, поэтому выполнится внутренний (inner) джойн JOIN Отделы -- условия соединения прописываются после ON -- условий может быть несколько, записанных через and, or и т.п. ON Сотрудники.Отдел = Отделы.id
Получим следующий результат:
id | Имя | Отдел |
---|---|---|
1 | Юлия | Кухня |
2 | Федор | Бар |
4 | Светлана | Бар |
Из результатов пропал сотрудник Алексей (id = 3), потому что условие «Сотрудники.Отдел = Отделы.id» не будет истинно для этой сроки из таблицы «Сотрудники» с каждой строкой из таблицы «Отделы». По той же логике в результате нет отдела «Администрация». Попробую это визуализировать (зеленные линии – условие TRUE, иначе линия красная):
Если не углубляться в то, как внутреннее соединение работает под капотом СУБД, то происходит примерно следующее:
- Каждая строка из одной таблицы сравнивается с каждой строкой из другой таблицы
- Строка возвращается, если условие сравнения является истинным
Если для одной или нескольких срок из левой таблицы (в рассмотренном примере левой таблицей является «Сотрудники», а правой «Отделы») истинным условием соединения будут являться одна или несколько срок из правой таблицы, то строки умножат друг друга (повторятся). В нашем примере так произошло для отдела с id = 2, поэтому строка из таблицы «Отделы» повторилась дважды для Федора и Светланы.
Перемножение таблиц проще ощутить на таком примере, где условие соединения будет всегда возвращать TRUE, например 1=1:
SELECT * FROM Сотрудники JOIN Отделы ON 1=1
В результате получится 12 строк (4 сотрудника * 3 отдела), где для каждого сотрудника подтянется каждый отдел.
Также хочу сразу отметить, что в соединении может участвовать сколько угодно таблиц, можно таблицу соединить даже саму с собой (в аналитических задачах это не редкость). Какая из таблиц будет правой или левой не имеется значения для INNER JOIN (для внешних соединений типа LEFT JOIN или RIGHT JOIN это важно. Читайте далее). Пример соединения 4-х таблиц:
SELECT * FROM Table_1 JOIN Table_2 ON Table_1.Column_1 = Table_2.Column_1 JOIN Table_3 ON Table_1.Column_1 = Table_3.Column_1 AND Table_2.Column_1 = Table_3.Column_1 JOIN Table_1 AS Tbl_1 -- Задаем алиас для таблицы, чтобы избежать неоднозначности -- Если в Table_1.Column_1 хранится порядковый номер какого-то объекта, -- то так можно присоединить следующий по порядку объект ON Table_1.Column_1 = Tbl_1.Column_1 + 1
Как видите, все просто, прописываем новый джойн после завершения условий предыдущего соединения. Обратите внимание, что для Table_3 указано несколько условий соединения с двумя разными таблицами, а также Table_1 соединяется сама с собой по условию с использованием сложения.
Строки, которые выведутся запросом, должны совпасть по всем условиям. Например:
- Строка из Table_1 соединилась со строкой из Table_2 по условию первого JOIN. Давайте назовем ее «объединенной строкой» из двух таблиц;
- Объединенная строка успешно соединилась с Table_3 по условию второго JOIN и теперь состоит из трех таблиц;
- Для объединенной строки не нашлось строки из Table_1 по условию третьего JOIN, поэтому она не выводится вообще.
На этом про внутреннее соединение и логику соединения таблиц в SQL – всё. Если остались неясности, то спрашивайте в комментариях.
Далее рассмотрим отличия остальных видов джойнов.
LEFT JOIN и RIGHT JOIN
Левое и правое соединения еще называют внешними. Главное их отличие от внутреннего соединения в том, что строка из левой (для LEFT JOIN) или из правой таблицы (для RIGHT JOIN) попадет в результаты в любом случае.
Давайте до конца определимся с тем, какая таблица левая, а какая правая.
Левая таблица та, которая идет перед написанием ключевых слов [LEFT | RIGHT| INNER] JOIN, правая таблица – после них:
SELECT * FROM Левая_таблица AS lt LEFT JOIN Правая_таблица AS rt ON lt.c = rt.c
Теперь изменим наш SQL-запрос из самого первого примера так, чтобы ответить на вопрос «В каких отделах работают сотрудники, а также показать тех, кто не распределен ни в один отдел?»:
SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Сотрудники LEFT JOIN Отделы -- добавляем только left ON Сотрудники.Отдел = Отделы.id
Результат запроса будет следующим:
id | Имя | Отдел |
---|---|---|
1 | Юлия | Кухня |
2 | Федор | Бар |
3 | Алексей | NULL |
4 | Светлана | Бар |
Как видите, запрос вернул все строки из левой таблицы «Сотрудники», дополнив их значениями из правой таблицы «Отделы». А вот строка для отдела «Администрация» не показана, т.к. для нее не нашлось совпадений слева.
Это мы рассмотрели пример для левого внешнего соединения. Для RIGHT JOIN будет все тоже самое, только вернутся все строки из таблицы «Отделы»:
id | Имя | Отдел |
---|---|---|
1 | Юлия | Кухня |
2 | Федор | Бар |
4 | Светлана | Бар |
NULL | NULL | Администрация |
Алексей «потерялся», Администрация «нашлась».
Вопрос для Вас. Что надо изменить в последнем приведенном SQL-запросе, чтобы результат остался тем же, но вместо LEFT JOIN, использовался RIGHT JOIN?
Ответ. Нужно поменять таблицы местами:
SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Отделы RIGHT JOIN Сотрудники ON Сотрудники.Отдел = Отделы.id
В одном запросе можно применять и внутренние соединения, и внешние одновременно, главное соблюдать порядок таблиц, чтобы не потерять часть записей (строк).
FULL JOIN
Еще один вид соединения, который осталось рассмотреть – полное внешнее соединение.
Этот вид джойна вернет все строки из всех таблиц, участвующих в соединении, соединив между собой те, которые подошли под условие ON.
Давайте посмотрим всех сотрудников и все отделы из наших тестовых таблиц:
SELECT Сотрудники.id, Сотрудники.Имя, Отделы.Наименование AS Отдел FROM Сотрудники FULL JOIN Отделы ON Сотрудники.Отдел = Отделы.id
id | Имя | Отдел |
---|---|---|
1 | Юлия | Кухня |
2 | Федор | Бар |
3 | Алексей | NULL |
4 | Светлана | Бар |
NULL | NULL |
SQL join в примерах с описанием
Присоединение таблиц в запросах — это базовый инструмент в работе с базами данных. Давайте рассмотрим какие присоединения (JOIN) бывают, и что от этого меняется в результатах запроса.
Для начала создадим две таблицы, над которыми будем проводить опыты. Это таблица с именами сотрудников и словарь с перечнем должностей.
Persons (Сотрудники)
Positions (должности)
Т.е. чтобы узнать должность сотрудника, нужно присоединить соответствующие данные. Далее мы рассмотрим все варианты присоединений. Данные специально подобраны так, чтобы продемонстрировать отличия в результатах разных запросов.
INNER JOIN
Внутреннее присоединение. Равносильно просто JOIN или CROSS JOIN.
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
INNER JOIN `positions` ps ON ps.id = p.post_id
| SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p INNER JOIN `positions` ps ON ps.id = p.post_id |
Такое присоединение покажет нам данные из таблиц только если условие связывания соблюдается — т.е. для сотрудника указан существующий в словаре идентификатор должности.
Если поменять порядок соединения таблиц — получим тот же результат.
Условно представим себе эти таблицы, как пересекающиеся множества, где пересечение — это наличие связи между таблицами. Получим картинку:
Далее проследим как получить разные части (подмножества) данного множества.
OUTER JOIN
Внешнее присоединение. Различают LEFT OUTER JOIN и RIGHT OUTER JOIN, и обычно опускают слово «OUTER».
Внешнее присоединение включает в себя результаты запроса INNER и добавляются «неиспользованные» строки из одной из таблиц. Какую таблицу использовать в качестве «добавки» — указывает токен LEFT или RIGHT.
LEFT JOIN
Внешнее присоединение «слева».
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id
| SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id |
«Левая» таблица persons, содержит строку id#3 — «Александр», где указан идентификатор должности, отсутствующей в словаре.
На картинке это можно показать вот так:
RIGHT JOIN
Присоединение «справа».
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id
| SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id |
Словарь должностей (правая таблица) содержит неиспользуемую запись с id#3 — «программист». Теперь она попала в результат запроса.
Полное множество
MySQL не знает соединения FULL OUTER JOIN. Что если нужно получить полное множество?
Первый способ — объединение запросов LEFT и RIGHT.
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id)
UNION
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id)
| (SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id) UNION (SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id) |
При таком вызове UNION, после слияния результатов, SQL отсечет дубли (как DISTINCT). Для отсечения дублей SQL прибегает к сортировке. Это может сказываться на быстродействии.
Второй способ — объединение LEFT и RIGHT, но в одном из запросов мы исключаем часть, соответствующую INNER. А объединение задаём как UNION ALL, что позволяет движку SQL обойтись без сортировки.
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id)
UNION ALL
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE p.id IS NULL)
| (SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id) UNION ALL (SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE p.id IS NULL) |
Этот пример показывает нам как исключить пересечение и получить только левую или правую часть множества.
Левое подмножество
LEFT JOIN ограничиваем проверкой, что данных из второй таблицы нет.
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE ps.id is NULL
| SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE ps.id is NULL |
В нашем примере — это специалисты, у которых не задана должность или нет должности с указанным ключом.
Правое подмножество
Точно также выделяем правую часть.
SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE p.id is NULL
| SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE p.id is NULL |
В нашем случае получим должности, которые никому не назначены.
Всё кроме пересечения
Остался один вариант, тот когда исключено пересечение множеств. Его можно сложить из двух предыдущих запросов через UNION ALL (т.к. подмножества не пересекаются).
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE ps.id is NULL)
UNION ALL
(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`
FROM `persons` p
RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id
WHERE p.id is NULL)
| (SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE ps.id is NULL) UNION ALL (SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE p.id is NULL) |
Данная запись опубликована в 19.09.2017 20:19 и размещена в mySQL.
Вы можете перейти в конец страницы и оставить ваш комментарий.
Многотабличные запросы, оператор JOIN
Многотабличные запросы
В предыдущих статьях описывалась работа только с одной таблицей базы данных. В реальности же очень часто приходится делать выборку из нескольких таблиц, каким-то образом объединяя их. В данной статье вы узнаете основные способы соединения таблиц.
Общая структура многотабличного запроса
MySQL
SELECT поля_таблиц
FROM таблица_1
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2
ON условие_соединения
[[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_n
ON условие_соединения]
Эту же структуру можно переписать следующим образом:
MySQL
SELECT поля_таблиц
FROM таблица_1
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2[ JOIN таблица_n]
ON условие_соединения [AND условие_соединения]
В большинстве случаев условием соединения является равенство столбцов таблиц (таблица_1.поле = таблица_2.поле), однако точно так же можно использовать и другие операторы сравнения.
Соединение бывает внутренним (INNER) или внешним (OUTER), при этом внешнее соединение делится на левое (LEFT), правое (RIGHT) и полное (FULL).
INNER JOIN
По умолчанию, если не указаны какие-либо параметры, JOIN выполняется как INNER JOIN, то есть как внутреннее (перекрёстное) соединение таблиц.
Внутреннее соединение — соединение двух таблиц, при котором каждая запись из первой таблицы соединяется с каждой записью второй таблицы, создавая тем самым все возможные комбинации записей обеих таблиц (декартово произведение).
Например, объединим таблицы покупок (Payments) и членов семьи (FamilyMembers) таким образом, чтобы дополнить каждую покупку данными о том, кто её совершил.
Данные в таблице Payments:
Данные в таблице FamilyMembers:
Для того, чтобы решить поставленную задачу выполним запрос, который объединяет поля строки из одной таблицы с полями другой, если выполняется условие, что покупатель товара (family_member) совпадает с идентификатором члена семьи (member_id):
MySQL
SELECT *
FROM Payments
JOIN FamilyMembers ON family_member = member_id;
В результате вы можете видеть, что каждая строка из таблицы Payments дополнилась данными о члене семьи, который совершил покупку. Обратите внимание на поля family_member и member_id — они одинаковы, что и было отражено в запросе.
Использование WHERE для соединения таблиц
Для внутреннего соединения таблиц также можно использовать оператор WHERE. Например, вышеприведённый запрос, написанный с помощью INNER JOIN, будет выглядеть так:
MySQL
SELECT *
FROM Payments, FamilyMembers
WHERE family_member = member_id;
OUTER JOIN
Внешнее соединение может быть трёх типов: левое (LEFT), правое (RIGHT) и полное (FULL). По умолчанию оно является полным.
Главным отличием внешнего соединения от внутреннего является то, что оно обязательно возвращает все строки одной (LEFT, RIGHT) или двух таблиц (FULL).
Внешнее левое соединение (LEFT OUTER JOIN)
Соединение, которое возвращает все значения из левой таблицы, соединённые с соответствующими значениями из правой таблицы если они удовлетворяют условию соединения, или заменяет их на NULL в обратном случае.
Для примера получим из базы данных расписание звонков объединённых с соответствующими занятиями в расписании занятий:
MySQL
SELECT *
FROM Timepair
LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;
Данные в таблице Timepair (расписание звонков):
Данные в таблице Schedule (расписание занятий):
В выборку попали все строки из левой таблицы, дополненные данными о занятиях. Примечательно, что в конце таблицы есть строки с полями, заполненными NULL. Это те строки, для которых не нашлось соответствующих занятий, однако они присутствуют в левой таблице, поэтому тоже были выведены.
Внешнее правое соединение (RIGHT OUTER JOIN)
Соединение, которое возвращает все значения из правой таблицы, соединённые с соответствующими значениями из левой таблицы если они удовлетворяют условию соединения, или заменяет их на NULL в обратном случае.
Внешнее полное соединение (FULL OUTER JOIN)
Соединение, которое выполняет внутреннее соединение записей и дополняет их левым внешним соединением и правым внешним соединением.
Алгоритм работы полного соединения:
- Формируется таблица на основе внутреннего соединения (INNER JOIN).
- В таблицу добавляются значения не вошедшие в результат формирования из левой таблицы (LEFT OUTER JOIN).
- В таблицу добавляются значения не вошедшие в результат формирования из правой таблицы (RIGHT OUTER JOIN).
Соединение FULL JOIN реализовано не во всех СУБД. Например, в MySQL оно отсутствует, однако его можно очень просто эмулировать:MySQL
SELECT *
FROM левая_таблица
LEFT JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
UNION ALL
SELECT *
FROM левая_таблица
RIGHT JOIN правая_таблица
ON правая_таблица.ключ = левая_таблица.ключ
WHERE левая_таблица.key IS NULL
Базовые запросы для разных вариантов объединения таблиц
Группировка данных, оператор GROUP BY, оператор HAVING
Оператор соединения JOIN SQL — CodeTown.ru
Доброго времени суток! В этой статье по языку SQL мы познакомимся с оператором соединения двух таблиц — JOIN. Как и всегда, разберем практические примеры и посмотрим на различные варианты применения оператора JOIN в SQL.
Прежде чем перейти к самой статье, настоятельно рекомендую освоить предыдущие записи в этом курсе, чтобы, как минимум, знать структуру используемых таблиц.
Введение
В прошлых статьях мы уже работали с запросами, которые используют данные из двух и более таблиц: многотабличные запросы и вложенные запросы в SQL. Но те способы имели свои минусы либо по скорости, либо по избыточности данных. Оператор соединения JOIN наиболее выгоден среди этих способов, но его использование подразумевает операции только с двумя таблицами. Тем не менее, запросы выполняются гораздо быстрее, чем вложенные запросы.
В этой статье мы разберем несколько вариантов применения оператора JOIN:
- INNER JOIN
- OUTER JOIN
- RIGHT OUTER JOIN
- LEFT OUTER JOIN
Про эти варианты использования мы и поговорим подробнее.
Оператор INNER JOIN
Этот оператор осуществляет симметричное соединение — это означает, что ему неважен порядок тех двух таблиц, которые соединяются. По своей сути все операторы SQL в данной статье похожи с вложенными запросами, но алгоритм работы отличается, в этом и преимущество. Разберем пример из прошлой статьи, но выполним его уже по-другому:
Вывести сумму заказов и дату, которые проводил продавец с фамилией Колованов. Используйте оператор INNER JOIN.
SELECT amt, odate FROM orders INNER JOIN salespeople on orders.snum = salespeople.snum and sname = 'Колованов'
В этом запросе четко видно, что мы берем два поля из таблицы orders, а затем присоединяем таблицу salespeople с нужными нам условиями, которые задаются после ключевого слова ON. В данном случае проверка идет по snum и фамилии продавца. Вывод запроса аналогичен предыдущей статье:
amt | odate |
---|---|
348 | 2017-04-08 |
80 | 2017-09-02 |
Рассмотрим еще один пример на оператор INNER JOIN, уже сложнее:
Вывести среднюю суму заказов для каждого продавца.
SELECT AVG(amt) as 'Средняя цена', salespeople.sname FROM orders INNER JOIN salespeople on orders.snum = salespeople.snum GROUP BY salespeople.sname
В этом запросе уже выводятся два поля из разных таблиц, помимо этого происходит группировка по фамилиям продавца.
Средняя цена | sname |
---|---|
214 | Колованов |
315.667 | Кучеров |
1180 | Мозякин |
640 | Плотников |
900 | Проворов |
Стоит отметить, что при группировке в SQL стоит быть внимательнее, так как при выборе сразу нескольких полей из нескольких таблиц группировка может вернуть ошибку. Поэтому в этом варианте решения правильнее использовать 2 поля для вывода.
Также заметьте, что всего в нашей таблицы 8 продавцов, а тут всего 5 строк — просто у других продавцов нет заказов в таблице orders.
Оператор OUTER JOIN
В языке SQL оператор OUTER JOIN используется гораздо реже, но иногда является очень полезным. Сейчас мы рассмотрим два варианта использования этого оператора. Оператор осуществляет несимметричное внешнее соединение двух таблиц — то есть порядок таблиц важен.
Оператор RIGHT OUTER JOIN
Правое внешнее соединение необходимо тогда, когда при соединении двух таблиц мы хотим показать все данные второй таблицы, даже если этим данным соответствуют нулевые значения первой таблицы. Чтобы было понятнее перейдем к примеру. За основу возьмем предыдущий код и поменяем в нем один оператор SQL.
SELECT AVG(amt) as 'Средняя цена', salespeople.sname FROM orders RIGHT OUTER JOIN salespeople on orders.snum = salespeople.snum GROUP BY salespeople.sname
И вот, что поменялось в выводе:
Средняя цена | sname |
---|---|
214 | Колованов |
315.667 | Кучеров |
NULL | Малкин |
1180 | Мозякин |
NULL | Петров |
640 | Плотников |
900 | Проворов |
NULL | Шипачев |
Как уже было сказано, такой запрос покажет все значения для второй таблицы (то есть правой), даже если у них нет значений в левой таблице — стоит NULL. Посмотрите еще раз на вывод предыдущего запроса с INNER JOIN и этот, и проанализируйте разницу.
Оператор LEFT OUTER JOIN
Аналогичным образом работает и оператор левого внешнего соединения в SQL. При его использовании покажутся все значения для левой таблицы, даже если в правой им соответствуют нулевые значения. Рассмотрим еще один ознакомительный пример:
Вывести дату заказов и фамилии абсолютно всех покупателей для этих заказов (если покупатель не совершал заказ, то его фамилию тоже необходимо вывести.
Итак, нам нужны все покупатели — значит в качестве первой (левой) таблицы возьмем таблицу customers, а затем будем присоединять таблицу orders.
SELECT customers.cname, odate FROM customers LEFT OUTER JOIN orders on orders.cnum = customers.cnum
Вывод:
cname | odate |
---|---|
Чудинов | 2016-01-01 |
Лосев | 2016-04-10 |
Краснов | 2017-04-08 |
Кириллов | 2016-06-07 |
Колесников | 2017-12-04 |
Колесников | 2016-03-03 |
Лермонтов | 2017-09-02 |
Деснов | 2016-03-07 |
Кириллов | 2017-10-07 |
Пушкин | 2016-01-08 |
Ермолаев | NULL |
Белый | NULL |
Очевидно, что в выводе присутствуют все фамилии покупателей, а некоторые даже несколько раз, потому что не было никаких условий или группировок.
Примеры на соединение таблиц в SQL
1.Напишите запрос, который бы использовал оператор INNER JOIN для получения всех Заказов для покупателя с фамилией Краснов.
SELECT onum, amt, odate, cname FROM orders INNER JOIN customers on orders.cnum = customers.cnum and cname = 'Краснов'
2.Напишите запрос, который бы вывел фамилии и города продавцов, комиссия которых составляет более 20%.
SELECT DISTINCT(sname), city, comm FROM salespeople INNER JOIN orders on orders.snum = salespeople.snum and comm > 20
3.Напишите запрос, который бы вывел суммарную сумму заказов для городов в которых работают продавцы.
SELECT SUM(amt), salespeople.city FROM orders INNER JOIN salespeople on orders.snum = salespeople.snum GROUP BY salespeople.city
4.Повторите предыдущий запрос, но выведите все города, даже если в них не совершалась сделка.
SELECT SUM(amt), salespeople.city FROM orders RIGHT OUTER JOIN salespeople on orders.snum = salespeople.snum GROUP BY salespeople.city
5.Напишите запрос, который бы вывел максимальную сумму заказов для городов в которых проживают покупатели, даже если в этих городах не было произведено сделки.
SELECT customers.city, MAX(amt) FROM customers LEFT OUTER JOIN orders on orders.cnum = customers.cnum GROUP BY customers.city
Заключение
После ознакомления с этой статьей у вас должно появиться понимание как работает оператор JOIN в SQL. Его особенности и преимущества над вложенными запросами мы отметили и рекомендуем в своих практических задачах пользоваться именно такими конструкциями. На этом все. Не забывайте оставлять ваши комментарии. До следующей статьи.
Поделиться ссылкой:
Похожее
SQL присоединяется к
SQL JOIN
Предложение JOIN используется для объединения строк из двух или более таблиц на основе
связанный столбец между ними.
Рассмотрим выборку из таблицы «Заказы»:
Код заказа | Идентификатор клиента | Дата заказа |
---|---|---|
10308 | 2 | 1996-09-18 |
10309 | 37 | 1996-09-19 |
10310 | 77 | 1996-09-20 |
Затем посмотрите на выбор из таблицы «Клиенты»:
Идентификатор клиента | Имя клиента | ContactName | Страна |
---|---|---|---|
1 | Альфредс Футтеркисте | Мария Андерс | Германия |
2 | Ana Trujillo Emparedados y helados | Ана Трухильо | Мексика |
3 | Антонио Морено Такерия | Антонио Морено | Мексика |
Обратите внимание, что столбец «CustomerID» в таблице «Заказы» относится к
«CustomerID» в таблице «Клиенты».Связь между двумя таблицами выше
столбец «CustomerID».
Затем мы можем создать следующий оператор SQL (который содержит INNER JOIN),
который выбирает записи, которые имеют совпадающие значения в обеих таблицах:
Пример
ВЫБЕРИТЕ Orders.OrderID, Customers.CustomerName, Orders.OrderDate
ИЗ Orders
ВНУТРЕННЕЕ ПРИСОЕДИНЯЙТЕСЬ к клиентам НА Orders.CustomerID = Customers.CustomerID;
Попробуй сам »
, и он выдаст что-то вроде этого:
Код заказа | Имя клиента | Дата заказа |
---|---|---|
10308 | Ana Trujillo Emparedados y helados | 18.09.1996 |
10365 | Антонио Морено Такерия | 27.11.1996 |
10383 | Вокруг Рога | 16.12.1996 |
10355 | Вокруг Рога | 15.11.1996 |
10278 | Berglunds snabbköp | 12.08.1996 |
Различные типы SQL JOIN
Вот различные типы JOIN в SQL:
- (INNER) JOIN : возвращает записи, которые имеют совпадающие значения в обеих таблицах
- LEFT (OUTER) JOIN : возвращает все записи из левой таблицы и соответствующие записи из правой таблицы
- RIGHT (OUTER) JOIN : возвращает все записи из правой таблицы и совпавшие
записи из левой таблицы - ПОЛНОЕ (ВНЕШНЕЕ) СОЕДИНЕНИЕ : возвращает все записи при совпадении в любом из левых
или правый стол
.
SQL: ПРИСОЕДИНЯЕТСЯ к
В этом руководстве по SQL объясняется, как использовать SQL JOINS с синтаксисом, наглядными иллюстрациями и примерами.
Описание
SQL JOINS используются для извлечения данных из нескольких таблиц. SQL JOIN выполняется всякий раз, когда две или более таблицы перечислены в операторе SQL.
Существует 4 различных типа соединений SQL:
- SQL INNER JOIN (иногда называется простым соединением)
- SQL LEFT OUTER JOIN (иногда называется LEFT JOIN)
- SQL RIGHT OUTER JOIN (иногда называется RIGHT JOIN)
- ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ SQL (иногда называемое ПОЛНЫМ СОЕДИНЕНИЕМ)
Итак, давайте обсудим синтаксис SQL JOIN, посмотрим на визуальные иллюстрации SQL JOINS и рассмотрим несколько примеров.
DDL / DML для примеров
Если вы хотите следовать этому руководству, получите DDL для создания таблиц и DML для заполнения данных. Тогда попробуйте примеры в своей базе данных!
Получить DDL / DML
SQL INNER JOIN (простое соединение)
Скорее всего, вы уже написали оператор SQL, который использует SQL INNER JOIN. Это наиболее распространенный тип соединения SQL. SQL INNER JOINS возвращает все строки из нескольких таблиц, в которых выполнено условие соединения.
Синтаксис
Синтаксис INNER JOIN в SQL:
ВЫБРАТЬ столбцы ИЗ table1 INNER JOIN table2 НА table1.column = table2.column;
Визуальная иллюстрация
На этой визуальной диаграмме SQL INNER JOIN возвращает заштрихованную область:
SQL INNER JOIN вернет записи, в которых пересекаются table1 и table2 .
Пример
Рассмотрим пример использования INNER JOIN в запросе.
В этом примере у нас есть таблица с именем клиентов со следующими данными:
customer_id | фамилия | имя | любимый_сайт |
---|---|---|---|
4000 | Джексон | Джо | techonthenet.com |
5000 | Смит | Джейн | digminecraft.com |
6000 | Фергюсон | Саманта | bigactivities.com |
7000 | Рейнольдс | Аллен | checkyourmath.com |
8000 | Андерсон | Пейдж | НЕТ |
9000 | Джонсон | Дерек | techonthenet.com |
И таблица под названием заказов со следующими данными:
идентификатор заказа | customer_id | дата заказа |
---|---|---|
1 | 7000 | 18.04.2016 |
2 | 5000 | 18.04.2016 |
3 | 8000 | 2016/04/19 |
4 | 4000 | 2016/04/20 |
5 | НЕТ | 01.05.2016 |
Введите следующий оператор SQL:
Попробуй это
ВЫБРАТЬ клиентов.customer_id, orders.order_id, orders.order_date ОТ клиентов INNER JOIN заказы ВКЛ. Customers.customer_id = orders.customer_id ЗАКАЗАТЬ ПО customers.customer_id;
Будет выбрано 4 записи. Вот результаты, которые вы должны увидеть:
customer_id | order_id | дата заказа |
---|---|---|
4000 | 4 | 2016/04/20 |
5000 | 2 | 18.04.2016 |
7000 | 1 | 18.04.2016 |
8000 | 3 | 2016/04/19 |
В этом примере будут возвращены все строки из таблиц customers и orders , в которых имеется совпадающее значение customer_id в таблицах customers и orders .
Строки, в которых customer_id равно 6000 и 9000, в таблице customers будут опущены, так как их нет в обеих таблицах. Строка, в которой order_id равно 5 из таблицы orders , будет опущена, так как customer_id со значением NULL не существует в таблице customers .
Старый синтаксис
В заключение стоит упомянуть, что приведенный выше пример INNER JOIN можно переписать с использованием старого неявного синтаксиса следующим образом (но мы по-прежнему рекомендуем использовать синтаксис ключевого слова INNER JOIN):
Попробуй это
ВЫБРАТЬ клиентов.customer_id, orders.order_id, orders.order_date ОТ клиентов, заказы ГДЕ customers.customer_id = orders.customer_id ЗАКАЗАТЬ ПО customers.customer_id;
SQL ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
Другой тип соединения называется LEFT OUTER JOIN. Этот тип соединения возвращает все строки из ЛЕВОЙ таблицы, указанной в условии ON, а — только тех строк из другой таблицы, в которых объединенные поля равны (условие соединения выполнено).
Синтаксис
Синтаксис LEFT OUTER JOIN в SQL:
ВЫБРАТЬ столбцы ИЗ table1 LEFT [OUTER] JOIN table2 НА table1.столбец = table2.column;
В некоторых базах данных ключевое слово OUTER опускается и записывается просто как LEFT JOIN.
Визуальная иллюстрация
На этой визуальной диаграмме SQL LEFT OUTER JOIN возвращает заштрихованную область:
SQL LEFT OUTER JOIN вернет все записи из table1 и только те записи из table2 , которые пересекаются с table1 .
Пример
Теперь давайте рассмотрим пример, показывающий, как использовать LEFT OUTER JOIN в операторе SELECT.
Используя ту же таблицу клиентов , что и в предыдущем примере:
customer_id | фамилия | имя | любимый_сайт |
---|---|---|---|
4000 | Джексон | Джо | techonthenet.com |
5000 | Смит | Джейн | digminecraft.com |
6000 | Фергюсон | Саманта | видов деятельности.com |
7000 | Рейнольдс | Аллен | checkyourmath.com |
8000 | Андерсон | Пейдж | НЕТ |
9000 | Джонсон | Дерек | techonthenet.com |
И таблица заказов со следующими данными:
идентификатор заказа | customer_id | дата заказа |
---|---|---|
1 | 7000 | 18.04.2016 |
2 | 5000 | 18.04.2016 |
3 | 8000 | 2016/04/19 |
4 | 4000 | 2016/04/20 |
5 | НЕТ | 01.05.2016 |
Введите следующий оператор SQL:
Попробуй это
ВЫБРАТЬ клиентов.customer_id, orders.order_id, orders.order_date ОТ клиентов LEFT OUTER JOIN заказы ВКЛ. Customers.customer_id = orders.customer_id ЗАКАЗАТЬ ПО customers.customer_id;
Будет выбрано 6 записей. Вот результаты, которые вы должны увидеть:
customer_id | order_id | дата заказа |
---|---|---|
4000 | 4 | 2016/04/20 |
5000 | 2 | 18.04.2016 |
6000 | НЕТ | НЕТ |
7000 | 1 | 18.04.2016 |
8000 | 3 | 2016/04/19 |
9000 | НЕТ | НЕТ |
В этом примере LEFT OUTER JOIN будут возвращены все строки из таблицы customers и только те строки из таблицы orders , в которых объединенные поля равны.
Если значение customer_id в таблице customers не существует в таблице orders , все поля в таблице orders будут отображаться как NULL в наборе результатов. Как видите, строки, в которых customer_id равно 6000 и 9000, будут включены в ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, но поля order_id и order_date будут отображать NULL.
SQL ПРАВО ВНЕШНЕЕ СОЕДИНЕНИЕ
Другой тип соединения называется SQL RIGHT OUTER JOIN.Этот тип соединения возвращает все строки из ПРАВОЙ таблицы, указанной в условии ON, а — только тех строк из другой таблицы, в которых объединенные поля равны (условие соединения выполнено).
Синтаксис
Синтаксис RIGHT OUTER JOIN в SQL:
ВЫБРАТЬ столбцы ИЗ table1 RIGHT [OUTER] JOIN table2 НА table1.column = table2.column;
В некоторых базах данных ключевое слово OUTER опускается и записывается просто как RIGHT JOIN.
Визуальная иллюстрация
На этой визуальной диаграмме SQL RIGHT OUTER JOIN возвращает заштрихованную область:
SQL RIGHT OUTER JOIN вернет все записи из table2 и только те записи из table1 , которые пересекаются с table2 .
Пример
Теперь давайте рассмотрим пример, показывающий, как использовать RIGHT OUTER JOIN в операторе SELECT.
Используя ту же таблицу клиентов , что и в предыдущем примере:
customer_id | фамилия | имя | любимый_сайт |
---|---|---|---|
4000 | Джексон | Джо | techonthenet.com |
5000 | Смит | Джейн | digminecraft.com |
6000 | Фергюсон | Саманта | bigactivities.com |
7000 | Рейнольдс | Аллен | checkyourmath.com |
8000 | Андерсон | Пейдж | НЕТ |
9000 | Джонсон | Дерек | techonthenet.com |
И таблица заказов со следующими данными:
идентификатор заказа | customer_id | дата заказа |
---|---|---|
1 | 7000 | 18.04.2016 |
2 | 5000 | 18.04.2016 |
3 | 8000 | 2016/04/19 |
4 | 4000 | 2016/04/20 |
5 | НЕТ | 01.05.2016 |
Введите следующий оператор SQL:
Попробуй это
ВЫБРАТЬ клиентов.customer_id, orders.order_id, orders.order_date ОТ клиентов RIGHT OUTER JOIN заказы ВКЛ. Customers.customer_id = orders.customer_id ЗАКАЗАТЬ ПО customers.customer_id;
Будет выбрано 5 записей. Вот результаты, которые вы должны увидеть:
customer_id | order_id | дата заказа |
---|---|---|
НУЛЬ | 5 | 01.05.2016 |
4000 | 4 | 2016/04/20 |
5000 | 2 | 18.04.2016 |
7000 | 1 | 18.04.2016 |
8000 | 3 | 2016/04/19 |
Этот пример RIGHT OUTER JOIN вернет все строки из таблицы orders и только те строки из таблицы customers , в которых объединенные поля равны.
Если значение customer_id в таблице orders не существует в таблице customers , все поля в таблице customers будут отображаться как NULL в наборе результатов. Как видите, строка, в которой order_id равно 5, будет включена в ПРАВИЛЬНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, но в поле customer_id отображается NULL.
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ SQL
Другой тип соединения называется SQL FULL OUTER JOIN. Этот тип соединения возвращает все строки из таблицы LEFT и RIGHT со значениями NULL в местах, где условие соединения не выполняется.
Синтаксис
Синтаксис SQL FULL OUTER JOIN :
ВЫБРАТЬ столбцы ИЗ table1 FULL [OUTER] JOIN table2 НА table1.column = table2.column;
В некоторых базах данных ключевое слово OUTER опускается и записывается просто как FULL JOIN.
Визуальная иллюстрация
На этой визуальной диаграмме SQL FULL OUTER JOIN возвращает заштрихованную область:
SQL FULL OUTER JOIN вернет все записи из table1 и table2 .
Пример
Давайте рассмотрим пример, который показывает, как использовать FULL OUTER JOIN в операторе SELECT.
Используя ту же таблицу клиентов , что и в предыдущем примере:
customer_id | фамилия | имя | любимый_сайт |
---|---|---|---|
4000 | Джексон | Джо | techonthenet.com |
5000 | Смит | Джейн | digminecraft.com |
6000 | Фергюсон | Саманта | bigactivities.com |
7000 | Рейнольдс | Аллен | checkyourmath.com |
8000 | Андерсон | Пейдж | НЕТ |
9000 | Джонсон | Дерек | techonthenet.com |
И таблица заказов со следующими данными:
идентификатор заказа | customer_id | дата заказа |
---|---|---|
1 | 7000 | 18.04.2016 |
2 | 5000 | 18.04.2016 |
3 | 8000 | 2016/04/19 |
4 | 4000 | 2016/04/20 |
5 | НЕТ | 01.05.2016 |
Введите следующий оператор SQL:
Попробуй это
ВЫБРАТЬ клиентов.customer_id, orders.order_id, orders.order_date ОТ клиентов ЗАКАЗЫ FULL OUTER JOIN ВКЛ. Customers.customer_id = orders.customer_id ЗАКАЗАТЬ ПО customers.customer_id;
Будет выбрано 7 записей. Вот результаты, которые вы должны увидеть:
customer_id | order_id | дата заказа |
---|---|---|
НУЛЬ | 5 | 01.05.2016 |
4000 | 4 | 2016/04/20 |
5000 | 2 | 18.04.2016 |
6000 | НЕТ | НЕТ |
7000 | 1 | 18.04.2016 |
8000 | 3 | 2016/04/19 |
9000 | НЕТ | НЕТ |
Этот пример FULL OUTER JOIN возвратит все строки из таблицы orders и все строки из таблицы customers .Если условие соединения не выполняется, значение NULL будет расширено до этих полей в наборе результатов. Это означает, что если значение customer_id в таблице customers не существует в таблице orders , все поля в таблице orders будут отображаться как NULL в наборе результатов. Кроме того, если значение customer_id в таблице orders не существует в таблице customers , все поля в таблице customers будут отображаться как NULL в наборе результатов.
Как видите, строки, где customer_id равно 6000 и 9000, будут включены, но поля order_id и order_date для этих записей содержат значение NULL. Строка, в которой order_id равно 5, также будет включена, но поле customer_id для этой записи имеет значение NULL.
.
SQL JOIN и оператор WHERE
Переполнение стека
- Около
Продукты
- Для команд
Переполнение стека
Общественные вопросы и ответыПереполнение стека для команд
Где разработчики и технологи делятся частными знаниями с коллегамиВакансии
Программирование и связанные с ним технические возможности карьерного ростаТалант
Нанимайте технических специалистов и создавайте свой бренд работодателяРеклама
Обратитесь к разработчикам и технологам со всего мира- О компании
.
SQL FULL OUTER JOIN ключевое слово
Ключевое слово FULL OUTER JOIN SQL
Ключевое слово FULL OUTER JOIN возвращает все записи, если в
левая (таблица1) или правая (таблица2) запись таблицы.
Примечание: FULL OUTER JOIN потенциально может возвращать очень большие
результирующие наборы!
Совет: FULL OUTER JOIN и FULL JOIN — это одно и то же.
Синтаксис FULL OUTER JOIN
ВЫБЕРИТЕ имя_столбца
ИЗ стол1
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ table2
ON table1.имя_столбца = table2.имя_столбца
ГДЕ состояние ;
Демо-база данных
В этом руководстве мы будем использовать хорошо известный образец базы данных Northwind.
Ниже представлен выбор из таблицы «Клиенты»:
Идентификатор клиента | Имя клиента | ContactName | Адрес | Город | Почтовый индекс | Страна |
---|---|---|---|---|---|---|
1 | Альфредс Футтеркисте | Мария Андерс | Obere Str.57 | Берлин | 12209 | Германия |
2 | Ana Trujillo Emparedados y helados | Ана Трухильо | Avda. de la Constitución 2222 | México D.F. | 05021 | Мексика |
3 | Антонио Морено Такерия | Антонио Морено | Матадерос 2312 | Мексика Д.F. | 05023 | Мексика |
И выбор из таблицы «Заказы»:
Код заказа | Идентификатор клиента | ID сотрудника | Дата заказа | ShipperID |
---|---|---|---|---|
10308 | 2 | 7 | 1996-09-18 | 3 |
10309 | 37 | 3 | 1996-09-19 | 1 |
10310 | 77 | 8 | 1996-09-20 | 2 |
Пример ПОЛНОГО ВНЕШНЕГО СОЕДИНЕНИЯ SQL
Следующий оператор SQL выбирает всех клиентов и все заказы:
ВЫБЕРИТЕ клиентов.CustomerName, Orders.OrderID
ОТ Заказчиков
Заказы FULL OUTER JOIN
ON Customers.CustomerID = Orders.CustomerID
ЗАКАЗАТЬ ПО Customers.CustomerName;
Выбор из набора результатов может выглядеть так:
Имя клиента | Код заказа |
---|---|
Альфредс Футтеркисте | Нулевой |
Ana Trujillo Emparedados y helados | 10308 |
Антонио Морено Такерия | 10365 |
Примечание: Ключевое слово FULL OUTER JOIN возвращает все совпадения
записи из обеих таблиц независимо от того, совпадает ли другая таблица или нет.Так что если
в «Заказчиках» есть строки, не совпадающие с «Заказами», или если есть
строки в «Заказы», которые не совпадают в «Клиенты», эти строки будут
также перечислены.
.