FULL OUTER JOIN в SQL: объяснение на примере
Перевод статьи «SQL Outer Join Tutorial – With Example Syntax».
Photo by Ziad Al Halabi on UnsplashВ SQL операторы JOIN используются для объединения строк двух или более таблиц. Объединение происходит по столбцу, который есть в каждой из таблиц.
JOIN-ы делятся на внутренние и внешние — INNER JOIN и OUTER JOIN. (Примечание редакции Techrocks: внутренние мы уже разбирали в другой статье — «INNER JOIN простыми словами»).
Внешнее объединение — OUTER JOIN — бывает трех видов: LEFT JOIN, RIGHT JOIN и FULL OUTER JOIN (левое, правое и полное). В этой статье мы разберем на примере FULL OUTER JOIN (полное внешнее объединение).
Что из себя представляет FULL OUTER JOIN?
FULL OUTER JOIN (или просто OUTER JOIN) используется, чтобы вернуть все записи, имеющие значения в левой или правой таблице.
Допустим, у нас есть таблица с клиентами и таблица с заказами. В каждой таблице есть столбец с идентификаторами клиентов. FULL OUTER JOIN этих таблиц вернет всех клиентов, даже тех, кто не делал заказов, а также все заказы. Строки с именами клиентов, сделавших заказы, будут скомбинированы со строками их заказов. Объединение произойдет на основе идентификатора клиента.
FULL OUTER JOIN может вернуть очень большое количество данных. Поэтому, прежде чем использовать его, подумайте, нет ли другого способа выполнить задачу.
Пример использования FULL OUTER JOIN
Представьте, что вы преподаете американскую литературу. У вас есть десять учеников. Каждый из них должен прочесть одну из рекомендованных книг (все ученики будут читать разные книги). Некоторые ребята уже выбрали себе книгу, а другие пока нет.
Вы создали таблицу со списком учеников, где для каждого ученика проставлен номер (т. е. ID).
student_id | name |
---|---|
1 | Джон |
2 | Саид |
3 | Алиса |
4 | Ной |
5 | Элеанор |
6 | Акико |
7 | Отто |
8 | Джамал |
9 | Кьяра |
10 | Клементина |
В другой таблице у вас содержится список книг. Для каждой книги указано ее название, автор, номер ISBN и ID ученика, который выбрал эту книгу для чтения (если, конечно, книгу кто-то уже выбрал).
isbn | student_id | title | author |
---|---|---|---|
1514649748 | 1 | Моби Дик | Герман Мелвилл |
0060935464 | 4 | Убить пересмешника | Харпер Ли |
9780060837563 | 9 | Сын Америки | Ричард Райт |
9780316769174 | NULL | Над пропастью во ржи | Джером Сэлинджер |
0143135694 | 7 | Цвет пурпурный | Элис Уокер |
1451673264 | 3 | 451 градус по Фаренгейту | Рэй Брэдбери |
9780743273565 | NULL | Великий Гэтсби | Фрэнсис Скотт Фицджеральд |
0807083690 | NULL | Родня | Октавия Батлер |
1950435091 | NULL | Маленькие женщины | Луиза Мэй Олкотт |
0140177396 | 2 | О мышах и людях | Джон Стейнбек |
Как делается OUTER JOIN в SQL
Чтобы сделать внешнее объединение записей наших таблиц, нужно выполнить следующий запрос:
SELECT students.name, books.title FROM students FULL OUTER JOIN books ON students.student_id=books.student_id;
В этом примере мы выбираем имена учеников из таблицы students и названия книг из таблицы books. Записи объединяются по столбцу student_id, который присутствует в обеих таблицах.
Результат будет выглядеть следующим образом:
name | title |
---|---|
Джон | Моби Дик |
Ной | |
Кьяра | Сын Америки |
NULL | Над пропастью во ржи |
Отто | Цвет пурпурный |
Алиса | 451 градус по Фаренгейту |
NULL | Великий Гэтсби |
NULL | Родня |
NULL | Маленькие женщины |
Саид | О мышах и людях |
Клементина | NULL |
Джамал | NULL |
Акико | NULL |
Элеанор | NULL |
Благодаря FULL OUTER JOIN мы можем увидеть список всех учеников, в том числе тех, кто еще не выбрал книгу. Также мы видим все книги, включая те, которые еще никто не выбрал.
Просматривая результат выполнения запроса, вы можете понять, кто из учеников еще не выбрал себе книгу, и какие книги остались для выбора.
Заключение
Использование FULL OUTER JOIN позволяет получить полное представление о данных в нескольких связанных таблицах. Но имейте в виду, что если у вас большой объем данных, этот запрос может вернуть просто огромное количество информации, так что пользуйтесь им с умом!
От редакции Techrocks: если хотите почитать об операторе JOIN более подробно, у нас есть хорошая статья — «SQL JOIN: руководство по объединению таблиц».
Руководство по SQL. RIGHT JOIN.
Запрос RIGHT JOIN возвращает все записи из правой таблицы, даже если у них нет совпадений в левой. Это означает, что если у нас нет совпадений в левой таблице, то мы получим все записи из правой, а колонки из левой таблицы будут иметь значения NULL.
SQL запрос с использованием RIGHT JOIN имеет следующий вид:
SELECT таблица1.
колонка1, таблица2.колонка2...
FROM таблица1
RIGHT JOIN таблицы2
ON таблицы1.общее_поле = таблица2.общее_поле;
Пример:
Предположим, что у нас есть две таблицы:
developers:
+----+-------------------+------------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+------------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | JavaScript | 3 | 2500 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | | 6 | Kolya Nikolaev | Javascript | 5 | 3400 | | 7 | Ivan Ivanov | C# | 1 | 900 | | 8 | Ludmila Geiko | UI/UX | 2 | 1800 | +----+-------------------+------------+------------+--------+
tasks:
+---------+-------------+------------------+------------+--------------+ | TASK_ID | TASK_NAME | DESCRIPTION | DEADLINE | DEVELOPER_ID | +---------+-------------+------------------+------------+--------------+ | 1 | Bug#123 | Fix company list | 2016-06-03 | 1 | | 2 | Bug#321 | Fix registration | 2016-06-06 | 2 | | 3 | Feature#777 | Latest actions | 2016-06-25 | 3 | | 4 | Feature#3 | Super feature | 2016-06-15 | 9 | +---------+-------------+------------------+------------+--------------+
Попробуем выполнить следующий запрос:
mysql> SELECT ID, NAME, TASK_NAME, DEADLINE FROM developers RIGHT JOIN tasks ON developers.ID = tasks.DEVELOPER_ID;
В результате мы получим следующую таблицу:
+------+-------------------+-------------+------------+ | ID | NAME | TASK_NAME | DEADLINE | +------+-------------------+-------------+------------+ | 1 | Eugene Suleimanov | Bug#123 | 2016-06-03 | | 2 | Peter Romanenko | Bug#321 | 2016-06-06 | | 3 | Andrei Komarov | Feature#777 | 2016-06-25 | | NULL | NULL | Feature#3 | 2016-06-15 | +------+-------------------+-------------+------------+
Как видите, мы получили все задачи из таблицы tasks, а поля ID и NAME записи, у которой нет совпадений в таблице developers имеют значение NULL.
На этом мы заканчиваем изучение RIGHT JOIN.
Ключевое слово SQL RIGHT JOIN
❮ Предыдущий Далее ❯
Ключевое слово SQL RIGHT JOIN
Ключевое слово RIGHT JOIN
возвращает все записи из правой таблицы (таблица2), а
совпадающие записи из левой таблицы (таблица1).
Правое соединение Синтаксис
Выберите Column_Name (S)
Из Таблица 1
Правое соединение Таблица 2
на Таблица 1.column_Name = table2.column_name ;
Примечание: В некоторых базах данных RIGHT JOIN
называется ПРАВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
.
Демонстрационная база данных
В этом руководстве мы будем использовать известную учебную базу данных Northwind.
Ниже выбор из таблицы «Заказы»:
OrderID | идентификатор клиента | ID сотрудника | Дата заказа | ID отправителя |
---|---|---|---|---|
10308 | 2 | 7 | 18 сентября 1996 г. | 3 |
10309 | 37 | 3 | 1996-09-19 | 1 |
10310 | 77 | 8 | 1996-09-20 | 2 |
И выбор из таблицы «Сотрудники»:
EmployeeID | Фамилия | Имя | Дата рождения | Фото |
---|---|---|---|---|
1 | Даволио | Нэнси | 08.![]() | EmpID1.pic |
2 | Фуллер | Андрей | 19.02.1952 | EmpID2.pic |
3 | Леверлинг | Джанет | 30.08.1963 | EmpID3.pic |
SQL RIGHT JOIN Пример
Следующая инструкция SQL вернет всех сотрудников и все заказы, которые они мог бы разместить:
Пример
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Примечание: Ключевое слово RIGHT JOIN
возвращает все записи из
правая таблица (Сотрудники), даже если в левой таблице нет совпадений
(Заказы).
Научитесь фильтровать данные в SQL как аналитик данных
Попробуйте практические занятия с пошаговыми инструкциями от эксперта. Попробуйте управляемый проект, созданный в сотрудничестве с Coursera, прямо сейчас!
Проверьте себя с помощью упражнений
Упражнение:
Выберите правильное предложение JOIN
, чтобы выбрать все записи из таблицы Customers
Orders
.ВЫБИРАТЬ * ОТ заказов ON Orders.CustomerID=
Customers.CustomerID;
Начать упражнение
❮ Предыдущий Далее ❯
ВЫБОР ЦВЕТА
Лучшие учебники
Учебник по HTMLУчебник по CSS
Учебник по JavaScript
Учебник How To
Учебник по SQL
Учебник по Python
Учебник по W3.CSS
Учебник по Bootstrap
Учебник по PHP
Учебник по Java
Учебник по C++
Учебник по jQuery
9000 3
Основные ссылки
HTML ReferenceCSS Reference
JavaScript Reference
SQL Reference
Python Reference
W3.

