Sql join where: Как ведет себя JOIN & WHERE? — Хабр Q&A
Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть четвертая / Хабр
Предыдущие части
В данной части мы рассмотрим
Многотабличные запросы:
- Операции горизонтального соединения таблиц – JOIN
- Связь таблиц при помощи WHERE-условия
- Операции вертикального объединения результатов запросов – UNION
Работу с подзапросами:
- Подзапросы в блоках FROM, SELECT
- Подзапрос в конструкции APPLY
- Использование предложения WITH
- Подзапросы в блоке WHERE:
- Групповое сравнение — ALL, ANY
- Условие EXISTS
- Условие IN
Добавим немного новых данных
Для демонстрационных целей добавим несколько отделов и должностей:
SET IDENTITY_INSERT Departments ON
INSERT Departments(ID,Name) VALUES(4,N'Маркетинг и реклама')
INSERT Departments(ID,Name) VALUES(5,N'Логистика')
SET IDENTITY_INSERT Departments OFF
SET IDENTITY_INSERT Positions ON
INSERT Positions(ID,Name) VALUES(5,N'Маркетолог')
INSERT Positions(ID,Name) VALUES(6,N'Логист')
INSERT Positions(ID,Name) VALUES(7,N'Кладовщик')
SET IDENTITY_INSERT Positions OFF
JOIN-соединения – операции горизонтального соединения данных
Здесь нам очень пригодится знание структуры БД, т. е. какие в ней есть таблицы, какие данные хранятся в этих таблицах и по каким полям таблицы связаны между собой. Первым делом всегда досконально изучайте структуру БД, т.к. нормальный запрос можно написать только тогда, когда ты знаешь, что откуда берется. У нас структура состоит из 3-х таблиц Employees, Departments и Positions. Приведу здесь диаграмму из первой части:
Если суть РДБ – разделяй и властвуй, то суть операций объединений снова склеить разбитые по таблицам данные, т.е. привести их обратно в человеческий вид.
Если говорить просто, то операции горизонтального соединения таблицы с другими таблицами используются для того, чтобы получить из них недостающие данные. Вспомните пример с еженедельным отчетом для директора, когда при запросе из таблицы Employees, нам для получения окончательного результата недоставало поля «Название отдела», которое находится в таблице Departments.
Начнем с теории. Есть пять типов соединения:
- JOIN – левая_таблица JOIN правая_таблица ON условия_соединения
- LEFT JOIN – левая_таблица LEFT JOIN правая_таблица ON условия_соединения
- RIGHT JOIN – левая_таблица RIGHT JOIN правая_таблица ON условия_соединения
- FULL JOIN – левая_таблица FULL JOIN правая_таблица ON условия_соединения
- CROSS JOIN – левая_таблица CROSS JOIN правая_таблица
Краткий синтаксис | Полный синтаксис | Описание (Это не всегда всем сразу понятно. Так что, если не понятно, то просто вернитесь сюда после рассмотрения примеров.) |
---|---|---|
JOIN | INNER JOIN | Из строк левой_таблицы и правой_таблицы объединяются и возвращаются только те строки, по которым выполняются условия_соединения. |
LEFT JOIN | LEFT OUTER JOIN | Возвращаются все строки левой_таблицы (ключевое слово LEFT). Данными правой_таблицы дополняются только те строки левой_таблицы, для которых выполняются условия_соединения. Для недостающих данных вместо строк правой_таблицы вставляются NULL-значения. |
RIGHT JOIN | RIGHT OUTER JOIN | Возвращаются все строки правой_таблицы (ключевое слово RIGHT). Данными левой_таблицы дополняются только те строки правой_таблицы, для которых выполняются условия_соединения. Для недостающих данных вместо строк левой_таблицы вставляются NULL-значения. |
FULL JOIN | FULL OUTER JOIN | Возвращаются все строки левой_таблицы и правой_таблицы. Если для строк левой_таблицы и правой_таблицы выполняются условия_соединения, то они объединяются в одну строку. Для строк, для которых не выполняются условия_соединения, NULL-значения вставляются на место левой_таблицы, либо на место правой_таблицы, в зависимости от того данных какой таблицы в строке не имеется. |
CROSS JOIN | — | Объединение каждой строки левой_таблицы со всеми строками правой_таблицы. Этот вид соединения иногда называют декартовым произведением. |
Как видно из таблицы полный синтаксис от краткого отличается только наличием слов INNER или OUTER.
Лично я всегда при написании запросов использую только краткий синтаксис, по той причине:
- Это короче и не засоряет запрос лишними словами;
- По словам LEFT, RIGHT, FULL и CROSS и так понятно о каком соединении идет речь, так же и в случае просто JOIN;
- Считаю слова INNER и OUTER в данном случае ненужными рудиментами, которые больше путают начинающих.
Но конечно, это мое личное предпочтение, возможно кому-то нравится писать длинно, и он видит в этом свои прелести.
Понимание каждого вида соединения очень важно, т.к. от применения того или иного вида, результат запроса может отличаться. Сравните результаты одного и того же запроса с применением разного типа соединения, попробуйте пока просто увидеть разницу и идите дальше (мы сюда еще вернемся):
-- JOIN вернет 5 строк
SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM Employees emp
JOIN Departments dep ON emp.DepartmentID=dep.ID
ID | Name | DepartmentID | ID | Name |
---|---|---|---|---|
1000 | Иванов И.И. | 1 | 1 | Администрация |
1001 | Петров П.П. | 3 | 3 | ИТ |
1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
1003 | Андреев А. А. | 3 | 3 | ИТ |
1004 | Николаев Н.Н. | 3 | 3 | ИТ |
-- LEFT JOIN вернет 6 строк
SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
ID | Name | DepartmentID | ID | Name |
---|---|---|---|---|
1000 | Иванов И.И. | 1 | 1 | Администрация |
1001 | Петров П.П. | 3 | 3 | ИТ |
1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
1003 | Андреев А.А. | 3 | 3 | ИТ |
1004 | Николаев Н.Н. | 3 | 3 | ИТ |
1005 | Александров А. А. | NULL | NULL | NULL |
-- RIGHT JOIN вернет 7 строк
SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM Employees emp
RIGHT JOIN Departments dep ON emp.DepartmentID=dep.ID
ID | Name | DepartmentID | ID | Name |
---|---|---|---|---|
1000 | Иванов И.И. | 1 | 1 | Администрация |
1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
1001 | Петров П.П. | 3 | 3 | ИТ |
1003 | Андреев А.А. | 3 | 3 | ИТ |
1004 | Николаев Н.Н. | 3 | 3 | ИТ |
NULL | NULL | NULL | 4 | Маркетинг и реклама |
NULL | NULL | NULL | 5 | Логистика |
-- FULL JOIN вернет 8 строк
SELECT emp. ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM Employees emp
FULL JOIN Departments dep ON emp.DepartmentID=dep.ID
ID | Name | DepartmentID | ID | Name |
---|---|---|---|---|
1000 | Иванов И.И. | 1 | 1 | Администрация |
1001 | Петров П.П. | 3 | 3 | ИТ |
1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
1003 | Андреев А.А. | 3 | 3 | ИТ |
1004 | Николаев Н.Н. | 3 | 3 | ИТ |
1005 | Александров А.А. | NULL | NULL | NULL |
NULL | NULL | NULL | 4 | Маркетинг и реклама |
NULL | NULL | NULL | 5 | Логистика |
-- CROSS JOIN вернет 30 строк - (6 строк таблицы Employees) * (5 строк таблицы Departments)
SELECT emp. ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM Employees emp
CROSS JOIN Departments dep
ID | Name | DepartmentID | ID | Name |
---|---|---|---|---|
1000 | Иванов И.И. | 1 | 1 | Администрация |
1001 | Петров П.П. | 3 | 1 | Администрация |
1002 | Сидоров С.С. | 2 | 1 | Администрация |
1003 | Андреев А.А. | 3 | 1 | Администрация |
1004 | Николаев Н.Н. | 3 | 1 | Администрация |
1005 | Александров А.А. | NULL | 1 | Администрация |
1000 | Иванов И.И. | 1 | 2 | Бухгалтерия |
1001 | Петров П. П. | 3 | 2 | Бухгалтерия |
1002 | Сидоров С.С. | 2 | 2 | Бухгалтерия |
1003 | Андреев А.А. | 3 | 2 | Бухгалтерия |
1004 | Николаев Н.Н. | 3 | 2 | Бухгалтерия |
1005 | Александров А.А. | NULL | 2 | Бухгалтерия |
1000 | Иванов И.И. | 1 | 3 | ИТ |
1001 | Петров П.П. | 3 | 3 | ИТ |
1002 | Сидоров С.С. | 2 | 3 | ИТ |
1003 | Андреев А.А. | 3 | 3 | ИТ |
1004 | Николаев Н.Н. | 3 | 3 | ИТ |
1005 | Александров А. А. | NULL | 3 | ИТ |
1000 | Иванов И.И. | 1 | 4 | Маркетинг и реклама |
1001 | Петров П.П. | 3 | 4 | Маркетинг и реклама |
1002 | Сидоров С.С. | 2 | 4 | Маркетинг и реклама |
1003 | Андреев А.А. | 3 | 4 | Маркетинг и реклама |
1004 | Николаев Н.Н. | 3 | 4 | Маркетинг и реклама |
1005 | Александров А.А. | NULL | 4 | Маркетинг и реклама |
1000 | Иванов И.И. | 1 | 5 | Логистика |
1001 | Петров П.П. | 3 | 5 | Логистика |
1002 | Сидоров С. С. | 2 | 5 | Логистика |
1003 | Андреев А.А. | 3 | 5 | Логистика |
1004 | Николаев Н.Н. | 3 | 5 | Логистика |
1005 | Александров А.А. | NULL | 5 | Логистика |
Настало время вспомнить про псевдонимы таблиц
Пришло время вспомнить про псевдонимы таблиц, о которых я рассказывал в начале второй части.
В многотабличных запросах, псевдоним помогает нам явно указать из какой именно таблицы берется поле. Посмотрим на пример:
SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM Employees emp
JOIN Departments dep ON emp.DepartmentID=dep.ID
В нем поля с именами ID и Name есть в обоих таблицах и в Employees, и в Departments. И чтобы их различать, мы предваряем имя поля псевдонимом и точкой, т. е. «emp.ID», «emp.Name», «dep.ID», «dep.Name».
Вспоминаем почему удобнее пользоваться именно короткими псевдонимами – потому что, без псевдонимов наш запрос бы выглядел следующим образом:
SELECT Employees.ID,Employees.Name,Employees.DepartmentID,Departments.ID,Departments.Name
FROM Employees
JOIN Departments ON Employees.DepartmentID=Departments.ID
По мне, стало очень длинно и хуже читаемо, т.к. имена полей визуально потерялись среди повторяющихся имен таблиц.
В многотабличных запросах, хоть и можно указать имя без псевдонима, в случае если имя не дублируется во второй таблице, но я бы рекомендовал всегда использовать псевдонимы в случае соединения, т.к. никто не гарантирует, что поле с таким же именем со временем не добавят во вторую таблицу, а тогда ваш запрос просто сломается, ругаясь на то что он не может понять к какой таблице относится данное поле.
Только используя псевдонимы, мы сможем осуществить соединения таблицы самой с собой. Предположим встала задача, получить для каждого сотрудника, данные сотрудника, который был принят прямо до него (табельный номер отличается на единицу меньше). Допустим, что у нас табельные номера выдаются последовательно и без дырок, тогда мы можем это сделать примерно следующим образом:
SELECT
e1.ID EmpID1,
e1.Name EmpName1,
e2.ID EmpID2,
e2.Name EmpName2
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ID=e2.ID+1 -- получить данные предыдущего сотрудника
Т.е. здесь одной таблице Employees, мы дали псевдоним «e1», а второй «e2».
Разбираем каждый вид горизонтального соединения
Для этой цели рассмотрим 2 небольшие абстрактные таблицы, которые так и назовем LeftTable и RightTable:
CREATE TABLE LeftTable(
LCode int,
LDescr varchar(10)
)
GO
CREATE TABLE RightTable(
RCode int,
RDescr varchar(10)
)
GO
INSERT LeftTable(LCode,LDescr)VALUES
(1,'L-1'),
(2,'L-2'),
(3,'L-3'),
(5,'L-5')
INSERT RightTable(RCode,RDescr)VALUES
(2,'B-2'),
(3,'B-3'),
(4,'B-4')
Посмотрим, что в этих таблицах:
SELECT * FROM LeftTable
LCode | LDescr |
---|---|
1 | L-1 |
2 | L-2 |
3 | L-3 |
5 | L-5 |
SELECT * FROM RightTable
RCode | RDescr |
---|---|
2 | B-2 |
3 | B-3 |
4 | B-4 |
JOIN
SELECT l. *,r.*
FROM LeftTable l
JOIN RightTable r ON l.LCode=r.RCode
LCode | LDescr | RCode | RDescr |
---|---|---|---|
2 | L-2 | 2 | B-2 |
3 | L-3 | 3 | B-3 |
Здесь были возвращены объединения строк для которых выполнилось условие (l.LCode=r.RCode)
LEFT JOIN
SELECT l.*,r.*
FROM LeftTable l
LEFT JOIN RightTable r ON l.LCode=r.RCode
LCode | LDescr | RCode | RDescr |
---|---|---|---|
1 | L-1 | NULL | NULL |
2 | L-2 | 2 | B-2 |
3 | L-3 | 3 | B-3 |
5 | L-5 | NULL | NULL |
Здесь были возвращены все строки LeftTable, которые были дополнены данными строк из RightTable, для которых выполнилось условие (l. LCode=r.RCode)
RIGHT JOIN
SELECT l.*,r.*
FROM LeftTable l
RIGHT JOIN RightTable r ON l.LCode=r.RCode
LCode | LDescr | RCode | RDescr |
---|---|---|---|
2 | L-2 | 2 | B-2 |
3 | L-3 | 3 | B-3 |
NULL | NULL | 4 | B-4 |
Здесь были возвращены все строки RightTable, которые были дополнены данными строк из LeftTable, для которых выполнилось условие (l.LCode=r.RCode)
По сути если мы переставим LeftTable и RightTable местами, то аналогичный результат мы получим при помощи левого соединения:
SELECT l.*,r.*
FROM RightTable r
LEFT JOIN LeftTable l ON l.LCode=r.RCode
LCode | LDescr | RCode | RDescr |
---|---|---|---|
2 | L-2 | 2 | B-2 |
3 | L-3 | 3 | B-3 |
NULL | NULL | 4 | B-4 |
Я за собой заметил, что я чаще применяю именно LEFT JOIN, т. е. я сначала думаю, данные какой таблицы мне важны, а потом думаю, какая таблица/таблицы будет играть роль дополняющей таблицы.
FULL JOIN – это по сути одновременный LEFT JOIN + RIGHT JOIN
SELECT l.*,r.*
FROM LeftTable l
FULL JOIN RightTable r ON l.LCode=r.RCode
LCode | LDescr | RCode | RDescr |
---|---|---|---|
1 | L-1 | NULL | NULL |
2 | L-2 | 2 | B-2 |
3 | L-3 | 3 | B-3 |
5 | L-5 | NULL | NULL |
NULL | NULL | 4 | B-4 |
Вернулись все строки из LeftTable и RightTable. Строки для которых выполнилось условие (l.LCode=r.RCode) были объединены в одну строку. Отсутствующие в строке данные с левой или правой стороны заполняются NULL-значениями.
CROSS JOIN
SELECT l.*,r.*
FROM LeftTable l
CROSS JOIN RightTable r
LCode | LDescr | RCode | RDescr |
---|---|---|---|
1 | L-1 | 2 | B-2 |
2 | L-2 | 2 | B-2 |
3 | L-3 | 2 | B-2 |
5 | L-5 | 2 | B-2 |
1 | L-1 | 3 | B-3 |
2 | L-2 | 3 | B-3 |
3 | L-3 | 3 | B-3 |
5 | L-5 | 3 | B-3 |
1 | L-1 | 4 | B-4 |
2 | L-2 | 4 | B-4 |
3 | L-3 | 4 | B-4 |
5 | L-5 | 4 | B-4 |
Каждая строка LeftTable соединяется с данными всех строк RightTable.
Возвращаемся к таблицам Employees и Departments
Надеюсь вы поняли принцип работы горизонтальных соединений. Если это так, то возвратитесь на начало раздела «JOIN-соединения – операции горизонтального соединения данных» и попробуйте самостоятельно понять примеры с объединением таблиц Employees и Departments, а потом снова возвращайтесь сюда, обсудим это вместе.
Давайте попробуем вместе подвести резюме для каждого запроса:
Запрос | Резюме |
---|---|
| По сути данный запрос вернет только сотрудников, у которых указано значение DepartmentID. Т.е. мы можем использовать данное соединение, в случае, когда нам нужны данные по сотрудникам числящихся за каким-нибудь отделом (без учета внештаткиков). |
| Вернет всех сотрудников. Для тех сотрудников у которых не указан DepartmentID, поля «dep.ID» и «dep.Name» будут содержать NULL. Вспоминайте, что NULL значения в случае необходимости можно обработать, например, при помощи ISNULL(dep.Name,’вне штата’). Этот вид соединения можно использовать, когда нам важно получить данные по всем сотрудникам, например, чтобы получить список для начисления ЗП. |
| Здесь мы получили дырки слева, т.е. отдел есть, но сотрудников в этом отделе нет. Такое соединение можно использовать, например, когда нужно выяснить, какие отделы и кем у нас заняты, а какие еще не сформированы. Эту информацию можно использовать для поиска и приема новых работников из которых будет формироваться отдел. |
| Этот запрос важен, когда нам нужно получить все данные по сотрудникам и все данные по имеющимся отделам. Соответственно получаем дырки (NULL-значения) либо по сотрудникам, либо по отделам (внештатники). Данный запрос, например, может использоваться в целях проверки, все ли сотрудники сидят в правильных отделах, т.к. может у некоторых сотрудников, которые числятся как внештатники, просто забыли указать отдел. |
| В таком виде даже сложно придумать где это можно применить, поэтому пример с CROSS JOIN я покажу ниже. |
Обратите внимание, что в случае повторения значений DepartmentID в таблице Employees, произошло соединение каждой такой строки со строкой из таблицы Departments с таким же ID, то есть данные Departments объединились со всеми записями для которых выполнилось условие (emp. DepartmentID=dep.ID):
В нашем случае все получилось правильно, т.е. мы дополнили таблицу Employees, данными таблицы Departments. Я специально заострил на этом внимание, т.к. бывают случаи, когда такое поведение нам не нужно. Для демонстрации поставим задачу – для каждого отдела вывести последнего принятого сотрудника, если сотрудников нет, то просто вывести название отдела. Возможно напрашивается такое решение – просто взять предыдущий запрос и поменять условие соединение на RIGHT JOIN, плюс переставить поля местами:
SELECT dep.ID,dep.Name,emp.ID,emp.Name
FROM Employees emp
RIGHT JOIN Departments dep ON emp.DepartmentID=dep.ID
ID | Name | ID | Name |
---|---|---|---|
1 | Администрация | 1000 | Иванов И.И. |
2 | Бухгалтерия | 1002 | Сидоров С.С. |
3 | ИТ | 1001 | Петров П. П. |
3 | ИТ | 1003 | Андреев А.А. |
3 | ИТ | 1004 | Николаев Н.Н. |
4 | Маркетинг и реклама | NULL | NULL |
5 | Логистика | NULL | NULL |
Но мы для ИТ-отдела получили три строчки, когда нам нужна была только строчка с последним принятым сотрудником, т.е. Николаевым Н.Н.
Задачу такого рода, можно решить, например, при помощи использования подзапроса:
SELECT dep.ID,dep.Name,emp.ID,emp.Name
FROM Employees emp
/*
объединяем с подзапросом возвращающим последний (максимальный - MAX(ID))
идентификатор сотрудника для каждого отдела (GROUP BY DepartmentID)
*/
JOIN
(
SELECT MAX(ID) MaxEmployeeID
FROM Employees
GROUP BY DepartmentID
) lastEmp
ON emp.ID=lastEmp.MaxEmployeeID
RIGHT JOIN Departments dep ON emp.DepartmentID=dep.ID -- все данные Departments
ID | Name | ID | Name |
---|---|---|---|
1 | Администрация | 1000 | Иванов И.И. |
2 | Бухгалтерия | 1002 | Сидоров С.С. |
3 | ИТ | 1004 | Николаев Н.Н. |
4 | Маркетинг и реклама | NULL | NULL |
5 | Логистика | NULL | NULL |
При помощи предварительного объединения Employees с данными подзапроса, мы смогли оставить только нужных нам для соединения с Departments сотрудников.
Здесь мы плавно переходим к использованию подзапросов. Я думаю использование их в таком виде должно быть для вас понятно на интуитивном уровне. То есть подзапрос подставляется на место таблицы и играет ее роль, ничего сложного. К теме подзапросов мы еще вернемся отдельно.
Посмотрите отдельно, что возвращает подзапрос:
SELECT MAX(ID) MaxEmployeeID
FROM Employees
GROUP BY DepartmentID
MaxEmployeeID |
---|
1005 |
1000 |
1002 |
1004 |
Т.е. он вернул только идентификаторы последних принятых сотрудников, в разрезе отделов.
Соединения выполняются последовательно сверху-вниз, наращиваясь как снежный ком, который катится с горы. Сначала происходит соединение «Employees emp JOIN (Подзапрос) lastEmp», формируя новый выходной набор:
Потом идет объединение набора, полученного «Employees emp JOIN (Подзапрос) lastEmp» (назовем его условно «ПоследнийРезультат») с Departments, т.е. «ПоследнийРезультат RIGHT JOIN Departments dep»:
Самостоятельная работа для закрепления материала
Если вы новичок, то вам обязательно нужно прорабатывать каждую JOIN-конструкцию, до тех пор, пока вы на 100% не будете понимать, как работает каждый вид соединения и правильно представлять результат какого вида будет получен в итоге.
Для закрепления материала про JOIN-соединения сделаем следующее:
-- очистим таблицы LeftTable и RightTable
TRUNCATE TABLE LeftTable
TRUNCATE TABLE RightTable
GO
-- и зальем в них другие данные
INSERT LeftTable(LCode,LDescr)VALUES
(1,'L-1'),
(2,'L-2a'),
(2,'L-2b'),
(3,'L-3'),
(5,'L-5')
INSERT RightTable(RCode,RDescr)VALUES
(2,'B-2a'),
(2,'B-2b'),
(3,'B-3'),
(4,'B-4')
Посмотрим, что в таблицах:
SELECT *
FROM LeftTable
LCode | LDescr |
---|---|
1 | L-1 |
2 | L-2a |
2 | L-2b |
3 | L-3 |
5 | L-5 |
SELECT *
FROM RightTable
RCode | RDescr |
---|---|
2 | B-2a |
2 | B-2b |
3 | B-3 |
4 | B-4 |
А теперь попытайтесь сами разобрать, каким образом получилась каждая строчка запроса с каждым видом соединения (Excel вам в помощь):
SELECT l.*,r.*
FROM LeftTable l
JOIN RightTable r ON l.LCode=r.RCode
LCode | LDescr | RCode | RDescr |
---|---|---|---|
2 | L-2a | 2 | B-2a |
2 | L-2a | 2 | B-2b |
2 | L-2b | 2 | B-2a |
2 | L-2b | 2 | B-2b |
3 | L-3 | 3 | B-3 |
SELECT l.*,r.*
FROM LeftTable l
LEFT JOIN RightTable r ON l.LCode=r.RCode
LCode | LDescr | RCode | RDescr |
---|---|---|---|
1 | L-1 | NULL | NULL |
2 | L-2a | 2 | B-2a |
2 | L-2a | 2 | B-2b |
2 | L-2b | 2 | B-2a |
2 | L-2b | 2 | B-2b |
3 | L-3 | 3 | B-3 |
5 | L-5 | NULL | NULL |
SELECT l.*,r.*
FROM LeftTable l
RIGHT JOIN RightTable r ON l.LCode=r.RCode
LCode | LDescr | RCode | RDescr |
---|---|---|---|
2 | L-2a | 2 | B-2a |
2 | L-2b | 2 | B-2a |
2 | L-2a | 2 | B-2b |
2 | L-2b | 2 | B-2b |
3 | L-3 | 3 | B-3 |
NULL | NULL | 4 | B-4 |
SELECT l.*,r.*
FROM LeftTable l
FULL JOIN RightTable r ON l.LCode=r.RCode
LCode | LDescr | RCode | RDescr |
---|---|---|---|
1 | L-1 | NULL | NULL |
2 | L-2a | 2 | B-2a |
2 | L-2a | 2 | B-2b |
2 | L-2b | 2 | B-2a |
2 | L-2b | 2 | B-2b |
3 | L-3 | 3 | B-3 |
5 | L-5 | NULL | NULL |
NULL | NULL | 4 | B-4 |
SELECT l.*,r.*
FROM LeftTable l
CROSS JOIN RightTable r
LCode | LDescr | RCode | RDescr |
---|---|---|---|
1 | L-1 | 2 | B-2a |
2 | L-2a | 2 | B-2a |
2 | L-2b | 2 | B-2a |
3 | L-3 | 2 | B-2a |
5 | L-5 | 2 | B-2a |
1 | L-1 | 2 | B-2b |
2 | L-2a | 2 | B-2b |
2 | L-2b | 2 | B-2b |
3 | L-3 | 2 | B-2b |
5 | L-5 | 2 | B-2b |
1 | L-1 | 3 | B-3 |
2 | L-2a | 3 | B-3 |
2 | L-2b | 3 | B-3 |
3 | L-3 | 3 | B-3 |
5 | L-5 | 3 | B-3 |
1 | L-1 | 4 | B-4 |
2 | L-2a | 4 | B-4 |
2 | L-2b | 4 | B-4 |
3 | L-3 | 4 | B-4 |
5 | L-5 | 4 | B-4 |
Еще раз про JOIN-соединения
Еще один пример с использованием нескольких последовательных операций соединении. Здесь повтор получился не специально, так получилось – не выбрасывать же материал. 😉 Но ничего «повторение – мать учения».
Если используется несколько операций соединения, то в таком случае они применяются последовательно сверху-вниз. Грубо говоря, после каждого соединения создается новый набор и следующее соединение уже происходит с этим расширенным набором. Рассмотрим простой пример:
SELECT
e.ID,
e.Name EmployeeName,
p.Name PositionName,
d.Name DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID=d.ID
LEFT JOIN Positions p ON e.PositionID=p.ID
Первым делом выбрались все записи таблицы Employees:
SELECT
e.*
FROM Employees e -- 1
Дальше произошло соединение с таблицей Departments:
SELECT
e.*, -- к полям Employees
d.* -- добавились соответствующие (e.DepartmentID=d.ID) поля Departments
FROM Employees e -- 1
LEFT JOIN Departments d ON e.DepartmentID=d.ID -- 2
Дальше уже идет соединение этого набора с таблицей Positions:
SELECT
e.*, -- к полям Employees
d.*, -- добавились соответствующие (e.DepartmentID=d.ID) поля Departments
p.* -- добавились соответствующие (e.PositionID=p.ID) поля Positions
FROM Employees e -- 1
LEFT JOIN Departments d ON e.DepartmentID=d.ID -- 2
LEFT JOIN Positions p ON e.PositionID=p.ID -- 3
Т.е. это выглядит примерно так:
И в последнюю очередь идет возврат тех данных, которые мы просим вывести:
SELECT
e.ID, -- 1. идентификатор сотрудника
e.Name EmployeeName, -- 2. имя сотрудника
p.Name PositionName, -- 3. название должности
d.Name DepartmentName -- 4. название отдела
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID=d.ID
LEFT JOIN Positions p ON e.PositionID=p.ID
Соответственно, ко всему этому полученному набору можно применить фильтр WHERE и сортировку ORDER BY:
SELECT
e.ID, -- 1. идентификатор сотрудника
e.Name EmployeeName, -- 2. имя сотрудника
p.Name PositionName, -- 3. название должности
d.Name DepartmentName -- 4. название отдела
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID=d.ID
LEFT JOIN Positions p ON e.PositionID=p.ID
WHERE d.ID=3 -- используем поля из поле ID из Departments
AND p.ID=3 -- используем для фильтрации поле ID из Positions
ORDER BY e.Name -- используем для сортировки поле Name из Employees
ID | EmployeeName | PositionName | DepartmentName |
---|---|---|---|
1004 | Николаев Н.Н. | Программист | ИТ |
1001 | Петров П.П. | Программист | ИТ |
То есть последний полученный набор – представляет собой такую же таблицу, над которой можно выполнять базовый запрос:
SELECT [DISTINCT] список_столбцов или *
FROM источник
WHERE фильтр
ORDER BY выражение_сортировки
То есть если раньше в роли источника выступала только одна таблица, то теперь на это место мы просто подставляем наше выражение:
Employees e
LEFT JOIN Departments d ON e.DepartmentID=d.ID
LEFT JOIN Positions p ON e.PositionID=p.ID
В результате чего получаем тот же самый базовый запрос:
SELECT
e.ID,
e.Name EmployeeName,
p.Name PositionName,
d.Name DepartmentName
FROM
/* источник - начало */
Employees e
LEFT JOIN Departments d ON e.DepartmentID=d.ID
LEFT JOIN Positions p ON e.PositionID=p.ID
/* источник - конец */
WHERE d.ID=3
AND p.ID=3
ORDER BY e.Name
А теперь, применим группировку:
SELECT
ISNULL(dep.Name,'Прочие') DepName,
COUNT(DISTINCT emp.PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(emp.Salary) SalaryAmount,
AVG(emp.Salary) SalaryAvg -- плюс выполняем пожелание директора
FROM
/* источник - начало */
Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
/* источник - конец */
GROUP BY emp.DepartmentID,dep.Name
ORDER BY DepName
Видите, мы все так же крутимся вокруг да около базовых конструкций, теперь надеюсь понятно, почему очень важно в первую очередь хорошо понять их.
И как мы увидели, в запросе на месте любой таблицы может стоять подзапрос. В свою очередь подзапросы могут быть вложены в подзапросы. И все эти подзапросы тоже представляют из себя базовые конструкции. То есть базовая конструкция, это кирпичики, из которых строится любой запрос.
Обещанный пример с CROSS JOIN
Давайте используем соединение CROSS JOIN, чтобы подсчитать сколько сотрудников, в каком отделе и на каких должностях числится. Для каждого отдела перечислим все существующие должности:
SELECT
d.Name DepartmentName,
p.Name PositionName,
e.EmplCount
FROM Departments d
CROSS JOIN Positions p
LEFT JOIN
(
/*
здесь я использовал подзапрос для подсчета сотрудников
в разрезе групп (DepartmentID,PositionID)
*/
SELECT DepartmentID,PositionID,COUNT(*) EmplCount
FROM Employees
GROUP BY DepartmentID,PositionID
) e
ON e.DepartmentID=d.ID AND e.PositionID=p.ID
ORDER BY DepartmentName,PositionName
В данном случае сначала выполнилось соединение при помощи CROSS JOIN, а затем к полученному набору сделалось соединение с данными из подзапроса при помощи LEFT JOIN. Вместо таблицы в LEFT JOIN мы использовали подзапрос.
Подзапрос заключается в скобки и ему присваивается псевдоним, в данном случае это «e». То есть в данном случае объединение происходит не с таблицей, а с результатом следующего запроса:
SELECT DepartmentID,PositionID,COUNT(*) EmplCount
FROM Employees
GROUP BY DepartmentID,PositionID
DepartmentID | PositionID | EmplCount |
---|---|---|
NULL | NULL | 1 |
2 | 1 | 1 |
1 | 2 | 1 |
3 | 3 | 2 |
3 | 4 | 1 |
Вместе с псевдонимом «e» мы можем использовать имена DepartmentID, PositionID и EmplCount. По сути дальше подзапрос ведет себя так же, как если на его месте стояла таблица. Соответственно, как и у таблицы,
все имена колонок, которые возвращает подзапрос, должны быть заданы явно и не должны повторяться.
Связь при помощи WHERE-условия
Для примера перепишем следующий запрос с JOIN-соединением:
SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM Employees emp
JOIN Departments dep ON emp.DepartmentID=dep.ID -- условие соединения таблиц
WHERE emp.DepartmentID=3 -- условие фильтрации данных
Через WHERE-условие он примет следующую форму:
SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM
Employees emp,
Departments dep
WHERE emp.DepartmentID=dep.ID -- условие соединения таблиц
AND emp.DepartmentID=3 -- условие фильтрации данных
Здесь плохо то, что происходит смешивание условий соединения таблиц (emp.DepartmentID=dep.ID) с условием фильтрации (emp.DepartmentID=3).
Теперь посмотрим, как сделать CROSS JOIN:
SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM Employees emp
CROSS JOIN Departments dep -- декартово соединение (соединение без условия)
WHERE emp.DepartmentID=3 -- условие фильтрации данных
Через WHERE-условие он примет следующую форму:
SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM
Employees emp,
Departments dep
WHERE emp.DepartmentID=3 -- условие фильтрации данных
Т.е. в этом случае мы просто не указали условие соединения таблиц Employees и Departments. Чем плох этот запрос? Представьте, что кто-то другой смотрит на ваш запрос и думает «кажется тот, кто писал запрос забыл здесь дописать условие (emp.DepartmentID=dep.ID)» и с радостью, что обнаружил косяк, дописывает это условие. В результате чего задуманное вами может сломаться, т.к. вы подразумевали CROSS JOIN. Так что, если вы делаете декартово соединение, то лучше явно укажите, что это именно оно, используя конструкцию CROSS JOIN.
Для оптимизатора запроса может быть и без разницы как вы реализуете соединение (при помощи WHERE или JOIN), он их может выполнить абсолютно одинаково. Но из соображения понимаемости кода, я бы рекомендовал в современных СУБД стараться никогда не делать соединение таблиц при помощи WHERE-условия. Использовать WHERE-условия для соединения, в том случае, если в СУБД реализованы конструкции JOIN, я бы назвал сейчас моветоном. WHERE-условия служат для фильтрации набора, и не нужно перемешивать условия служащие для соединения, с условиями отвечающими за фильтрацию. Но если вы пришли к выводу, что без реализации соединения через WHERE не обойтись, то конечно приоритет за решеной задачей и «к черту все устои».
UNION-объединения – операции вертикального объединения результатов запросов
Я специально использую словосочетания горизонтальное соединение и вертикальное объединение, т.к. заметил, что новички часто недопонимают и путают суть этих операций.
Давайте первым делом вспомним как мы делали первую версию отчета для директора:
SELECT
'Администрация' Info,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=1 -- данные по Администрации
SELECT
'Бухгалтерия' Info,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=2 -- данные по Бухгалтерии
SELECT
'ИТ' Info,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=3 -- данные по ИТ отделу
SELECT
'Прочие' Info,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID IS NULL -- и еще не забываем данные по внештатникам
Так вот, если бы мы не знали, что существует операция группировки, но знали бы, что существует операция объединения результатов запроса при помощи UNION ALL, то мы могли бы склеить все эти запросы следующим образом:
SELECT
'Администрация' Info,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=1 -- данные по Администрации
UNION ALL
SELECT
'Бухгалтерия' Info,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=2 -- данные по Бухгалтерии
UNION ALL
SELECT
'ИТ' Info,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=3 -- данные по ИТ отделу
UNION ALL
SELECT
'Прочие' Info,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID IS NULL -- и еще не забываем данные по внештатникам
Т.е. UNION ALL позволяет склеить результаты, полученные разными запросами в один общий результат.
Соответственно количество колонок в каждом запросе должно быть одинаковым, а также должны быть совместимыми и типы этих колонок, т.е. строка под строкой, число под числом, дата под датой и т.п.
Немного теории
В MS SQL реализованы следующие виды вертикального объединения:
Операция | Описание |
---|---|
UNION ALL | В результат включаются все строки из обоих наборов. (A+B) |
UNION | В результат включаются только уникальные строки двух наборов. DISTINCT(A+B) |
EXCEPT | В результат попадают уникальные строки верхнего набора, которые отсутствуют в нижнем наборе. Разница 2-х множеств. DISTINCT(A-B) |
INTERSECT | В результат включаются только уникальные строки, присутствующие в обоих наборах. Пересечение 2-х множеств. DISTINCT(A&B) |
Все это проще понять на наглядном примере.
Создадим 2 таблицы и наполним их данными:
CREATE TABLE TopTable(
T1 int,
T2 varchar(10)
)
GO
CREATE TABLE BottomTable(
B1 int,
B2 varchar(10)
)
GO
INSERT TopTable(T1,T2)VALUES
(1,'Text 1'),
(1,'Text 1'),
(2,'Text 2'),
(3,'Text 3'),
(4,'Text 4'),
(5,'Text 5')
INSERT BottomTable(B1,B2)VALUES
(2,'Text 2'),
(3,'Text 3'),
(6,'Text 6'),
(6,'Text 6')
Посмотрим на содержимое:
SELECT *
FROM TopTable
T1 | T2 |
---|---|
1 | Text 1 |
1 | Text 1 |
2 | Text 2 |
3 | Text 3 |
4 | Text 4 |
5 | Text 5 |
SELECT *
FROM BottomTable
B1 | B2 |
---|---|
2 | Text 2 |
3 | Text 3 |
6 | Text 6 |
6 | Text 6 |
UNION ALL
SELECT T1 x,T2 y
FROM TopTable
UNION ALL
SELECT B1,B2
FROM BottomTable
UNION
SELECT T1 x,T2 y
FROM TopTable
UNION
SELECT B1,B2
FROM BottomTable
По сути UNION можно представить, как UNION ALL, к которому применена операция DISTINCT:
EXCEPT
SELECT T1 x,T2 y
FROM TopTable
EXCEPT
SELECT B1,B2
FROM BottomTable
INTERSECT
SELECT T1 x,T2 y
FROM TopTable
INTERSECT
SELECT B1,B2
FROM BottomTable
Завершаем разговор о UNION-соединениях
Вот в принципе и все, что касается вертикальных объединений, это намного проще, чем JOIN-соединения.
Чаще всего в моей в практике находит применение UNION ALL, но и другие виды вертикальных объединений находят свое применение.
При нескольких операциях вертикально объединения, не гарантируется, что они будут выполняться последовательно сверху-вниз. Создадим еще одну таблицу и рассмотрим это на примере:
CREATE TABLE NextTable(
N1 int,
N2 varchar(10)
)
GO
INSERT NextTable(N1,N2)VALUES
(1,'Text 1'),
(4,'Text 4'),
(6,'Text 6')
Например, если мы напишем просто:
SELECT T1 x,T2 y
FROM TopTable
EXCEPT
SELECT B1,B2
FROM BottomTable
INTERSECT
SELECT N1,N2
FROM NextTable
То мы получим:
x | y |
---|---|
1 | Text 1 |
2 | Text 2 |
3 | Text 3 |
4 | Text 4 |
5 | Text 5 |
Т.е. получается сначала выполнился INTERSECT, а после EXCEPT. Хотя логически будто должно было быть наоборот, т.е. идти сверху-вниз.
Я редко использую эти операции объединений, а тем более в таком виде, поэтому, чтобы не думать не гадать, в какой очередности он выполняет объединения, можно просто при помощи скобок явно указать последовательность объединений, давайте скажем, что сначала нужно сделать EXCEPT, а потом INTERSECT:
(
SELECT T1 x,T2 y
FROM TopTable
EXCEPT
SELECT B1,B2
FROM BottomTable
)
INTERSECT
SELECT N1,N2
FROM NextTable
Вот теперь я получил то, что и хотел.
Я не знаю работает ли такой синтаксис в других СУБД, но если что используйте подзапрос:
SELECT x,y
FROM
(
SELECT T1 x,T2 y
FROM TopTable
EXCEPT
SELECT B1,B2
FROM BottomTable
) q
INTERSECT
SELECT N1,N2
FROM NextTable
При использовании ORDER BY сортировка применяется к окончательному набору:
SELECT T1 x,T2 y
FROM TopTable
UNION ALL
SELECT B1,B2
FROM BottomTable
UNION ALL
SELECT B1,B2
FROM BottomTable
ORDER BY x DESC
Для задания сортировки здесь удобней использовать псевдоним колонки, заданный в первом запросе.
Самое главное про UNION-объединения я вроде написал, если что поиграйте с UNION-объединениями самостоятельно.
Примечание. В СУБД Oracle тоже есть такие же виды соединения, разница только в операции EXCEPT, там она называется MINUS.
Использование подзапросов
Подзапросы я оставил на последнюю очередь, т.к. прежде чем их использовать нужно научиться правильно строить запросы. К тому же в некоторых случаях можно вообще избежать использования подзапросов и обойтись базовыми конструкциями.
Косвенно мы уже использовали подзапросы в блоке FROM. Там результат, возвращаемый подзапросом по сути играет роль новой таблицы. Думаю, большого смысла останавливаться здесь нет смысла. Просто рассмотрим абстрактный пример с объединением 2-х подзапросов:
SELECT q1.x1,q1.y1,q2.x2,q2.y2
FROM
(
SELECT T1 x1,T2 y1
FROM TopTable
EXCEPT
SELECT B1,B2
FROM BottomTable
) q1
JOIN
(
SELECT T1 x2,T2 y2
FROM TopTable
EXCEPT
SELECT N1,N2
FROM NextTable
) q2
ON q1.x1=q2.x2
Если не понятно, сразу, то разбирайте такие запросы по частям. Т.е. сначала посмотрите, что возвращает первый подзапрос «q1», потом, что возвращает второй подзапрос «q2», а затем выполните операцию JOIN над результатами подзапросов «q1» и «q2».
Конструкция WITH
Это достаточно полезная конструкция особенно в случае работы с большими подзапросами.
Сравним:
SELECT q1.x1,q1.y1,q2.x2,q2.y2
FROM
(
SELECT T1 x1,T2 y1
FROM TopTable
EXCEPT
SELECT B1,B2
FROM BottomTable
) q1
JOIN
(
SELECT T1 x2,T2 y2
FROM TopTable
EXCEPT
SELECT N1,N2
FROM NextTable
) q2
ON q1.x1=q2.x2
То же самое написанное при помощи WITH:
WITH q1 AS(
SELECT T1 x1,T2 y1
FROM TopTable
EXCEPT
SELECT B1,B2
FROM BottomTable
),
q2 AS(
SELECT T1 x2,T2 y2
FROM TopTable
EXCEPT
SELECT N1,N2
FROM NextTable
)
-- основной запрос становится более прозрачным
SELECT q1.x1,q1.y1,q2.x2,q2.y2
FROM q1
JOIN q2 ON q1.x1=q2.x2
Как видим большие подзапросы вынесены и поименованы в блоке WITH, что дало возможность разгрузить текст основного запроса и сделать его понятным.
Вспомним так же пример из предыдущей части, где использовалось представление ViewEmployeesInfo:
CREATE VIEW ViewEmployeesInfo
AS
SELECT
emp.*, -- вернуть все поля таблицы Employees
dep.Name DepartmentName, -- к этим полям добавить поле Name из таблицы Departments
pos.Name PositionName -- и еще добавить поле Name из таблицы Positions
FROM Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
LEFT JOIN Positions pos ON emp.PositionID=pos.ID
И запрос, который использовал данное представление:
SELECT
DepartmentName,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount,
AVG(Salary) SalaryAvg
FROM ViewEmployeesInfo emp
GROUP BY DepartmentID,DepartmentName
ORDER BY DepartmentName
По сути WITH дает нам возможность разместить текст из представления непосредственно в запросе, т.е. смысл один и тот же:
WITH cteEmployeesInfo AS(
SELECT
emp.*, -- вернуть все поля таблицы Employees
dep.Name DepartmentName, -- к этим полям добавить поле Name из таблицы Departments
pos.Name PositionName -- и еще добавить поле Name из таблицы Positions
FROM Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
LEFT JOIN Positions pos ON emp.PositionID=pos.ID
)
SELECT
DepartmentName,
COUNT(DISTINCT PositionID) PositionCount,
COUNT(*) EmplCount,
SUM(Salary) SalaryAmount,
AVG(Salary) SalaryAvg
FROM cteEmployeesInfo emp
GROUP BY DepartmentID,DepartmentName
ORDER BY DepartmentName
Только в случае созданного представления мы можем использовать его из разных запросов, т.к. представление создается на уровне БД. Тогда как подзапрос оформленный в блоке WITH виден только в рамках этого запроса.
Использование WITH по-другому называет CTE-выражениями:
Общие табличные выражения (CTE — Common Table Expressions) позволяют существенно уменьшить объем кода, если многократно приходится обращаться к одним и тем же запросам. CTE играет роль представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы.У CTE есть еще одно важное назначение, с его помощью можно написать рекурсивный запрос.
Приведу только небольшой пример рекурсивного запроса. Отобразим сотрудников с учетом их подчинения другому сотруднику (если помните, у нас в таблице Employees ключ, ссылающийся на эту же таблицу).
WITH cteEmpl AS(
SELECT ID,CAST(Name AS nvarchar(300)) Name,1 EmpLevel
FROM Employees
WHERE ManagerID IS NULL -- все сотрудники у которых нет вышестоящего
UNION ALL
SELECT emp.ID,CAST(SPACE(cte.EmpLevel*5)+emp.Name AS nvarchar(300)),cte.EmpLevel+1
FROM Employees emp
JOIN cteEmpl cte ON emp.ManagerID=cte.ID
)
SELECT *
FROM cteEmpl
ID | Name | EmpLevel |
---|---|---|
1000 | Иванов И.И. | 1 |
1002 | _____Сидоров С.С. | 2 |
1003 | _____Андреев А.А. | 2 |
1005 | _____Александров А.А. | 2 |
1001 | __________Петров П.П. | 3 |
1004 | __________Николаев Н.Н. | 3 |
Для наглядности пробелы заменены знаками подчеркивания.
Рассматривать, как строятся рекурсивные запросы, в рамках данного учебника я не буду. Я считаю, это достаточно специфичная тема для начинающих, и она пока совсем ни к чему им. Прежде чем приступать к изучению рекурсивных запросов нужно уверено научиться пользоваться всеми осн
Как я могу сделать инструкцию UPDATE с JOIN в SQL?
вы получите лучшую производительность, если забудете предложение where и поместите все условия в выражение ON.
Я думаю, это потому, что запрос сначала должен присоединиться к таблицам, затем запускает предложение where на этом, поэтому, если вы можете уменьшить то, что требуется для присоединения, то это быстрый способ получить результаты/сделать udpate.
пример
сценарий
у вас есть таблица пользователей. Они могут войти, используя свое имя пользователя или email или account_number. Эти учетные записи могут быть активными (1) или неактивными (0). Эта таблица имеет 50000 строк
затем у вас есть таблица пользователей, чтобы отключить на одном дыхании, потому что вы узнаете, что они все сделали что-то плохое. Однако в этой таблице есть один столбец с именами пользователей, электронными письмами и номерами счетов. Он также имеет индикатор «has_run», который должен быть установлен в 1 (true), когда он был запущен
запрос
UPDATE users User
INNER JOIN
blacklist_users BlacklistUser
ON
(
User.username = BlacklistUser.account_ref
OR
User.email = BlacklistedUser.account_ref
OR
User.phone_number = BlacklistUser.account_ref
AND
User.is_active = 1
AND
BlacklistUser.has_run = 0
)
SET
User.is_active = 0,
BlacklistUser.has_run = 1;
мышление
Если бы нам пришлось присоединиться только в условиях OR необходимо будет проверить каждую строку 4 раза, чтобы увидеть, следует ли ей присоединиться, и потенциально вернуть намного больше строк. Однако, давая ему больше условий, он может «пропустить» много строк, если они не соответствуют всем условиям при присоединении.
бонус
Это более читабельным. Все условия находятся в одном месте, а строки для обновления-в одном месте
SQL: Как понять, когда надо писать условие в JOIN-условие, а когда в WHERE
Это в общем-то важно только для LEFT JOIN, для INNER JOIN тут могут быть только теоретические различия в производительности, хотя думаю, что парсер их решит.
Допустим у нас есть сотрудники, оборудование и таблица связи между ними.
CREATE TABLE Employee (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`login` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY ( `login` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `Equipment` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`inv_number` VARCHAR(255) NOT NULL,
`employee_equipment_id` INT(10) UNSIGNED,
PRIMARY KEY (`id`),
UNIQUE KEY ( `inv_number` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS EmployeeEquipment (
`employee_id` INT(10) UNSIGNED NOT NULL,
`equipment_id` INT(10) UNSIGNED NOT NULL,
`handed_at` DATETIME NOT NULL,
`returned_at` DATETIME,
PRIMARY KEY (`employee_id`, `equipment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| CREATE TABLE Employee ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `login` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY ( `login` ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `Equipment` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `inv_number` VARCHAR(255) NOT NULL, `employee_equipment_id` INT(10) UNSIGNED, PRIMARY KEY (`id`), UNIQUE KEY ( `inv_number` ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS EmployeeEquipment ( `employee_id` INT(10) UNSIGNED NOT NULL, `equipment_id` INT(10) UNSIGNED NOT NULL, `handed_at` DATETIME NOT NULL, `returned_at` DATETIME, PRIMARY KEY (`employee_id`, `equipment_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Для простоты убрал внешние ключи и лишние столбцы.
Если нам нужно вывести оборудование, которое ещё ни разу никому не выдавалось, то нам нужен запрос:
SELECT
eq.*,
e.id AS employee_id,
e.name AS employee_name,
ee.handed_at
FROM Equipment as eq
LEFT JOIN EmployeeEquipment AS ee
ON ee.equipment_id = eq.id
LEFT JOIN Employee AS e
ON e.id = ee.employee_id
WHERE ee.handed_at IS NULL;
| SELECT eq.*, e.id AS employee_id, e.name AS employee_name, ee.handed_at FROM Equipment as eq LEFT JOIN EmployeeEquipment AS ee ON ee.equipment_id = eq.id LEFT JOIN Employee AS e ON e.id = ee.employee_id WHERE ee.handed_at IS NULL; |
Если нам нужен полный список оборудования, но при этом для оборудования «на руках» нужно выводить дату, когда оно было выдано, и сотрудника, которому оно было выдано:
SELECT
eq.*,
e.id AS employee_id,
e.name AS employee_name,
ee.handed_at
FROM Equipment as eq
LEFT JOIN EmployeeEquipment AS ee
ON ee.equipment_id = eq.id AND ee.returned_at IS NULL
LEFT JOIN Employee AS e
ON e.id = ee.employee_id;
| SELECT eq.*, e.id AS employee_id, e.name AS employee_name, ee.handed_at FROM Equipment as eq LEFT JOIN EmployeeEquipment AS ee ON ee.equipment_id = eq.id AND ee.returned_at IS NULL LEFT JOIN Employee AS e ON e.id = ee.employee_id; |
Хитрость второго запроса — в том, что он учитывает ситуации, когда оборудование было выдано сотруднику, а потом его возвратили обратно.
То есть первый запрос ограничивает всю выборку (поэтому условие пишется в WHERE), а второй запрос ограничивает «участие LEFTJOINed таблицы» в выборке, поэтому условие пишется в условии ON.
Выполнение объединений с помощью SQL Access
-
- Чтение занимает 5 мин
В этой статье
В реляционной системе баз данных, такой как доступ, часто требуется извлекать данные из нескольких таблиц за раз.In a relational database system like Access, you often need to extract information from more than one table at a time. Для этого можно использовать инструкцию SQL Join , которая позволяет извлекать записи из таблиц с определенными связями независимо от того, являются ли они одними, одними и многими.This can be accomplished by using an SQL JOIN statement, which enables you to retrieve records from tables that have defined relationships, whether they are one-to-one, one-to-many, or many-to-many.
ВНУТРЕННИЕ соединенияINNER JOINs
Внутреннее соединение, которое также называется эквивалентным объединением, является наиболее часто используемым типом объединения.The INNER JOIN, also known as an equi-join, is the most commonly used type of join. Это соединение используется для получения строк из двух или более таблиц, сопоставляя значение поля, которое является общим для таблиц.This join is used to retrieve rows from two or more tables by matching a field value that is common between the tables. Объединяемые поля должны иметь похожие типы данных, и вы не можете присоединиться к типам данных мемо или OLEOBJECT.The fields you join on must have similar data types, and you cannot join on MEMO or OLEOBJECT data types.
Чтобы создать оператор inner join , используйте ключевые слова inner join в предложении from инструкции SELECT .To build an INNER JOIN statement, use the INNER JOIN keywords in the FROM clause of a SELECT statement.
В этом примере используется внутреннее соединение для создания набора результатов всех клиентов, у которых есть счета, а также даты и суммы этих счетов.This example uses the INNER JOIN to build a result set of all customers who have invoices, in addition to the dates and amounts of those invoices.
SELECT [Last Name], InvoiceDate, Amount
FROM tblCustomers INNER JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID
ORDER BY InvoiceDate
Обратите внимание на то, что имена таблиц делятся на ключевые слова inner join и что реляционное сравнение выполняется после ключевого слова On .Be aware that the table names are divided by the INNER JOIN keywords and that the relational comparison is after the ON keyword. Для реляционных сравнений можно также использовать операторы <, >, <=, >= или <> , а также использовать ключевое слово between .For the relational comparisons, you can also use the <, >, <=, >=, or <> operators, and you can also use the BETWEEN keyword. Кроме того, обратите внимание, что поля ID из обеих таблиц используются только в реляционном сравнении; они не являются частью результирующего набора.Also note that the ID fields from both tables are used only in the relational comparison; they are not part of the final result set.
Для дальнейшей квалификации оператора SELECT можно использовать предложение WHERE после сравнения JOIN в предложении On .To further qualify the SELECT statement, you can use a WHERE clause after the join comparison in the ON clause.
В приведенном ниже примере набор результатов сужается, чтобы включить только счета, выпущенные после 1 января 1998 г.The following example narrows the result set to include only invoices dated after January 1, 1998.
SELECT [Last Name], InvoiceDate, Amount
FROM tblCustomers INNER JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID
WHERE tblInvoices.InvoiceDate > #01/01/1998#
ORDER BY InvoiceDate
Если необходимо присоединиться к нескольким таблицам, можно вложить условия inner join .When you must join more than one table, you can nest the INNER JOIN clauses. Следующий пример строится на предыдущем операторе SELECT для создания набора результатов, но также включает город и состояние каждого клиента, добавляя внутреннее соединение для таблицы тблшиппинг.The following example builds on a previous SELECT statement to create the result set, but also includes the city and state of each customer by adding the INNER JOIN for the tblShipping table.
SELECT [Last Name], InvoiceDate, Amount, City, State
FROM (tblCustomers INNER JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID)
INNER JOIN tblShipping
ON tblCustomers.CustomerID=tblShipping.CustomerID
ORDER BY InvoiceDate
Обратите внимание, что первое предложение Join заключено в круглые скобки, чтобы оно было логически отделено от второго предложения Join .Be aware that the first JOIN clause is enclosed in parentheses to keep it logically separated from the second JOIN clause. Кроме того, можно присоединить таблицу к самой себе с помощью псевдонима для имени второй таблицы в предложении from .It is also possible to join a table to itself by using an alias for the second table name in the FROM clause. Предположим, вы хотите найти все записи клиентов, содержащие повторяющиеся фамилии.Suppose that you want to find all customer records that have duplicate last names. Это можно сделать, создав псевдоним «A» для второй таблицы и проверив имена, которые отличаются.You can do this by creating the alias «A» for the second table and checking for first names that are different.
SELECT tblCustomers.[Last Name],
tblCustomers.[First Name]
FROM tblCustomers INNER JOIN tblCustomers AS A
ON tblCustomers.[Last Name]=A.[Last Name]
WHERE tblCustomers.[First Name]<>A.[First Name]
ORDER BY tblCustomers.[Last Name]
ВНЕШНИЕ соединенияOUTER JOINs
Внешнее объединение используется для получения записей из нескольких таблиц, сохраняя записи из одной из таблиц, даже если в другой таблице нет соответствующей записи.An OUTER JOIN is used to retrieve records from multiple tables while preserving records from one of the tables, even if there is no matching record in the other table. Существует два типа внешних объединений , поддерживаемых ядром баз данных Access: левые внешние соединения и правая внешние соединения.There are two types of OUTER JOINs that the Access database engine supports: LEFT OUTER JOINs and RIGHT OUTER JOINs.
Вспомните две таблицы, расположенные рядом друг с другом, таблица слева и таблица справа.Think of two tables that are beside each other, a table on the left and a table on the right. Левое внешнее соединение выбирает все строки в правой таблице, которые совпадают с условиями реляционного сравнения, а также выбирает все строки из левой таблицы, даже если в правой таблице нет соответствия.The LEFT OUTER JOIN selects all rows in the right table that match the relational comparison criteria, and also selects all rows from the left table, even if no match exists in the right table. Правое внешнее соединение просто является обратным по отношению к левому внешнему соединению; Вместо этого сохраняются все строки в правой таблице.The RIGHT OUTER JOIN is simply the reverse of the LEFT OUTER JOIN; all rows in the right table are preserved instead.
Например, предположим, что вы хотите определить общую сумму выставленных счетов для каждого клиента, но если у клиента нет накладных, вы хотите отобразить его, отображая слово «нет».As an example, suppose that you want to determine the total amount invoiced to each customer, but if a customer has no invoices, you want to show it by displaying the word «NONE.»
SELECT [Last Name] & ', ' & [First Name] AS Name,
IIF(Sum(Amount) IS NULL,'NONE',Sum(Amount)) AS Total
FROM tblCustomers LEFT OUTER JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID
GROUP BY [Last Name] & ', ' & [First Name]
В предыдущей инструкции SQL возникают некоторые события.Several things occur in the previous SQL statement. Первый — использование оператора сцепления строк «&».The first is the use of the string concatenation operator «&». Этот оператор позволяет объединить два поля вместе с одной строкой.This operator allows you to join two or more fields together as one string. Второй оператор — непосредственный оператор if (IIf), который проверяет, имеет ли значение значение null.The second is the immediate if (IIf) statement, which checks to see if the total is null. Если это так, оператор возвращает слово «NONE».If it is, the statement returns the word «NONE.» Если параметр Total имеет значение, отличное от NULL, возвращается значение.If the total is not null, the value is returned. Последним является предложение OUTER JOIN .The final thing is the OUTER JOIN clause. Использование левого внешнего соединения сохраняет строки в левой таблице, чтобы видеть всех клиентов, даже тех, у которых нет счетов.Using the LEFT OUTER JOIN preserves the rows in the left table so that you see all customers, even those who do not have invoices.
Внешние соединения могут быть вложены в внутренние соединения при объединении с несколькими таблицами, но внутренние соединения не могут быть вложены в внешние соединения.OUTER JOINs can be nested inside INNER JOINs in a multi-table join, but INNER JOINs cannot be nested inside OUTER JOINs.
Декартово произведениеThe Cartesian product
Термин, с которым часто приходится обсуждать соединения, это декартово произведение.A term that often comes up when discussing joins is the Cartesian product. Декартово-продукт определяется как «все возможные сочетания всех строк во всех таблицах».A Cartesian product is defined as «all possible combinations of all rows in all tables.» Например, если вы присоединяетесь к двум таблицам без какого бы то ни было какого типа квалификации или присоединения, вы получите декартово произведение.For example, if you were to join two tables without any kind of qualification or join type, you would get a Cartesian product.
SELECT *
FROM tblCustomers, tblInvoices
Это не хорошая вещь, особенно с таблицами, содержащими сотни или тысячи строк.This is not a good thing, especially with tables that contain hundreds or thousands of rows. Не создавайте декартово продукты, всегда выполняя присоединение.You should avoid creating Cartesian products by always qualifying your joins.
Оператор UNIONThe UNION operator
Несмотря на то, что оператор Union , также называемый запросом на объединение, не является объединением, он включается здесь, так как он включает в себя объединение данных из нескольких источников данных в один набор результатов, что аналогично некоторым типам соединений.Although the UNION operator, also known as a union query, is not technically a join, it is included here because it does involve combining data from multiple sources of data into one result set, which is similar to some types of joins. Оператор Union используется для объединения данных в таблицах, инструкциях SELECT или запросах, при этом не удаляются повторяющиеся строки.The UNION operator is used to splice together data from tables, SELECT statements, or queries, while leaving out any duplicate rows. Оба источника данных должны иметь одинаковое число полей, но эти поля не обязательно должны иметь одинаковый тип данных.Both data sources must have the same number of fields, but the fields do not have to be the same data type. Предположим, у вас есть таблица Employees, имеющая ту же структуру, что и таблица Customers, и вы хотите создать список имен и адресов электронной почты, объединив обе таблицы.Suppose that you have an Employees table that has the same structure as the Customers table, and you want to build a list of names and email addresses by combining both tables.
SELECT [Last Name], [First Name], Email
FROM tblCustomers
UNION
SELECT [Last Name], [First Name], Email
FROM tblEmployees
Чтобы получить все поля из обеих таблиц, можно использовать ключевое слово Table , как показано ниже.To retrieve all fields from both tables, you could use the TABLE keyword, like this.
TABLE tblCustomers
UNION
TABLE tblEmployees
Оператор Union не будет отображать записи, которые являются точными дубликатами в обеих таблицах, но их можно переопределить с помощью предиката ALL после ключевого слова Union , как показано ниже:The UNION operator will not display any records that are exact duplicates in both tables, but this can be overridden by using the ALL predicate after the UNION keyword, like this:
SELECT [Last Name], [First Name], Email
FROM tblCustomers
UNION ALL
SELECT [Last Name], [First Name], Email
FROM tblEmployees
Оператор TRANSFORMThe TRANSFORM statement
Несмотря на то, что инструкция Transform , также называемая перекрестным запросом, также не считается присоединением, она включается здесь, так как она включает в себя объединение данных из нескольких источников данных в один набор результатов, что аналогично некоторым типам соединений.Although the TRANSFORM statement, also known as a crosstab query, is also not technically considered a join, it is included here because it does involve combining data from multiple sources of data into one result set, which is similar to some types of joins.
Оператор Transform используется для вычисления суммы, среднего, количества или другого типа итогового значения для записей.A TRANSFORM statement is used to calculate a sum, average, count, or other type of aggregate total on records. Затем сведения отображаются в сетке или электронном формате с данными, сгруппированными по вертикали (строкам) и горизонтали (столбцам).It then displays the information in a grid or spreadsheet format with data grouped both vertically (rows) and horizontally (columns). Ниже приведена общая форма для оператора Transform .The general form for a TRANSFORM statement is the following.
TRANSFORM aggregating function
SELECT statement
PIVOT column heading field
Примером может служить создание таблицы, отображающей итоговые значения по счетам для каждого клиента на основе года.An example scenario could be if you want to build a datasheet that displays the invoice totals for each customer on a year-by-year basis. В качестве вертикальных заголовков будут использоваться имена клиентов, а для горизонтальных заголовков — годы.The vertical headings will be the customer names, and the horizontal headings will be the years. Вы можете изменить предыдущую инструкцию SQL, чтобы она соответствовала оператору Transform.You can modify a previous SQL statement to fit the transform statement.
TRANSFORM
IIF(Sum([Amount]) IS NULL,'NONE',Sum([Amount]))
AS Total
SELECT [Last Name] & ', ' & [First Name] AS Name
FROM tblCustomers LEFT JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID
GROUP BY [Last Name] & ', ' & [First Name]
PIVOT Format(InvoiceDate, 'yyyy')
IN ('1996','1997','1998','1999','2000')
Обратите внимание, что функция статистической обработки является функцией Sum , вертикальными заголовками являются предложение Group By оператора SELECT , а горизонтальные заголовки определяются полем, указанным после ключевого слова Pivot .Be aware that the aggregating function is the Sum function, the vertical headings are in the GROUP BY clause of the SELECT statement, and the horizontal headings are determined by the field listed after the PIVOT keyword.
Поддержка и обратная связьSupport and feedback
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи?Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
SQL optimization. Join против In и Exists. Что использовать?
«Раньше было проще» — Подумал я, садясь за оптимизацию очередного запроса в SQL management studio. Когда я писал под MySQL, реально все было проще — или работает, или нет. Или тормозит или нет. Explain решал все мои проблемы, больше ничего не требовалось. Сейчас у меня есть мощная среда разработки, отладки и оптимизации запросов и процедур/функций, и все это нагромождение создает по-моему только больше проблем. А все почему? Потому что встроенный оптимизатор запросов — зло. Если в MySQL и PostgreSQL я напишу
select * from a, b, c where a.id = b.id, b.id = c.id
и в каждой из табличек будет хотя бы по 5к строк — все зависнет. И слава богу! Потому что иначе в разработчике, в лучшем случае, вырабатывается ленность писать правильно, а в худшем он вообще не понимает что делает! Ведь этот же запрос в MSSQL пройдет аналогично
select * from a join b on a.id = b.id join c on b.id = c.id
Встроенный оптимизатор причешет быдлозапрос и все будет окей.
Он так же сам решит, что лучше делать — exist или join и еще много чего. И все будет работать максимально оптимально.
Только есть одно НО. В один прекрасный момент оптимизатор споткнется о сложный запрос и спасует, и тогда вы получите большущую проблему. И получите вы ее, возможно, не сразу, а когда вес таблиц достигнет критической массы.
Так вот к сути статьи. exists и in — очень тяжелые операции. Фактически это отдельный подзапрос для каждой строчки результата. А если еще и присутствует вложенность, то это вообще туши свет. Все будет окей, когда возвращается 1, 10, 50 строк. Вы не почувствуете разницы, а возможно join будет даже медленнее. Но когда вытаскивается 500 — начнутся проблемы. 500 подзапросов в рамках одного запроса — это серьезно.
Пусть с точки зрения человеческого понимания in и exists лучше, но с точки зрения временных затрат для запросов, возвращающих 50+ строк — они не допустимы.
Нужно оговориться, что естественно, если где-то убывает — где-то должно прибывать. Да, join более ресурсоемок по памяти, ведь держать единовременно всю таблицу значений и оперировать ею — накладнее, чем дергать подзапросы для каждой строки, быстро освобождая память. Нужно смотреть конкретно по запросу и замерять — критично ли будет использование лишней памяти в угоду времени или нет.
Приведу примеры полных аналогий. Вообще говоря, я не встречал еще запросов такой степени сложности, которые не могли бы быть раскручены в каскад join’ов. Пусть на это уйдет день, но все можно раскрыть.
select * from a where a.id in (select id from b) select * from a where exists (select top 1 1 from b where b.id = a.id) select * from a join b on a.id = b.id
select * from a where a.id not in (select id from b) select * from a where not exists (select top 1 1 from b where b.id = a.id) select * from a left join b on a.id = b.id where b.id is null
Повторюсь — данные примеры MSSQL оптимизатор оптимизирует под максимальную производительность и на таких простейших запросах тупняков не будет никогда.
Рассмотрим теперь пример реального запроса, который пришлось переписывать из-за того что на некоторых выборках он просто намертво зависал (структура очень упрощена и понятия заменены, не нужно пугаться некоей не оптимальности структуры бд).
Нужно вытащить все дубликаты «продуктов» в разных аккаунтах, ориентируясь на параметры продукта, его группы, и группы-родителя, если таковая есть.
select d.PRODUCT_ID from PRODUCT s, PRODUCT_GROUP sg left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID) where s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID and d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT and dg.PRODUCT_GROUP_IS_TMPL=0 and ( ( sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null ) or exists ( select 1 from PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 where sd.M_PG_DEPENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID and dd.M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID and sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC and sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME and ) )
Так вот это тот случай, когда оптимизатор спасовал. И для каждой строчки выполнялся тяжеленный exists, что убивало базу.
select d.PRODUCT_ID from PRODUCT s join PRODUCT d on s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT join PRODUCT_GROUP sg on s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID join PRODUCT_GROUP dg on d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC left join M_PG_DEPENDENCY sd on sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID left join M_PG_DEPENDENCY dd on dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID left join PRODUCT_GROUP sgp on sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID left join PRODUCT_GROUP dgp on dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID and sgp.PRODUCT_GROUP_NAME = dgp.PRODUCT_GROUP_NAME and isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull(dgp.PRODUCT_GROUP_IS_TMPL, 0) where ( sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null ) or ( sgp.PRODUCT_GROUP_NAME is not null and dgp.PRODUCT_GROUP_NAME is not null ) go
После данных преобразований производительность вьюхи увеличилась экспоненциально количеству найденных продуктов. Вернее сказать, время поиска оставалось практически независимым от числа совпадений и было всегда очень маленьким. Как и должно быть.
Это наглядный пример того, как доверие MSSQL оптимизатору может сыграть злую шутку. Не доверяйте ему, не ленитесь, join’те ручками, каждый раз думайте что лучше в данной ситуации — exists, in или join.
SQL JOIN, JOIN Syntax, JOIN Differences, 3 таблицы — с примерами
Как получить данные из нескольких таблиц?
SQL JOIN объединяет записи из двух таблиц.
JOIN находит связанные значения столбцов в двух таблицах.
Запрос может содержать ноль, одну или несколько операций JOIN.
INNER JOIN — это то же самое, что JOIN; ключевое слово INNER необязательно.
Четыре различных типа JOIN
- (INNER) JOIN: выберите записи, значения которых совпадают в обеих таблицах.
- ПОЛНОЕ (ВНЕШНЕЕ) СОЕДИНЕНИЕ: выбирает все записи, соответствующие либо левой, либо правой записям таблицы.
- LEFT (OUTER) JOIN: выберите записи из первой (самой левой) таблицы с соответствующими записями правой таблицы.
- RIGHT (OUTER) JOIN: выберите записи из второй (самой правой) таблицы с соответствующими записями левой таблицы.
Примечание. Все ключевые слова INNER и OUTER необязательны.
Подробная информация о различных соединениях доступна на следующих страницах руководства.
Синтаксис SQL JOIN
Общий синтаксис
ВЫБЕРИТЕ имена столбцов FROM table-name1 ПРИСОЕДИНЯТЬСЯ к table-name2 ON имя-столбца1 = имя-столбца2 ГДЕ условие
Общий синтаксис INNER:
ВЫБЕРИТЕ имена столбцов ИЗ имя-таблицы1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ имя-таблицы2 ON имя-столбца1 = имя-столбца2 ГДЕ условие
Примечание. Ключевое слово INNER является необязательным: оно используется по умолчанию, а также является наиболее часто используемой операцией JOIN.
КЛИЕНТ |
---|
Идентификатор |
Имя |
Фамилия |
Город |
Страна |
Телефон |
ЗАКАЗ | Идентификатор |
---|
Номер заказа |
CustomerId |
TotalAmount |
Примеры SQL JOIN
Проблема: Список всех заказов с информацией о клиенте
ВЫБЕРИТЕ OrderNumber, TotalAmount, FirstName, LastName, City, Country ОТ [Заказ] ПРИСОЕДИНЯЙТЕСЬ к клиенту На заказ].CustomerId = Customer.Id
В этом примере может оказаться полезным использование псевдонимов таблицы для [Заказ] и клиента.
Результат: 830 записей.
Номер для заказа | Всего | Имя | Фамилия | Город | Страна |
---|---|---|---|---|---|
542378 | 440.00 | Пол | Анрио | Реймс | Франция |
542379 | 1863,40 | Карин | Джозефс | Мюнстер | Германия |
542380 | 1813,00 | Марио | Понты | Рио-де-Жанейро | Бразилия |
542381 | 670.80 | Мэри | Савелей | Лион | Франция |
542382 | 3730,00 | Паскаль | Картрейн | Шарлеруа | Бельгия |
542383 | 1444,80 | Марио | Понты | Рио-де-Жанейро | Бразилия |
542384 | 625.20 | Ян | Ван | Берн | Швейцария |
ПРОДУКТ |
---|
Идентификатор |
Название продукта |
Идентификатор поставщика |
Цена единицы |
Упаковка |
Снята с производства |
ProductId |
UnitPrice |
Количество |
ORDER |
---|
Id |
OrderDate |
OrderNumber |
Задача: Список всех заказов
с названиями продуктов,
количества и цены
ВЫБЕРИТЕ O.OrderNumber, CONVERT (date, O.OrderDate) AS Date, P.ProductName, I.Quantity, I.UnitPrice ОТ [Заказ] O JOIN OrderItem I ON O.Id = I.OrderId ПРИСОЕДИНЯЙТЕСЬ к продукту P ON P.Id = I.ProductId ЗАКАЗ ПО O.O.OrderNumber
Результат: 2155 записей
Номер для заказа | Дата | Название продукта | Кол-во | Цена за единицу |
---|---|---|---|---|
542378 | 04.07.2012 00:00:00 | Queso Cabrales | 12 | 14.00 |
542378 | 04.07.2012 00:00:00 | Сингапурский Хоккиен Фрид Ми | 10 | 9,80 |
542378 | 04.07.2012 00:00:00 | Моцарелла ди Джованни | 5 | 34,80 |
542379 | 05.07.2012 00:00:00 | Тофу | 9 | 18.60 |
542379 | 05.07.2012 00:00:00 | Сушеные яблоки Манджимуп | 40 | 42,40 |
542380 | 8.07.2012 00:00:00 | Похлебка из моллюсков из Новой Англии Джека | 10 | 7,70 |
542380 | 8.07.2012 00:00:00 | Сушеные яблоки Манджимуп | 35 | 42.40 |
542380 | 8.07.2012 00:00:00 | Соус Луизиана Огненный Острый Перец | 15 | 16,80 |
542381 | 8.07.2012 00:00:00 | Knäckebröd Густава | 6 | 16,80 |
542381 |
SQL JOIN
Соединение SQL ڸ е ֮ ĹϵЩвѯ ݡ
Ключ присоединения
ʱΪ˵õĽҪıлȡǾҪ ִ присоединяйтесь
ݿ еıͨ˴ϵПервичный ключһУеÿһе ֵ Ψһġ ڱ Уÿ ֵ ΨһġĿ ڲظ ÿе ݵ £ ѱ ݽ һ
뿴 «Персоны»
Id_P | Фамилия | Имя | Адрес | Город |
---|---|---|---|---|
1 | Адамс | Джон | Оксфорд-стрит, | Лондон |
2 | Втулка | Джордж | Пятая авеню | Нью-Йорк |
3 | Картер | Томас | Чанган улица (индекс | Пекин |
ע ⣬ «Id_P» Лица еĵζû ܹ ӵͬ Id_Pʹ˵ȫͬId_P Ҳǡ
뿴 «Заказы»
Ид_О | ЗаказатьNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
ע ⣬ «Ид_О» Заказы е «Приказы» е «Id_P» «Лица»
⣬ «Id_P» аϵ
ǿͨķʽлȡ ݣ
номер
ВЫБЕРИТЕ людей.Фамилия, Лиц.Фамилия, Заказы.Номер заказа ОТ Лиц, Заказы ГДЕ Persons.Id_P = Orders.Id_P
Фамилия | Имя | ЗаказатьNo |
---|---|---|
Адамс | Джон | 22456 |
Адамс | Джон | 24562 |
Картер | Томас | 77895 |
Картер | Томас | 44678 |
SQL JOIN — ʹ Присоединиться к
ķҲʹù ؼ ПРИСОЕДИНЯЙТЕСЬ лȡ ݡ
ϣг˵Ķʹ ВЫБРАТЬ 䣺
ВЫБЕРИТЕ людей.Фамилия, Лиц.Фамилия, Заказы.Номер заказа
ОТ Лиц
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ЗАКАЗАТЬ ПО Persons.LastName
Фамилия | Имя | ЗаказатьNo |
---|---|---|
Адамс | Джон | 22456 |
Адамс | Джон | 24562 |
Картер | Томас | 77895 |
Картер | Томас | 44678 |
ͬ SQL JOIN
шт. ВНУТРЕННЕЕ СОЕДИНЕНИЕ
гʹх JOIN ͣԼ ֮ IJ 졣
- ПРИСОЕДИНЯЙТЕСЬ: һƥ 䣬
- ЛЕВАЯ СОЕДИНЕНИЕ: ʹұûƥ 䣬 Ҳе
- ПРАВО ПРИСОЕДИНЯЙТЕСЬ: ʹûƥ 䣬 Ҳұе
- ПОЛНОЕ СОЕДИНЕНИЕ: ֻ Ҫһдƥ 䣬 ͷ
SQL ВНУТРЕННЕЕ СОЕДИНЕНИЕ ؼ
SQL INNER JOIN ؼ
ڱ дһƥʱINNER JOIN ؼ ַ С
INNER JOIN ؼ�
ВЫБЕРИТЕ имя_столбца FROM table_name1 INNER JOIN table_name2 ON имя_таблицы1.имя_столбца = имя_таблицы2.имя_столбца
עͣ INNER JOIN JOIN ͬġ
ԭʼı (е)
«Персоны»
Id_P | Фамилия | Имя | Адрес | Город |
---|---|---|---|---|
1 | Адамс | Джон | Оксфорд-стрит, | Лондон |
2 | Втулка | Джордж | Пятая авеню | Нью-Йорк |
3 | Картер | Томас | Чанган улица (индекс | Пекин |
«Заказы»
Ид_О | ЗаказатьNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
ӣINNER JOINʵ
ڣ ϣг˵Ķ
ʹ ВЫБРАТЬ 䣺
ВЫБЕРИТЕ людей.Фамилия, Лиц.Фамилия, Заказы.Номер заказа ОТ Лиц INNER JOIN Заказы ON Persons.Id_P = Orders.Id_P ЗАКАЗАТЬ ПО Persons.LastName
Фамилия | Имя | ЗаказатьNo |
---|---|---|
Адамс | Джон | 22456 |
Адамс | Джон | 24562 |
Картер | Томас | 77895 |
Картер | Томас | 44678 |
INNER JOIN ؼڱ дһƥʱС «Лица» е «Заказы» ûƥ 䣬 ͲгЩС
SQL INNER JOIN — SQL
На языке SQL, который используется для INNER JOIN, применяется к EQUIJOIN, является типом соединения, объединяющим коммуны для более крупных входных таблиц.Cette commande retourne les enregistrements lorsqu’il y a au moins une ligne dans chaque Colonne qui соответствует à la condition.
Intersection de 2 ensembles
Syntaxe
Pour utiliser ce type de jointure il удобный d’utiliser un Requête SQL avec cette syntaxe:
SELECT * ИЗ table1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ table2 ON table1.id = table2.fk_id
Синтаксис ci-dessus stipule qu’il faut sélectionner les enregistrements des tables table1 et table2 lorsque les données de la colonne «id» de table1 est égal aux de la Colonne fk_id de table2.
La jointure SQL peux aussi écrite de la façon suivante:
SELECT * ИЗ table1 INNER JOIN table2 WHERE table1.id = table2.fk_id
Синтаксис с условием WHERE is un manière alternate de faire la jointure mais qui possible l’inconvénient d’être moins easy à lire s’il y a déjà plusieurs conditions dans le WHERE.
Exemple
Imaginons — это приложение, которое может быть таблицей utilisateur ainsi qu’une table command qui contient toutes les comandes effectuées par les utilisateurs.
Table utilisateur:
id | prenom | nom | ville | |
---|---|---|---|---|
1 | Aimée | Marechal | [email protected] | |
2 | Esmée | Lefort | [email protected] | Lyon |
3 | Marine | Prevost | м[email protected] | Lille |
4 | Luc | Rolland | [email protected] | Marseille |
Командный стол:
utilisateur_id | utilisateur_id | utilisateur_id | num_facture | prix_total |
---|---|---|---|---|
1 | 2013-01-23 | A00103 | 203,14 | |
1 | 2013-02-14 | A00104 | 124.00 | |
2 | 2013-02-17 | A00105 | 149,45 | |
2 | 2013-02-21 | A00106 | 235,35 | |
5 | 2013-03-02 | A00107 | 47,58 |
Вылейте дополнительные сведения о командах, связанных с утилизаторами, если это возможно, по запросу:
SELECT id, prenom, nom, date_achat, num_facture, prix_total ОТ утилизатора ВНУТРЕННЕЕ СОЕДИНЕНИЕ ЗАПРЕЩАЕТСЯ НА utilisateur.id = commande.utilisateur_id
Результаты:
id | prenom | nom | date_achat | num_facture | prix_total |
---|---|---|---|---|---|
prix_total | |||||
01-23 | A00103 | 203,14 | |||
1 | Aimée | Marechal | 2013-02-14 | A00104 | 124.00 |
2 | Esmée | Lefort | 2013-02-17 | A00105 | 149,45 |
2 | Esmée | Lefort | 2013-02-21 | A00106 | 235,35 |
Результаты поиска на двух столах. Les utilisateurs 3 et 4 ne sont pas affichés puisqu’il n’y a pas de commandes associés à ces utilisateurs.
Внимание: очень важно для того, чтобы узнать, что утилизатор в этом дополнении, не может не возвращаться в прошлый раз, когда задан список результатов ВНУТРЕННЯЯ СОЕДИНЕНИЕ повторяет уникальные результаты или состояние, которое находится в двух таблицах.
SQL JOIN — Учебное пособие по SQL
Предложение SQL JOIN используется всякий раз, когда нам нужно выбрать данные из 2 или более таблиц.
Чтобы иметь возможность использовать предложение SQL JOIN для извлечения данных из 2 (или более) таблиц, нам нужна связь между определенными столбцами в этих таблицах.
Мы собираемся проиллюстрировать наш пример SQL JOIN следующими двумя таблицами:
Клиентов:
Идентификатор клиента | Имя | Фамилия | Электронная почта | DOB | Телефон |
1 | Джон | Смит | Джон[email protected] | 04.02.1968 | 626 222-2222 |
2 | Стивен | Золотая рыбка | [email protected] | 04.04.1974 | 323 455-4545 |
3 | Паула | Коричневый | [email protected] | 24.05.1978 | 416 323-3232 |
4 | Джеймс | Смит | джим @ supergig.co.uk | 20.10.1980 | 416 323-8888 |
В продаже:
Идентификатор клиента | Дата | ПродажаКоличество |
2 | 06.05.2004 | $ 100,22 |
1 | 07.05.2004 | 99 $.95 |
3 | 07.05.2004 | $ 122.95 |
3 | 13.05.2004 | 100,00 |
4 | 22.05.2004 | $ 555,55 |
Как вы можете видеть, эти 2 таблицы имеют общее поле с именем CustomerID, и благодаря этому мы можем извлекать информацию из обеих таблиц, сопоставляя их столбцы CustomerID.
Рассмотрим следующий оператор SQL:
ВЫБЕРИТЕ Customers.FirstName, Customers.LastName, SUM (Sales.SaleAmount) AS SalesPerCustomer FROM Customers, Sales WHERE Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName |
Выражение SQL, приведенное выше, выберет всех отдельных клиентов (их имя и фамилию) и соответствующую общую сумму потраченных ими долларов.
Условие SQL JOIN было указано после предложения SQL WHERE и гласит, что две таблицы должны соответствовать их соответствующим столбцам CustomerID.
Вот результат этого оператора SQL:
Имя | Фамилия | SalesPerCustomers |
Джон | Смит | 99 $.95 |
Стивен | Золотая рыбка | $ 100,22 |
Паула | Коричневый | $ 222.95 |
Джеймс | Смит | $ 555,55 |
Приведенный выше оператор SQL можно переписать с помощью предложения SQL JOIN следующим образом:
ВЫБЕРИТЕ клиентов.FirstName, Customers.LastName, SUM (Sales.SaleAmount) AS SalesPerCustomer FROM Customers JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName |
Существует 2 типа SQL JOINS INNER JOINS и OUTER JOINS . Если вы не помещаете ключевые слова INNER или OUTER перед ключевым словом SQL JOIN , тогда используется INNER JOIN . Вкратце «INNER JOIN» = «JOIN» (обратите внимание, что разные базы данных имеют разный синтаксис для своих предложений JOIN).
INNER JOIN выберет все строки из обеих таблиц до тех пор, пока есть совпадение между столбцами, по которым мы сопоставляем. В случае, если у нас есть клиент в таблице «Клиенты», который до сих пор не сделал никаких заказов (для этого клиента нет записей в таблице «Продажи»), этот клиент не будет указан в результате нашего запроса SQL выше.
Если в таблице продаж есть следующие строки:
Идентификатор клиента | Дата | ПродажаКоличество |
2 | 06.05.2004 | 100 $.22 |
1 | 06.05.2004 | $ 99.95 |
И мы используем тот же оператор SQL JOIN , указанный выше:
ВЫБЕРИТЕ Customers.FirstName, Customers.LastName, SUM (Sales.SaleAmount) AS SalesPerCustomer FROM Customer JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customer.FirstName, Customers.LastName |
Получим следующий результат:
Имя | Фамилия | SalesPerCustomers |
Джон | Смит | $ 99.95 |
Стивен | Золотая рыбка | 100 $.22 |
Несмотря на то, что Паула и Джеймс указаны как клиенты в таблице «Клиенты», они не будут отображаться, потому что они еще ничего не купили.
Но что, если вы хотите отобразить всех клиентов и их продажи, независимо от того, заказали они что-то или нет? Сделайте это с помощью предложения SQL OUTER JOIN .
Второй тип SQL JOIN называется SQL OUTER JOIN и имеет 2 подтипа: LEFT OUTER JOIN и RIGHT OUTER JOIN .
LEFT OUTER JOIN или просто LEFT JOIN (вы можете опустить ключевое слово OUTER в большинстве баз данных), выбирает все строки из первой таблицы, перечисленной после предложения FROM, независимо от того, есть ли у них совпадения во второй таблице.
Если мы немного изменим наш последний оператор SQL на:
ВЫБЕРИТЕ Customers.FirstName, Customers.LastName, SUM (Sales.SaleAmount) AS SalesPerCustomer FROM Customer LEFT JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName |
а в таблице продаж по-прежнему есть следующие строки:
Идентификатор клиента | Дата | ПродажаКоличество |
2 | 06.05.2004 | 100 $.22 |
1 | 06.05.2004 | $ 99.95 |
Результат будет следующий:
Имя | Фамилия | SalesPerCustomers |
Джон | Смит | $ 99.95 |
Стивен | Золотая рыбка | 100 $.22 |
Паула | Коричневый | НЕТ |
Джеймс | Смит | НЕТ |
Как видите, мы выбрали все из Заказчиков (первая таблица). Для всех строк от клиентов, которым нет совпадений в продажах (вторая таблица), столбец SalesPerCustomer имеет значение NULL (NULL означает, что столбец ничего не содержит).
RIGHT OUTER JOIN или просто RIGHT JOIN ведет себя точно так же, как SQL LEFT JOIN , за исключением того, что он возвращает все строки из второй таблицы (правая таблица в нашем операторе SQL JOIN ).
.