Операции соединения таблиц JOIN в SQL с примерами
Одной из самых ключевых операций в SQL является операция соединения таблиц (JOIN операции). Благодаря этому мы можем сопоставлять данные из одной таблицы с данными другой построчно, прописав правила сопоставления. В одном запросу мы можем производить несколько подобных операций. Для дальнейшего изучения работы с соединением таблиц создадим две таблицы, в первой таблице у нас будут храниться информация по покупателю (идентификатор покупателя, имя и фамилия), а во второй таблице информация по покупке (идентификатор покупки, идентификатор покупателя и купленный товар).
CREATE TABLE buyers
(
id_buyer INT PRIMARY KEY AUTO_INCREMENT,
firstname VARCHAR(15),
lastname VARCHAR(15)
);
CREATE TABLE sales
(
id_product INT PRIMARY KEY AUTO_INCREMENT,
id_buyer INT(4),
product VARCHAR(50)
);
INSERT INTO buyers(firstname, lastname) VALUES
('Carlos', 'Boolman'),
('Logan', 'Evans'),
('Laura', 'White'),
('Ethan', 'Walker'),
('Mary', 'Wilson');
INSERT INTO sales(id_buyer, product) VALUES
(5, 'Bread'),
(2, 'Eggs'),
(3, 'Meat'),
(5, 'Milk'),
(7, 'Juice');
SELECT * FROM buyers;
SELECT * FROM sales;
SELECT * FROM sales;
Внутреннее соединение INNER JOIN
Это самый распространенный тип соединения, который позволяет получить строки, которые полностью удовлетворяют условию соединения (ключу). Если в одной из таблиц будут встречаться значения ключа, которых нет во второй таблице, то такие строки не попадут в итоговый вывод данных. При использовании внутреннего соединения можно не писать INNER, система управления базой данный использует его по умолчанию при указании команды JOIN.
SELECT b.firstname, b.lastname, s.product FROM buyers AS b
JOIN sales AS s ON b.id=s.id_buyer;
Таким образом в вывод данных не попали строки с клиентами Carlos Boolman и Ethan Walker из таблицы, потому что их значений идентификатора (id_buyer) нет в соединяемой таблице sales. По этой же причине строка c товаром Juice так же не попала в итоговую таблицу. При этом у нас две строки с клиентом Mary Wilson, это из-за того что у нас в таблице sales две строки с ее идентификатором. Присвоенные таблицам алиасы обязательны к указанию у столбцов, которые встречаются как в первой, так и во второй таблице. В случае если столбцы уникальны, указание алиасов носит рекомендательный характер.JOIN sales AS s ON b.id=s.id_buyer;
Внешние соединения LEFT (RIGHT) OUTER JOIN
Есть задачи в соответствии с которыми вам необходимо вывести всю информацию из одной таблицы и соответствующие им строки из второй. Например, нам необходимо получить покупки по всем клиентам и вывести даже тех кто не совершил ни одной покупки (у таких клиентов в поле product будет указано NULL). В данной ситуации нам помогут внешние левые и правые соединения.
SELECT b.firstname, b.lastname, s.product FROM buyers AS b
LEFT OUTER JOIN sales AS s ON b.id_buyer=s.id_buyer;
Правое внешнее соединение работает по такому же принципу, только сохраняет все строки из правой (указанной в JOIN), а из левой берет только удовлетворяющие ключу значения. В указанном ниже примере запрос будет выводить все товары, даже те что еще не были куплены.
LEFT OUTER JOIN sales AS s ON b.id_buyer=s.id_buyer;
SELECT b.firstname, b.lastname, s.product FROM buyers AS b
RIGHT OUTER JOIN sales AS s ON b.id_buyer=s.id_buyer;
RIGHT OUTER JOIN sales AS s ON b.id_buyer=s.id_buyer;
Перекрестное соединение CROSS JOIN
Рассмотрим еще один вид соединения таблиц CROSS JOIN или перекрестное соединение таблиц. Благодаря данному соединению в выводе мы мы получаем сопоставление каждой строки одной таблицы с каждой строкой второй, т.е. мы получаем декартово произведение. Это соединение не так часто используется как указанные выше внутреннее и внешние соединения, но оно очень полезно при генерации данных. Рассмотрим это соединение на примере наших таблиц buyers и sales и выведем первые 10 строк из итогового набора данных.
SELECT firstname, lastname, product FROM buyers AS b
CROSS JOIN sales AS s
LIMIT 10;
CROSS JOIN sales AS s
LIMIT 10;