Full join mysql: FULL JOIN and MySQL | Interactive tutorial on SQL - Блог сумасшедшего сисадмина

Содержание

MySQL | Outer Join

Последнее обновление: 22.05.2018

В предыдущей теме рассматривля Inner Join или внутреннее соединение таблиц. Но также в MySQL мы можем использовать и так называемое внешнее соединение или Outer Join. В отличие от Inner Join внешнее соединение возвращает все строки одной или двух таблиц, которые участвуют в соединении.

Outer Join имеет следующий формальный синтаксис:


SELECT столбцы
FROM таблица1
	{LEFT|RIGHT} [OUTER] JOIN таблица2 ON условие1
	[{LEFT|RIGHT} [OUTER] JOIN таблица3 ON условие2]...

Перед оператором JOIN указывается одно из ключевых слов LEFT или RIGHT, которые определяют тип соединения:

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

Например, соединим таблицы Orders и Customers:


SELECT FirstName, CreatedAt, ProductCount, Price, ProductId 
FROM Orders LEFT JOIN Customers 
ON Orders.  CustomerId = Customers.Id

Таблица Orders является первой или левой таблицей, а таблица Customers — правой таблицей. Поэтому, так как здесь используется выборка по левой таблице, то вначале будут выбираться все строки из Orders, а затем к ним по условию Orders.CustomerId = Customers.Id будут добавляться связанные строки из Customers.

По вышеприведенному результату может показаться, что левостороннее соединение аналогично INNER Join, но это не так. Inner Join объединяет строки из дух таблиц при соответствии условию. Если одна из таблиц содержит строки, которые не соответствуют этому условию, то данные строки не включаются в выходную выборку. Left Join выбирает все строки первой таблицы и затем присоединяет к ним строки правой таблицы. К примеру, возьмем таблицу Customers и добавим к покупателям информацию об их заказах:


#INNER JOIN
SELECT FirstName, CreatedAt, ProductCount, Price 
FROM Customers JOIN Orders 
ON Orders. CustomerId = Customers.Id;

#LEFT JOIN
SELECT FirstName, CreatedAt, ProductCount, Price 
FROM Customers LEFT JOIN Orders 
ON Orders.CustomerId = Customers.Id;

В случае с LEFT JOIN MySQL выбирает сначала всех покупателей из таблицы Customers, затем сопоставляет их с заказами из таблицы Orders через условие

Orders.CustomerId = Customers.Id. Однако не у всех покупателей есть заказы. В этом случае покупателю для соответствующих столбцов устанавливаются значения NULL.

Изменим в примере выше тип соединения для OUTER JOIN с левостороннего на правостороннее:


SELECT FirstName, CreatedAt, ProductCount, Price 
FROM Customers RIGHT JOIN Orders 
ON Orders.CustomerId = Customers.Id;

Теперь будут выбираться все строки из Orders (из правой таблицы), а к ним уже будет присоединяться связанные по условию строки из таблицы Customers:

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


SELECT Customers. FirstName, Orders.CreatedAt, 
       Products.ProductName, Products.Manufacturer
FROM Orders 
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
LEFT JOIN Products ON Orders.ProductId = Products.Id;

И также можно применять более комплексные условия с фильтрацией и сортировкой. Например, выберем все заказы с информацией о клиентах и товарах по тем товарам, у которых цена больше 45000, и отсортируем по дате заказа:


SELECT Customers.FirstName, Orders.CreatedAt, 
       Products.ProductName, Products.Manufacturer
FROM Orders 
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
LEFT JOIN Products ON Orders.ProductId = Products.Id
WHERE Products.Price > 45000
ORDER BY Orders.CreatedAt;

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


SELECT FirstName FROM Customers
LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE Orders. CustomerId IS NULL;

Также можно комбинировать Inner Join и Outer Join:


SELECT Customers.FirstName, Orders.CreatedAt, 
       Products.ProductName, Products.Manufacturer
FROM Orders 
JOIN Products ON Orders.ProductId = Products.Id AND Products.Price > 45000
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
ORDER BY Orders.CreatedAt;

Вначале по условию к таблице Orders через Inner Join присоединяется связанная информация из Products, затем через Outer Join добавляется информация из таблицы Customers.

НазадСодержаниеВперед

База Данных MySQL FULL OUTER JOIN

HTML5CSS.ru

ЛУЧШИЙ САЙТ ДЛЯ РАЗРАБОТЧИКОВ

❮ Назад Дальше ❯

Ключевое слово полного внешнего соединения SQL

Полное внешнее ключевое слово Join возвращает все записи при совпадении в записях таблицы Left (Table1) или right (Table2).

