JOIN-соединения – операции горизонтального соединения данных

Здесь нам очень пригодится знание структуры БД, т.е. какие в ней есть таблицы, какие данные хранятся в этих таблицах и по каким полям таблицы связаны между собой. Первым делом всегда досконально изучайте структуру БД, т.к. нормальный запрос можно написать только тогда, когда ты знаешь, что откуда берется. У нас структура состоит из 3-х таблиц Employees, Departments и Positions. Приведу здесь диаграмму из первой части:

Если суть РДБ – разделяй и властвуй, то суть операций объединений снова склеить разбитые по таблицам данные, т.е. привести их обратно в человеческий вид.

Если говорить просто, то операции горизонтального соединения таблицы с другими таблицами используются для того, чтобы получить из них недостающие данные. Вспомните пример с еженедельным отчетом для директора, когда при запросе из таблицы Employees, нам для получения окончательного результата недоставало поля «Название отдела», которое находится в таблице Departments.

Начнем с теории. Есть пять типов соединения:

1. JOIN – левая_таблица JOIN правая_таблица ON условия_соединения

2. LEFT JOIN – левая_таблица LEFT JOIN правая_таблица ON условия_соединения

3. RIGHT JOIN – левая_таблица RIGHT JOIN правая_таблица ON условия_соединения

4. FULL JOIN – левая_таблица FULL JOIN правая_таблица ON условия_соединения

5. 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.

Лично я всегда при написании запросов использую только краткий синтаксис, по той причине:

1.Это короче и не засоряет запрос лишними словами;

2.По словам LEFT, RIGHT, FULL и CROSS и так понятно о каком соединении идет речь, так же и в случае просто JOIN;

3.Считаю слова 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	Логистика