http://www.skillz.ru/dev/php/article-Obyasnenie_SQL_obedinenii_JOIN_INNER_OUTER.html
http://sqlfiddle.com/#!2/659f69/1
Разберем пример. Имеем две таблицы: пользователи и отделы.
U) users D) departments
id name d_id id name
-- ---- ---- -- ----
1 Владимир 1 1 Сейлз
2 Антон 2 2 Поддержка
3 Александр 6 3 Финансы
4 Борис 2 4 Логистика
5 Юрий 4
SELECT u.id, u.name, d.name AS d_name
FROM users u
INNER JOIN departments d ON u.d_id = d.id
Запрос вернет объединенные данные, которые пересекаются по условию, указанному в INNER JOIN ON <..>.
В нашем случае условие <таблица_пользователей>.<идентификатор_отдела> должен совпадать с <таблица_отделов>.<идентификатор>
В результате отсутствуют:
- пользователь Александр (отдел 6 — не существует)
- отдел Финансы (нет пользователей)
id name d_name
-- -------- ---------
1 Владимир Сейлз
2 Антон Поддержка
4 Борис Поддержка
3 Юрий Логистика
рис. Inner join
Внутреннее объединение INNER JOIN (синоним JOIN, ключевое слово INNER можно опустить).
Выбираются только совпадающие данные из объединяемых таблиц. Чтобы получить данные, которые не подходят по условию, необходимо использовать
внешнее объединение — OUTER JOIN.
Такое объединение вернет данные из обеих таблиц совпадающими по одному из условий.
рис. Left join
Существует два типа внешнего объединения OUTER JOIN — LEFT OUTER JOIN и RIGHT OUTER JOIN.
Работают они одинаково, разница заключается в том что LEFT — указывает что «внешней» таблицей будет находящаяся слева (в нашем примере это таблица users).
Ключевое слово OUTER можно опустить. Запись LEFT JOIN идентична LEFT OUTER JOIN.
SELECT u.id, u.name, d.name AS d_name
FROM users u
LEFT OUTER JOIN departments d ON u.d_id = d.id
Получаем полный список пользователей и сопоставленные департаменты.
id name d_name
-- -------- ---------
1 Владимир Сейлз
2 Антон Поддержка
3 Александр NULL
4 Борис Поддержка
5 Юрий Логистика
Добавив условие
WHERE d.id IS NULL
в выборке останется только 3#Александр, так как у него не назначен департамент.
рис. Left outer join с фильтрацией по полю
RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей.
SELECT u.id, u.name, d.name AS d_name
FROM users u
RIGHT OUTER JOIN departments d ON u.d_id = d.id
id name d_name
-- -------- ---------
1 Владимир Сейлз
2 Антон Поддержка
4 Борис Поддержка
NULL NULL Финансы
5 Юрий Логистика
Дополнительно можно отфильтровать данные, проверяя их на NULL.
SELECT d.id, d.name
FROM users u
RIGHT OUTER JOIN departments d ON u.d_id = d.id
WHERE u.id IS NULL
В нашем примере указав WHERE u.id IS null, мы выберем департаменты, в которых не числятся пользователи. (3#Финансы)
Все примеры вы можете протестировать здесь:
SQLFiddle
Self Join
Выборка из одной и той же таблицы для нескольких условий.
Рассмотрим задачку от яндекса:
Есть таблица товаров.
CREATE TABLE `ya_goods` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO ya_goods VALUES (1, 'яблоки'), (2, 'яблоки') ,(3, 'груши'), (4,'яблоки'), (5, 'апельсины'), (6, 'груши');
Она содержит следующие значения.
`id` `name`
1 Яблоки
2 Яблоки
3 Груши
4 Яблоки
5 Апельсины
6 Груши
Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:
(1,2), (4,1), (2,4), (6,3)…
При решении задачи необходимо учесть, что пары (x,y) и (y,x) — одинаковы.
Решение:
SELECT g1.id id1, g2.id id2
-- CONCAT('(', LEAST(g1.id, g2.id), ',', GREATEST(g1.id, g2.id), ')') row
FROM ya_goods g1
INNER JOIN ya_goods g2 ON g1.name = g2.name
WHERE g1.id <> g2.id
GROUP BY LEAST(g1.id, g2.id), GREATEST(g1.id, g2.id)
ORDER BY g1.id;SELECT DISTINCT CONCAT(‘(‘, LEAST(g1.id, g2.id), ‘,’, GREATEST(g1.id, g2.id), ‘)’) row
FROM ya_goods g1
INNER JOIN ya_goods g2 ON g1.name = g2.name
WHERE g1.id <> g2.id
Объединяем таблицы ya_goods по одинаковому полю `name`, группируем по уникальным idентификаторам и получаем результат.
(1,2)(1,4)(2,4)(3,6)
Множественное объединение multi join
Пригодится нам, если необходимо выбрать более одного значения из таблиц для нескольких условий.
Пример: набор вариантов (вес, объем) товаров.
Продукты в таблице products, Варианты — таблица product_options, Значения вариантов — таблица product2options
Необходимо: фильтровать продукты по дате, и имеющимся вариантам
CREATE TABLE `products` (
`id` int(11),
`title` varchar(255),
`created_at` datetime
)
CREATE TABLE `product_options` (
`id` int(11),
`name` varchar(255)
)
CREATE TABLE `product2options` (
`product_id` int(11),
`option_id` int(11),
`value` int(11)
)
Тестовые данные
INSERT INTO `products` (`id`, `title`, `created_at`) VALUES
(1, 'Кружка', '2009-01-17 20:00:00'),
(2, 'Ложка', '2009-01-18 20:00:00'),
(3, 'Тарелка', '2009-01-19 20:00:00');,
`name`) VALUES
(11,
‘Вес’),
(12,
‘Объем’);
INSERT INTO `product2options` (`product_id`, `option_id`, `value`) VALUES
(1, 11, 200),
(1, 12, 250),
(2, 11, 35),
(2, 12, 15),
(3, 11, 310),
(3, 12, 300),
(2, 11, 45),
(2, 12, 25);
Пример: выбрать товары,
добавленные после 17/01/2009 в следующих вариантах:
- вес=310, объем=300
- вес=35, объем=15
- вес=45, объем=25
- вес=200, объем=250
Просто перечислить условия вариантов в подзапросе/джоине через OR/AND не сработает,
необходимо осуществить объединение таблиц вариантов равное количеству этих самых вариантов (у нас — 2: объем и вес)
SELECT p.*, po1.name 'P1', p2o1.value, po2.name 'P2', p2o2.value products p
INNER JOIN product2options p2o1 ON p.id = p2o1.product_id
INNER JOIN product_options po1 ON po1.id = p2o1.option_id
INNER JOIN product2options p2o2 ON p.id = p2o2.product_id
INNER JOIN product_options po2 ON po2.id = p2o2.option_id
WHERE p.created_at > ’2009-01-17 21:00′
AND ( — тарелка#3
p2o1.option_id = 11 AND p2o1.value = 310
AND p2o2.option_id = 12 AND p2o2.value = 300
OR — ложка#2
p2o1.option_id = 11 AND p2o1.value = 35
AND p2o2.option_id = 12 AND p2o2.value = 15
OR — ложка#2
p2o1.option_id = 11 AND p2o1.value = 45
AND p2o2.option_id = 12 AND p2o2.value = 25
OR — кружка#1 не попадает по дате
p2o1.option_id = 12 AND p2o1.value = 250
AND p2o2.option_id = 11 AND p2o2.value = 200
)
;
Результ выборки:
id title created_at P1 value P2 value
2 Ложка 2009-01-18 20:00:00 Вес 35 Объем 15
3 Тарелка 2009-01-19 20:00:00 Вес 310 Объем 300
2 Ложка 2009-01-18 20:00:00 Вес 45 Объем 25
1 Кружка 2009-01-17 20:00:00 Объем 250 Вес 200
Этот пример на SQLFiddle
За бортом статьи остались смежные объединениям (а также специфичные для определенных базданных темы):
SELF JOIN, FULL OUTER JOIN, CROSS JOIN (CROSS [OUTER] APPLY), операции над множествами UNION [ALL], INTERSECT, EXCEPT и т.д.
.