Связи между таблицами. JOIN. Агрегатные функции
Есть таблица workers и departments. Попробуем выбрать работников с их отделами.
SELECT workers.name as w_name, departments.name as d_name FROM workers, departments WHERE workers.dep_id = departments.id Теперь в PHP мы передаем таблицу с двумя полями w_name и d_name.
Проблема такого подхода, является то, что в начале из двух таблиц генерируется таблица, содержащая все возможные комбинации записей первой и второй таблиц. Если в первой таблице N записей, а во второй M, то в промежуточных вычислениях мы получаем таблицу с M*N записей. Если мы работаем с небольшими таблицами порядка 10000 строчек, то получим таблицу 100 миллионов записей. В дальнейшем с помощью WHERE мы вернем ее к 10K записей. Такой подход нерационален как по памяти, так и по времени.
SELECT workers.name as w_name, departments.name as d_name FROM workers JOIN departments WHERE workers.dep_id = departments.id JOIN объединит две таблицы, но при такой записи мы не получим выигрыша. Мы по прежнему получаем все комбинации в таблице, а потом их фильтруем
К счастью JOIN позволяет объединять таблицы сразу по условию
SELECT workers.name as w_name, departments.name as d_name FROM workers JOIN departments ON workers.dep_id = departments.id Попробуем сократить наш запрос, используя псевдонимы на этапе таблиц
SELECT w.name as w_name, d.name as d_name FROM workers as w JOIN departments as d ON w.dep_id = d.id Наш запрос имеет один недостаток – если работник не находится в каком-либо отделе, то он не попадет в результирующую выборку.
Чтобы исправить эту ситуацию, воспользуемся LEFT JOIN
SELECT w.name as w_name, d.name as d_name FROM workers as w LEFT JOIN departments as d ON w.dep_id = d.id Отличие LEFT JOIN от JOIN состоит в том, что записи первой (левой) таблицы берутся в любом случае. Если им не найдены соответствующие поля с таблицы справа, то вместо них подставляется NULL
RIGHT JOIN аналогичен только для таблицы справа
Связи между таблицами
users — profile (один к одному)
workers – departments (один ко многим)
posts – hashtags (многие ко многим)
На практике связь «многие ко многим» реализуется через промежуточную таблицу
Полезное чтиво:
- Создать таблицу departments(id,name) с названиями отделов, добавить поле dep_id в workers. Выбрать всех работников с названием отделов
- SpeedDating. Есть таблица с именами девушек, есть таблица с именами парней. Есть таблица знакомств — кто с кем познакомился. Вывести для каждой девушки список парней, которые с ней познакомились.
results matching » «
No results matching » «
Объяснение SQL объединений JOIN: LEFT/RIGHT/INNER/OUTER
Разберем пример. Имеем две таблицы: пользователи и отделы.
U) users D) departments
id name d_id id name
— —- —- — —-
1 Владимир 1 1 Сейлз
2 Антон 2 2 Поддержка
3 Александр 6 3 Финансы
4 Борис 2 4 Логистика
5 Юрий 4
Запрос вернет объединенные данные, которые пересекаются по условию, указанному в INNER JOIN ON .
В нашем случае условие . должен совпадать с .
В результате отсутствуют:
— пользователь Александр (отдел 6 — не существует)
— отдел Финансы (нет пользователей)
id name d_name
— ——— ———
1 Владимир Сейлз
2 Антон Поддержка
4 Борис Поддержка
3 Юрий Логистика
Внутреннее объединение INNER JOIN (синоним JOIN, ключевое слово INNER можно опустить).
Выбираются только совпадающие данные из объединяемых таблиц.
Чтобы получить данные, которые подходят по условию частично, необходимо использовать
внешнее объединение — OUTER JOIN.
Такое объединение вернет данные из обеих таблиц (совпадающие по условию объединения) ПЛЮС дополнит выборку оставшимися данными из внешней таблицы, которые по условию не подходят, заполнив недостающие данные значением NULL.
Существует два типа внешнего объединения OUTER JOIN — LEFT OUTER JOIN и RIGHT OUTER JOIN.
Работают они одинаково, разница заключается в том что LEFT — указывает что «внешней» таблицей будет находящаяся слева (в нашем примере это таблица users).
Ключевое слово OUTER можно опустить. Запись LEFT JOIN идентична LEFT OUTER JOIN.
Получаем полный список пользователей и сопоставленные департаменты.
id name d_name
— ——— ———
1 Владимир Сейлз
2 Антон Поддержка
3 Александр NULL
4 Борис Поддержка
5 Юрий Логистика
в выборке останется только 3#Александр, так как у него не назначен департамент.
рис. Left outer join с фильтрацией по полю
RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей.
id name d_name
— ——— ———
1 Владимир Сейлз
2 Антон Поддержка
4 Борис Поддержка
NULL NULL Финансы
5 Юрий Логистика
Дополнительно можно отфильтровать данные, проверяя их на NULL.
В нашем примере указав WHERE u.id IS null, мы выберем департаменты, в которых не числятся пользователи. (3#Финансы)
Все примеры вы можете протестировать здесь:
Cross/Full Join
FULL JOIN возвращает `объединение` объединений LEFT и RIGHT таблиц, комбинируя результат двух запросов.
CROSS JOIN возвращает перекрестное (декартово) объединение двух таблиц. Результатом будет выборка всех записей первой таблицы объединенная с каждой строкой второй таблицы. Важным моментом является то, что для кросса не нужно указывать условие объединения.
Дублирование строк при использовании JOIN
При использовании объединения новички часто забывают что результирующая выборка может содержать дублирующиеся данные!
Если вам нужна одна запись, делайте объединение с подзапросом
SELECT t1. * , t2. * from left_table t1 left join ( select * from right_table where some_column = 1 limit 1 ) t2 ON t1.id = t2.join_id
Выборка из одной и той же таблицы для нескольких условий.
Рассмотрим задачку от яндекса:
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`, например:
При решении задачи необходимо учесть, что пары (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ентификаторам и получаем результат.
Множественное объединение 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’ ) ;
INSERT INTO `product _ options` ( `id` , `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
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
UPDATE и JOIN
Объединение можно использовать совместно с UPDATE.
Например, имеем таблицу houses (id, title, area). Нужно выбрать title, если в нем встречается `число м2`, заменить поле area, если оно меньше. Т.к. в mysql отстутсутствует поддержка регулярных выражений, нужно немного поколдовать с locate и substr.
В подзапросе выбираем интересующие нас данные, и в финальной стадии осуществляем обновление данных подходящий по критерию (p5 > area).
UPDATE houses base
INNER JOIN (
— Антарис аренда офиса 1594 м2, по ставке 12700 руб. м2/год -> 1594
SELECT
id ,
@baseString := title title ,
@areaTitleEnd := LOCATE ( ‘ м2’ , @baseString ) as p2 ,
@tmpString := LTRIM ( REVERSE ( SUBSTR ( @baseString , 1 , @areaTitleEnd ) ) ) as p3 ,
@areaTitleBegin := LEFT ( @tmpString , — 1 + LOCATE ( ‘ ‘ , @tmpString ) ) as p4 ,
@ value := CAST ( REVERSE ( @areaTitleBegin ) as UNSIGNED ) as p5
FROM ga_pageviews
WHERE title like ‘ % м2 % ‘
) calc USING ( `id` )
SET base. area = calc.p5
WHERE base. area < calc.p5
DELETE и JOIN
Рассмотрим пример с удалением дубликатов. Есть таблица tableWithDups (id, email). Нужно удалить строки с одинаковыми email:
DELETE tableWithDups
FROM tableWithDups
INNER JOIN (
SELECT MAX ( id ) AS lastId , email
FROM tableWithDups
GROUP BY email
HAVING COUNT ( * ) > 1
) dups ON dups.email = tableWithDups.email
WHERE tableWithDups.id < dups.lastId ;
Последние два примера не совместимы с ANSI SQL, но работают в mySQL.
За бортом статьи остались смежные объединениям (а также специфичные для определенных базданных темы):
SELF JOIN, FULL OUTER JOIN, CROSS JOIN (CROSS [OUTER] APPLY), операции над множествами UNION [ALL], INTERSECT, EXCEPT и т.д.
@tags: sql, mysql, sql server, oracle, sqlite, postgresql