Примечание: Полное внешнее соединение потенциально может возвращать очень большие результирующие наборы!

FULL OUTER JOIN Синтаксис

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1. column_name = table2.column_name;

Демонстрационная база данных

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

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

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

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

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

Пример полного внешнего соединения SQL

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

SELECT Customers. CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Выбор из результирующего набора может выглядеть так:

CustomerName	OrderID
Alfreds Futterkiste
Ana Trujillo Emparedados y helados	10308
Antonio Moreno Taquería	10365
	10382
	10351

Примечание: Полное внешнее ключевое слово Join возвращает все строки из левой таблицы (Customers) и все строки из правой таблицы (Orders). Если есть строки в «Customers», которые не имеют совпадений в «заказы», или если есть строки в «заказы», которые не имеют совпадений в «Customers», эти строки будут перечислены также.

❮ Назад Дальше ❯

PHP\CSS\JS\HMTL Editor

Правила и Условия Политика конфиденциальности О нас Контакты

sql — Как я могу выполнить ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ в MySQL?

Ответ, который дал Пабло Санта-Крус, правильный; однако, если кто-то наткнулся на эту страницу и хочет получить дополнительные разъяснения, вот подробная разбивка.

Примеры таблиц

Предположим, у нас есть следующие таблицы:

 -- t1
идентификатор имя
1 Тим
2 марта
-- т2
идентификатор имя
1 Тим
3 Катарина

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

Внутреннее соединение, например:

 SELECT *
ОТ `t1`
ВНУТРЕННЕЕ СОЕДИНЕНИЕ `t2` ON `t1`.`id` = `t2`.`id`;

Мы получим только те записи, которые присутствуют в обеих таблицах, например:

 1 Tim 1 Tim

Внутренние соединения не имеют направления (например, влево или вправо), поскольку они явно являются двунаправленными — нам требуется совпадение с обеих сторон.

Внешние соединения

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

ЛЕВОЕ СОЕДИНЕНИЕ и ПРАВОЕ СОЕДИНЕНИЕ являются сокращением для ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ и ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ ; Я буду использовать их полные имена ниже, чтобы укрепить концепцию внешних и внутренних соединений.

Левое внешнее соединение

Левое внешнее соединение, например:

 SELECT *
