Outer Join LEFT|RIGHT - внешнее соединение таблиц
Внешнее соединение возвращает все строки одной или двух таблиц, которые участвуют в соединении. Если для строки таблицы не нашлось ни одной строки в другой таблице , то строка все равно добавляется в результат, а значения столбцов правой таблицы равны null
Outer Join
имеет следующий формальный синтаксис:
SELECT `столбцы_таблиц_для_вывода` FROM `основная_таблица`
LEFT|RIGHT OUTER JOIN вспомогательная_таблица_1 ON условие
LEFT|RIGHT OUTER JOIN вспомогательная_таблица_2 ON условие; -- возможно применение операторов
Перед оператором JOIN
указывается одно из ключевых слов LEFT
или RIGHT
,
которые определяют тип соединения:
LEFT
левое внешнее соединение. Работает какJOIN
, но если для строки таблицы, находящейся по левую сторону ключевого словаLEFT JOIN
, не нашлось ни одной строки в таблице, находящейся по правую сторонуLEFT JOIN
, то строка все равно добавляется в результат, а значения столбцов правой таблицы равныnull
.RIGHT
правое внешнее соединение. Работает какJOIN
, но если для строки таблицы, находящейся по правую сторону ключевого словаRIGHT JOIN
, не нашлось ни одной строки в таблице, находящейся по левую сторонуRIGHT JOIN
, то строка все равно добавляется в результат, а значения столбцов левой таблицы равныnull
.
Также перед оператором JOIN
может указываться ключевое слово OUTER
, но его применение
необязательно. Далее после JOIN
указывается присоединяемая таблица, а затем идет условие
соединения.
Главное отличие от внутреннего соединения в том, что строка из левой (для LEFT JOIN
) или из правой
таблицы (для RIGHT
JOIN
) попадет в результаты в любом случае. Давайте до конца определимся с тем, какая таблица левая, а какая
правая.
Левая таблица та, которая идет перед написанием ключевых слов LEFT
| RIGHT
, правая таблица
– после них.
Возьмем таблицу:
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE Products
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(30) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price DECIMAL NOT NULL
);
CREATE TABLE Customers
(
Id INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductId INT NOT NULL,
CustomerId INT NOT NULL,
CreatedAt DATE NOT NULL,
ProductCount INT DEFAULT 1,
Price DECIMAL NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products(Id) ON DELETE CASCADE,
FOREIGN KEY (CustomerId) REFERENCES Customers(Id) ON DELETE CASCADE
);
Здесь таблицы Products
и Customers
связаны с таблицей Orders
связью один ко
многим. Таблица Orders
в виде внешних ключей ProductId
и CustomerId
содержит
ссылки на столбцы Id
из соответственно таблиц Products
и Customers
. Также она
хранит количество купленного товара (ProductCount
) и и по какой цене он был куплен
(Price
). И кроме того, таблицы также хранит в виде столбца CreatedAt
дату покупки.
Заполним таблицу данными:
USE test;
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES ('iPhone X', 'Apple', 2, 76000),
('iPhone 8', 'Apple', 2, 51000),
('iPhone 7', 'Apple', 5, 42000),
('Galaxy S9', 'Samsung', 2, 56000),
('Galaxy S8', 'Samsung', 1, 46000),
('Honor 10', 'Huawei', 2, 26000),
('Nokia 8', 'HMD Global', 6, 38000);
INSERT INTO Customers(FirstName) VALUES ('Tom'), ('Bob'),('Sam');
INSERT INTO Orders (ProductId, CustomerId, CreatedAt, ProductCount, Price)
VALUES
(
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2018-05-21',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone X'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2018-05-23',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone X')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone X'),
(SELECT Id FROM Customers WHERE FirstName='Bob'),
'2018-05-21',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone X')
);
Например, соединим таблицы 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
.