Bootstrap Reference
PHP Reference
HTML Colors
Java Reference
Angular Reference
jQuery Reference
901 98 лучших примеров Примеры HTML
Примеры CSS
Примеры JavaScript
Примеры SQL
Примеры Python
Примеры W3.CSS
Примеры Bootstrap
Примеры PHP
Примеры Java
Примеры XML
Примеры jQuery
FORUM | О
W3Schools оптимизирован для обучения и обучения. Примеры могут быть упрощены для улучшения чтения и обучения. Учебники, ссылки и примеры постоянно пересматриваются, чтобы избежать ошибок, но мы не можем гарантировать полную правильность всего содержания. Используя W3Schools, вы соглашаетесь прочитать и принять наши условия использования, куки-файлы и политика конфиденциальности. Авторское право 1999-2023 по данным Refsnes. Все права защищены.
W3Schools работает на основе W3.CSS.
SQL RIGHT JOIN (с примерами)
В этом руководстве вы узнаете об операторе SQL RIGHT JOIN с помощью примеров.
Оператор SQL RIGHT JOIN
объединяет две таблицы на основе общего столбца. Он выбирает записи с совпадающими значениями в этих столбцах и остальных строках из правой таблицы.
Пример
-- объединение таблиц "Клиенты" и "Заказы" -- на основе их общих столбцов customer_id -- Клиенты - это левый стол -- Заказы - правильная таблица ВЫБЕРИТЕ Customers.customer_id, Customers.first_name, Orders.item ОТ клиентов ПРАВОЕ СОЕДИНЕНИЕ Заказы ON Customers.customer_id = Orders.customer_id;
Здесь кодовое право объединяет таблицы Customers
и Orders
на основе customer_id , общего для обеих таблиц. Набор результатов содержит
- столбцов customer_id и first_name из таблицы Customers .
- столбец item из таблицы
Orders
(включая те, для которых значение customer_id отсутствует в таблицеCustomers
)
RIGHT JOIN SYNTAX
Синтаксис инструкции SQL RIGHT JOIN
:
SELECT columns_from_both_tables ИЗ таблицы1 ПРАВОЕ СОЕДИНЕНИЕ таблица2 ON table1.column1 = table2.column2
Здесь
- table1 — это левая таблица, которую нужно объединить
- table2 правильная таблица для объединения
- столбец 1 и
столбец 2
являются связанными столбцами в двух таблицах
Пример: SQL RIGHT JOIN
-- объединить таблицы Customers и Orders -- на основе идентификатора customer_id для клиентов и клиента для заказов -- Клиенты - это левый стол -- Заказы - правильная таблица ВЫБЕРИТЕ Customers.customer_id, Customers.first_name, Orders.amount ОТ клиентов ПРАВОЕ СОЕДИНЕНИЕ Заказы ON Customers.customer_id = Orders.customer;
Вот как работает этот код:
Пример: SQL RIGHT JOIN Здесь команда SQL выбирает customer_id
и first_name
столбцы (из таблицы "Клиенты
«) и столбец "Сумма
» (из таблицы "Заказы
«).
И результирующий набор будет содержать те строки, в которых есть совпадение между customer_id
(из таблицы Customers
) и customer
(из таблицы Orders
), а также все остальные строки из Заказы
стол.
Примечание: RIGHT JOIN
не поддерживается нашим онлайн-компилятором SQL, поскольку он основан на SQLite. Однако вы можете получить те же результаты, используя LEFT JOIN и поменяв местами левую и правую таблицы.
-- левое соединение таблиц "Заказы" и "Клиенты" -- Заказы - это левый стол -- Клиенты - правильная таблица ВЫБЕРИТЕ Customers.customer_id, Customers.first_name, Orders.amount ОТ заказов ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ к клиентам ON Orders.customer = Customers.customer_id;
RIGHT JOIN с предложением WHERE
Оператор SQL RIGHT JOIN
может иметь необязательное предложение WHERE. Например,
ВЫБЕРИТЕ Customers.customer_id, Customers.first_name, Orders.amount ОТ клиентов ПРАВОЕ СОЕДИНЕНИЕ Заказы ON Customers.customer_id = Orders.customer ГДЕ Orders.amount >= 500;
Здесь команда SQL объединяет таблицы Customers
и Orders
и выбирает строки, в которых сумма на больше или равна 500 .