ОТ `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

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

 1 Тим 1 Тим
2 Марта NULL NULL

Правое внешнее соединение

Правое внешнее соединение, например:

 SELECT *
ОТ `t1`
ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ `t2` ON `t1`.`id` = `t2`.`id`;

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

 1 Tim 1 Tim
NULL NULL 3 Катарина

Полное внешнее соединение

Полное внешнее соединение даст нам все записи из обеих таблиц, независимо от того, совпадают они или нет в другой таблице, с NULL на обеих сторонах, где совпадения нет. Результат будет выглядеть так:

 1 Тим 1 Тим
2 Марта NULL NULL
NULL NULL 3 Катарина

Однако, как указал Пабло Санта-Крус, MySQL не поддерживает это. Мы можем эмулировать это, выполнив UNION левого соединения и правого соединения, например:

 SELECT *
ОТ `t1`
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ `t2` ON `t1`.`id` = `t2`.`id`
СОЮЗ
ВЫБРАТЬ *
ОТ `t1`
ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ `t2` ON `t1`.`id` = `t2`.`id`;

Вы можете думать о UNION как о значении «запустить оба этих запроса, затем сложить результаты друг над другом»; некоторые строки будут получены из первого запроса, а некоторые из второго.

Следует отметить, что UNION в MySQL устранит точные дубликаты: Тим появится в обоих запросах здесь, но в результате UNION он указан только один раз. Мой коллега-гуру базы данных считает, что на такое поведение нельзя полагаться. Поэтому, чтобы быть более точным, мы могли бы добавить предложение WHERE ко второму запросу:

 SELECT *
ОТ `t1`
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ `t2` ON `t1`. `id` = `t2`.`id`
СОЮЗ
ВЫБРАТЬ *
ОТ `t1`
ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ `t2` ON `t1`.`id` = `t2`.`id`
ГДЕ `t1`.`id` имеет значение NULL;

С другой стороны, если вы хотите, чтобы по какой-то причине видел дубликаты, вы можете использовать UNION ALL .

sql — Почему MySQL сообщает о синтаксической ошибке при ПОЛНОМ ВНЕШНЕМ СОЕДИНЕНИИ?

спросил 12 лет, 8 месяцев назад

Изменено 1 год, 4 месяца назад

Просмотрено 82к раз

 ВЫБЕРИТЕ авиакомпания, airports.icao_code, континент, страна, провинция, город, веб-сайт
ОТ авиакомпаний
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ airports ON Airlines.iaco_code = airports.iaco_code
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ городов НА airports.city_id = city.city_id
ПОЛНОЕ ВНЕШНЕЕ ОБЪЕДИНЕНИЕ провинций ON citys. province_id = провинцияs.province_id
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ стран ON citys.country_id = country.country_id
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Там написано, что

У вас ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, для правильного синтаксиса для использования рядом с «внешним соединением аэропортов на авиалиниях.iaco_code = airports.iaco_code». полное внешнее соединение в строке 4

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

mysql
sql
присоединиться
mysql-ошибка-1064

В MySQL нет FULL OUTER JOIN . См. 7.2.12. Упрощение внешнего соединения и 12.2.8.1. ПРИСОЕДИНЯЙТЕ Синтаксис:

Вы можете эмулировать FULL OUTER JOIN , используя UNION (начиная с MySQL 4. 0.0):
с двумя таблицами t1, t2:
 ВЫБРАТЬ * ИЗ t1
ЛЕВОЕ СОЕДИНЕНИЕ t2 ON t1.id = t2.id
СОЮЗ
ВЫБЕРИТЕ * ИЗ t1
ПРАВОЕ СОЕДИНЕНИЕ t2 НА t1.id = t2.id
 
с тремя таблицами t1, t2, t3:
 ВЫБРАТЬ * ИЗ t1
ЛЕВОЕ СОЕДИНЕНИЕ t2 ON t1.id = t2.id
ЛЕВОЕ СОЕДИНЕНИЕ t3 ON t2.id = t3.id
СОЮЗ
ВЫБЕРИТЕ * ИЗ t1
ПРАВОЕ СОЕДИНЕНИЕ t2 НА t1.id = t2.id
ЛЕВОЕ СОЕДИНЕНИЕ t3 ON t2.id = t3.id
СОЮЗ
ВЫБЕРИТЕ * ИЗ t1
ПРАВОЕ СОЕДИНЕНИЕ t2 НА t1.id = t2.id
ПРАВОЕ СОЕДИНЕНИЕ t3 НА t2.id = t3.id
 

Ответ Клетуса не совсем правильный. UNION удалит повторяющиеся записи, которые будут включены в FULL OUTER JOIN . Если вам нужны дубликаты, используйте что-то вроде:

 ВЫБРАТЬ * ИЗ t1
ЛЕВОЕ СОЕДИНЕНИЕ t2 ON t1.id = t2.id
ЛЕВОЕ СОЕДИНЕНИЕ t3 ON t2.id = t3.id
ЛЕВОЕ СОЕДИНЕНИЕ t4 ON t3.id = t4.id
СОЮЗ ВСЕХ
ВЫБЕРИТЕ * ИЗ t1
ПРАВОЕ СОЕДИНЕНИЕ t2 НА t1.id = t2. id
ЛЕВОЕ СОЕДИНЕНИЕ t3 ON t2.id = t3.id
ЛЕВОЕ СОЕДИНЕНИЕ t4 ON t3.id = t4.id
ГДЕ t1.id имеет значение NULL
СОЮЗ ВСЕХ
ВЫБЕРИТЕ * ИЗ t1
ПРАВОЕ СОЕДИНЕНИЕ t2 НА t1.id = t2.id
ПРАВОЕ СОЕДИНЕНИЕ t3 НА t2.id = t3.id
ЛЕВОЕ СОЕДИНЕНИЕ t4 ON t3.id = t4.id
ГДЕ t2.id имеет значение NULL
СОЮЗ ВСЕХ
ВЫБЕРИТЕ * ИЗ t1
ПРАВОЕ СОЕДИНЕНИЕ t2 НА t1.id = t2.id
ПРАВОЕ СОЕДИНЕНИЕ t3 НА t2.id = t3.id
ПРАВОЕ СОЕДИНЕНИЕ t4 НА t3.id = t4.id
ГДЕ t3.id имеет значение NULL;

Я только что сделал для этого трюк:

 (выберите 1 из DUAL) d
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ t1 ON t1.id = t2.id
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ t2 ON t1.id = t2.id

дело в том, что запрос из двойного делает точку фиксации, и mysql может внешне присоединиться к 2 другим таблицам к этому

Просто дополню случай, когда нужно FULL OUTER JOIN три таблицы t1, t2, t3. Вы можете сделать t1, t2, t3, по очереди, левое соединение двух оставшихся таблиц, а затем объединение.

Full join mysql: FULL JOIN and MySQL | Interactive tutorial on SQL