Объединение по именам столбцов

Объединение по именам столбцов похоже на естественное, но является более гибким. При естественном объединении проверяется равенство значений из всех одноименных столбцов, которые только имеются в исходных таблицах. А что касается объединения по именам столбцов, то в нем можно выбирать, какие одноименные столбцы должны проверяться, а какие – нет. Если хотите, то можете выбрать их все, фактически превращая объединение по именам столбцов в естественное. Можете также выбрать и меньшее количество этих столбцов. Таким образом, есть возможность в большей степени определить, какие строки из перекрестного произведения должны оказаться в полученной вами итоговой таблице.

Скажем, вы изготовитель шахмат и имеете инвентарную таблицу, в которой хранятся данные о белых фигурах, а также другую такую же таблицу, но с данными о черных фигурах. Эти таблицы называются WHITE (белая) и BLACK (черная), и в каждой из них имеются следующие поля: Piece (фигура), Quant (количество), Wood (дерево). В таблицах хранятся такие данные:

WHITE

BLACK

Piece Quant Wood Piece Quant Wood   King 502 Oak King 502 Ebony               Queen 398 Oak Queen 397 Ebony               Rook 1020 Oak Rook 1020 Ebony               Bishop 985 Oak Bishop 985 Ebony               Knight 950 Oak Knight 950 Ebony               Pawn 431 Oak Pawn 431 Ebony              

Для каждой разновидности фигур – короля (King), королевы (Queen), ладьи (Rook), слона (Bishop), коня (Knight), пешки (Pawn), – изготавливаемых из дуба (Oak) или из черного дерева (Ebony), количество белых и черных фигур должно быть равным. Если же равенство нарушено, то это означает, что некоторые фигуры или потеряны, или украдены, и, следовательно, вам надо улучшить условия хранения товара.

При естественном объединении проверяется равенство значений во всех одноименных столбцах. В таком случае получится пустая таблица, потому что в таблице WHITE нет таких строк, где значение в столбце Wood было бы равно какому-либо значению из столбца Wood таблицы BLACK. Таблица, полученная в результате естественного объединения, не позволит определить, пропало что-нибудь или нет. Поэтому надо использовать объединение по именам столбцов, в котором столбец Wood исключается из рассмотрения. Это объединение может быть представлено в таком виде:

SELECT *

FROM WHITE JOIN BLACK

USING (Piese, Quant);

В результате объединения получается таблица только с теми строками, в которых количество белых и черных фигур, имеющихся на складе, совпадает:

Piece	Quant	Wood	Piece	Quant	Wood
King	502	Oak	King	502	Ebony
Rook	1020	Oak	Rook	1020	Ebony
Bishop	985	Oak	Bishop	985	Ebony
Knight	950	Oak	Knight	950	Ebony

Внимательный читатель может заметить, что из списка пропали королева и пешка, – признак того, что каких-либо из этих фигур не хватает.

Внутреннее объединение

Объединения – мистические операторы, и для правильного обращения с ними требуется недюжинная внутренняя сила. Возможно, вы даже слышали о внутреннем объединении (inner join), – оно-то и является квинтэссенцией реляционных операций. Я вас разыграл! Во внутренних объединениях вовсе нет ничего таинственного. На самом деле внутренними являются все объединения, о которых уже говорилось в этой главе. Объединение по именам столбцов из последнего примера можно сформулировать и как внутреннее, если воспользоваться следующим синтаксисом:

SELECT *

FROM WHITE INNER JOIN BLACK

USING (Piese, Quant);

Результат при этом получится тот же самый.

Внутреннее объединение называется "внутренним", чтобы его можно было отличить от внешнего объединения. Из таблицы, получаемой при внутреннем объединении, выбрасываются все строки, у которых нет соответствующих строк одновременно в обеих исходных таблицах. А при внешнем объединении несоответствующие строки сохраняются. Такая вот между ними разница, и нет в ней ничего метафизического.

Внешнее объединение

При объединении двух таблиц в первой из них (назовем ее левой) могут быть строки, которых нет во второй (правой) таблице. И наоборот, в правой таблице могут быть строки, которых нет в левой. При выполнении внутреннего объединения этих таблиц все несоответствующие строки из вывода удаляются. Однако при внешнем объединении (outer join) такие строки остаются. На самом деле любое внешнее объединение бывает трех видов: левое, правое и полное.

Левое внешнее объединение

В запросе, имеющем объединение, левая таблица – это та, которая в операторе запроса предшествует ключевому слову JOIN, а правая – та, которая следует за ним. При левом внешнем объединении (left outer join) несоответствующие строки, имеющиеся в левой таблице, в выводе сохраняются, а имеющиеся в правой – из него, наоборот, удаляются.

Чтобы понять работу внешних объединений, представьте себе корпоративную базу данных, в которой хранятся записи о сотрудниках компании, ее отделах и представительствах. Примеры данных этой компании приведены в табл. 10.1-10.3.

Таблица 10.1. LOCATION (представительство).

LOCATION_ID (идентификатор представительства)	CITY (город)
1	Boston
3	Tampa
5	Chicago

Таблица 10.2. DEPT (отдел).

DEPT_ID (идентификатор отдела)	LOCATION_ID	NAME (название)
21	1	Sales
24	1	Admin
27	5	Repair
29	5	Stock

Таблица 10.3. EMPLOYEE (сотрудник).

EMP_ID (идентификатор сотрудника)	DEPT_ID	NAME (фамилия)
61	24	Kirk
63	27	McCoy

Теперь предположим, что вам нужно просмотреть все данные обо всех сотрудниках, в том числе, в каком отделе и представительстве сотрудник работает. Такую задачу можно выполнить с помощью объединения, основанного на равенстве:

SELECT *

FROM LOCATION L, DEPT D, EMPLOYEE E

WHERE L.LocationlD a D.LocationID

AND D.DeptID = E.DeptID;

Результат выполнения этого оператора следующий:

1	Boston	24	Admin	61	24	Kirk
5	Chicago	27	Repair	63	27	McCoy

Полученная в результате таблица содержит все данные обо всех сотрудниках, в том числе, в каком отделе и представительстве сотрудник работает. Так как каждый сотрудник компании работает в каком-либо представительстве и в одном из отделов, то для этого примера как раз и подходит объединение, основанное на равенстве.

А теперь предположим, что вам требуются данные как о представительствах, так и связанные с представительствами данные об отделах и сотрудниках. Это "уже совсем другая история", потому что в представительстве может не быть никаких отделов. Поэтому для получения нужных данных используйте, как показано в следующем примере, внешнее объединение:

SELECT *FROM LOCATION L LEFT OUTER JOIN DEPT DON (L.LocationID = D.LocationID)LEFT OUTER JOIN EMPLOYEE EON (D.DeptID = E.DeptID);

В этом объединении данные берутся из трех таблиц. Сначала объединяются таблицы LOCATION и DEPT. Затем получившаяся таблица объединяется с таблицей EMPLOYEE. Даже если строки из таблицы, расположенной левее оператора LEFT OUTER JOIN, и не имеют соответствующих строк в таблице, расположенной правее этого оператора, они все равно входят в результат. Таким образом, при первом объединении в результат войдут все представительства, даже без отделов. А при втором объединении – войдут все отделы, даже без персонала. И вот какой получается результат:

1	Boston	24	1	Admin	61	24	Kirk
5	Chicago	27	5	Repair	63	27	McCoy
3	Tampa	NULL	NULL	NULL	NULL	NULL	NULL
5	Chicago	29	5	Stock	NULL	NULL	NULL
1	Boston	21	1	Sales	NULL	NULL	NULL

В нем первые две строки такие же, как и строки из предыдущего примера. А в третьей строке в столбцах, относящихся к отделам и сотрудникам, находятся неопределенные значения, потому что в Тампе нет никаких отделов и никто из сотрудников там постоянно не работает. В четвертой и пятой строках находятся данные о складе и об отделе продаж, но в столбцах этих строк, относящихся к сотрудникам, находятся неопределенные значения, так как в этих двух отделах персонала нет. Это внешнее объединение сообщает все то же, что и объединение, основанное на равенстве, а также предоставляет следующую информацию:

· обо всех представительствах компании, с отделами или без таковых;

· обо всех отделах компании, с персоналом или без него.

Нет никакой гарантии, что строки из последнего примера выведены в нужном вам порядке. Этот порядок в различных реализациях может быть разным. Чтобы выводить строки в том порядке, который вам нужен, вставьте предложение ORDER BY в оператор SELECT, как, например, в этот:

SELECT *FROM LOCATION L LEFT OUTER JOIN DEPT DON (L.LocationID = D.LocationID)LEFT OUTER JOIN EMPLOYEE EON (D.DeptID = E.DeptID) ORDER BY L.LocationID, D.DeptID, E.EmpID;

Совет:
Так как левого внутреннего объединения не существует, то левое внешнее объединение можно назвать покороче – левое объединение (в коде SQL это ключевые слова LEFT JOIN).

Правое внешнее объединение

Готов поклясться, что вы уже знаете, как ведет себя правое внешнее объединение. И вы правы! Правое внешнее объединение (right outer join) сохраняет в выводе несоответствующие строки, взятые из правой таблицы, но удаляет из него несоответствующие строки, взятые из левой. Это внешнее объединение можно использовать с теми же таблицами, что в левом внешнем объединении, и получить при этом те же результаты. Для этого надо, заменив в операторе ключевые слова левого внешнего объединения на ключевые слова правого, поменять порядок следования таблиц на обратный:

SELECT *FROM EMPLOYEE E RIGHT OUTER JOIN DEPT DON (D.DeptID = E.DeptID)RIGHT OUTER JOIN LOCATION LON (L.LocationID = D.LocationID);

В такой формулировке первое объединение создает таблицу, в которой находятся все отделы, с персоналом или без него. А второе объединение создает таблицу со всеми представительствами, независимо от наличия в них отделов.

Так как правого внутреннего объединения не существует, то правое внешнее объединение можно называть правое объединение (в коде SQL это ключевые слова RIGHT JOIN).

Полное внешнее объединение

Полное внешнее объединение (full outer join) соединяет в себе функции левого и правого внешних объединений. В результате выполнения в выводе остаются несоответствующие строки как из левой, так и из правой таблицы. Проанализируем теперь самый общий вариант корпоративной базы данных, которая уже рассматривалась в предыдущих примерах. В этой базе могут быть:

· представительства без отделов;

· отделы без представительств;

· отделы без сотрудников;

· сотрудники без отделов.

Чтобы показать все представительства, отделы и сотрудников, независимо от того, имеют они соответствующие строки в других таблицах или не имеют, используйте полное внешнее объединение, заданное в следующем виде:

SELECT *FROM LOCATION L FULL JOIN DEPT DON (L.LocationID = D.LocationID)FULL JOIN EMPLOYEE EON (D.DeptID = E.DeptID);

Совет:
Так как полного внутреннего объединения не существует, то полное внешнее объединение можно называть, полное объединение (в коде SQL это ключевые слова FULL JOIN).

Объединение-слияние

В отличие от других видов объединения, объединение-слияние (union join) не пытается искать для строки из левой исходной таблицы хотя бы одну соответствующую строку из правой исходной таблицы. Это объединение создает виртуальную таблицу, в которой находятся все столбцы обеих исходных таблиц. В созданной виртуальной таблице столбцы, взятые из левой исходной таблицы, содержат все строки этой исходной таблицы. В этих строках все столбцы, взятые из правой исходной таблицы, содержат неопределенные значения. И, аналогично, столбцы, взятые из правой исходной таблицы, содержат все строки этой исходной таблицы. А в этих строках все столбцы, взятые из левой исходной таблицы, содержат неопределенные значения. Таким образом, таблица, получившаяся в результате объединения-слияния, содержит все столбцы из обеих исходных таблиц, причем количество ее строк равно сумме количества строк, имеющихся в обеих исходных таблицах.

В большинстве случаев сам по себе результат объединения-слияния лишь промежуточный. В процессе обработки данных он является таблицей с большим количеством неопределенных значений. Впрочем, для получения полезной информации эту таблицу можно использовать вместе с выражением COALESCE (соединение) (см. главу 8).

Предположим, что вы работаете в компании, которая проектирует и производит ракеты, предназначенные для экспериментальных запусков. У вас в работе имеется несколько проектов. Под вашим руководством работают несколько инженеров-проектировщиков, каждый из которых имеет квалификацию в нескольких видах деятельности. Как менеджера вас интересует, какие инженеры в каких видах деятельности имеют квалификацию и над какими проектами работали. В настоящее время эти данные разбросаны по трем таблицам: EMPLOYEE (сотрудник), PROJECTS (проекты) и SKILLS (области квалификации).

В таблице EMPLOYEE хранятся данные о сотрудниках, и ее первичным ключом является EMPLOYEE.EmpID. Каждый проект, над которым работал сотрудник, занимает одну строку в другой таблице – PROJECTS. PROJECTS.EmpID – это внешний ключ, который ссылается на таблицу EMPLOYEE. В таблице SKILLS для каждого сотрудника перечислены те виды деятельности, в которых он имеет квалификацию. SKILLS.EmpID– внешний ключ, который ссылается на таблицу EMPLOYEE.

В таблице EMPLOYEE для каждого сотрудника имеется в точности одна строка. А в таблицах PROJECTS и SKILLS таких строк может быть сколько угодно, в том числе и ни одной.

Примеры данных, хранящихся в трех указанных таблицах, приведены в табл. 10.4-10.6.

Таблица 10.4. Таблица EMPLOYEE.

EmpID	Name (фамилия)
1	Ferguson
2	Frost
3	Toyon

Таблица 10.5. Таблица PROJECTS.

ProjectName (название проекта)	EmpID
X-63 Structure (устройство ракеты Х-63)	1
X-64 Structure (устройство ракеты Х-64)	1
X-63 Guidance (система управления Х-63)	2
X-64 Guidance (система управления Х-64)	2
X-63 Telemetry (телеметрия Х-63)	3
X-64 Telemetry (телеметрия X-64)	3

Как видно в этих таблицах, Фергюсон работал над проектами устройства ракет Х-63 и Х-64, а также является специалистом по механическому проектированию и расчетам аэродинамической нагрузки.

Теперь предположим, что вы как менеджер хотите увидеть всю информацию обо всех своих сотрудниках. Для этого вы решили применить к таблицам EMPLOYEE, PROJECTS и SKILLS объединение, основанное на равенстве:

SELECT *

FROM EMPLOYEE E, PROJECTS P, SKILLS S

WHERE E.EmpID = P.EmpID

AND E.EmpID = S.EmpID;

Таблица 10.6. Таблица SKILLS.

Skill (квалификация)	EmpID
Mechanical Design (механическое проектирование)	1
Aerodynamic Loading (расчеты аэродинамической нагрузки)	1
Analog Design (проектирование аналоговых устройств)	2
Gyroscope Design (проектирование гироскопов)	2
Digital Design (проектирование цифровых устройств)	3
R/F Design (проектирование РЛС)	3

Эту же операцию можно представить в виде внутреннего объединения, используя для этого следующий синтаксис:

SELECT *

FROM EMPLOYEE E INNER JOIN PROJECTS P

ON (E.EmpID = P.EmpID)

INNER JOIN SKILLS S

ON (E.EmpID = S.EmpID);

Обе формулировки дают одинаковый результат, показанный в табл. 10.7.

Таблица 10.7. Результаты внутреннего объединения.

Е.EmpID	E.Name	P.EmpID	ProjectName	S.EmpID	S.Skill
1	Ferguson	1	X-63 Structure	1	Mechanical Design
1	Ferguson	1	X-63 Structure	1	Aerodynamic Loading
1	Ferguson	1	X-64 Structure	1	Mechanical Design
1	Ferguson	1	X-64 Structure	1	Aerodynamic Loading
2	Frost	2	X-63 Guidance	2	Analog Design
2	Frost	2	X-63 Guidance	2	Gyroscope Design
2	Frost	2	X-64 Guidance	2	Analog Design
2	Frost	2	X-64 Guidance	2	Gyroscope Design
3	Toyon	3	X-63 Telemetry	3	Digital Design
3	Toyon	3	X-63 Telemetry	3	R/F Design
3	Toyon	3	X-64 Telemetry	3	Digital Design
3	Toyon	3	X-64 Telemetry	3	R/F Design

Такое расположение данных не отличается слишком большой ясностью. В каждой строке идентификатор сотрудника появляется три раза, а для каждого сотрудника его проекты и виды квалификации указываются несколько раз. Для ответа на возникшие у вас вопросы внутреннее произведение подходит недостаточно хорошо. Более подходящий результат можно получить, используя объединение-слияние с несколькими операторами SELECT. Начнем с простого объединения-слияния:

SELECT *

FROM EMPLOYEE E

UNION JOIN PROJECTS P UNION JOIN SKILLS S;

Обратите внимание, что в объединении-слиянии нет предложения ON. Дело в том, что сейчас данные не фильтруются, поэтому предложение ON не нужно. Результат, полученный при выполнении этого оператора, приведен в табл. 10.8.

Таблица 10.8. Результат операции union join.

E.EmpID	E.Name	P.EmpID	ProjectName	S.EmpID	S.Skill
1	Ferguson	NULL	NULL	NULL	NULL
NULL	NULL	1	X-63 Structure	NULL	NULL
NULL	NULL	1	X-64 Structure	NULL	NULL
NULL	NULL	NULL	NULL	1	Mechanical Design
NULL	NULL	NULL	NULL	1	Aerodynamic Loading
2	Frost	NULL	NULL	NULL	NULL
NULL	NULL	2	X-63 Guidance	NULL	NULL
NULL	NULL	2	X-64 Guidance	NULL	NULL
NULL	NULL	NULL	NULL	2	Analog Design
NULL	NULL	NULL	NULL	2	Gyroscope Design
3	Toyon	NULL	NULL	NULL	NULL
NULL	NULL	3	X-63 Telemetry	NULL	NULL
NULL	NULL	3	X-64 Telemetry	NULL	NULL
NULL	NULL	NULL	NULL	3	Digital Design
NULL	NULL	NULL	NULL	3	R/F Design

Каждая таблица была расширена справа или слева неопределенными (NULL) значениями, после чего проведено объединение в одну таблицу всех строк, получившихся в результате этого расширения. Порядок этих строк произвольный и зависит от используемой реализации. Теперь можно представить полученные данные в более "удобоваримой" форме.

Обратите внимание, что для идентификатора сотрудника в таблице есть три столбца, но в любой из строк определенным является только один из них. Вид выводимой таблицы можно улучшить, если использовать для этих столбцов выражение COALESCE (соединить). Как уже говорилось в главе 8, это выражение выбирает из переданного ему списка значений первое, не являющееся неопределенным. В данном случае COALESCE выбирает из списка столбцов единственное значение:

SELECT COALESCE (E.EmpID, P.EmpID, S.EmpID) AS ID,

E.Name, P.ProjectName, S.Skill

FROM EMPLOYEE E UNION JOIN PROJECTS P

UNION JOIN SKILLS S

ORDER BY ID;

Предложение FROM здесь такое же, как и в предыдущем примере, но теперь три столбца EmpID соединяются с помощью выражения COALESCE в один, который называется ID. Кроме того, результат упорядочивается как раз по этому столбцу ID. Что в итоге получилось, показано в табл. 10.9.

В каждой строке этой таблицы имеются данные или о проекте, или о квалификации, но не о том и другом вместе. При чтении результата необходимо вначале определить, какого типа данные в каждой строке. Если в строке столбец ProjectName является определенным, то в ней указан проект, над которым работал сотрудник. А если определенным является столбец Skill, то в строке указаны навыки сотрудника.

Таблица 10.9. Результат применения операции union join вместе с предложением.

ID	Name	ProjectName	Skill
1	Ferguson	X-63 Structure	NULL
1	Ferguson	X-64 Structure	NULL
1	Ferguson	NULL	Mechanical Design
1	Ferguson	NULL	Aerodynamic Loading
2	Frost	X-63 Guidance	NULL
2	Frost	X-64 Guidance	NULL
2	Frost	NULL	Analog Design
2	Frost	NULL	Gyroscope Design
3	Toyon	X-63 Telemetry	NULL
3	Toyon	X-64 Telemetry	NULL
3	Toyon	NULL	Digital Design
3	Toyon	NULL	R/F Design

Можно получить чуть более ясный результат, если в оператор SELECT вставить еще одно предложение COALESCE, как это сделано в следующем примере:

SELECT COALESCE (E.EmpID, P.EmpID, S.EmpID) AS ID,

E.Name, COALESCE (P.Type, S.Type) AS Type,

P.ProjectName, S.Skill

FROM EMPLOYEE E

UNION JOIN (SELECT "Project" AS Type, *

FROM PROJECTS) P

UNION JOIN (SELECT "Skill" AS Type, *

FROM SKILLS) S

ORDER BY ID, Type;

В первом предложении UNION JOIN таблица PROJECTS заменена вложенным предложением SELECT, которое добавляет к столбцам, взятым из этой таблицы, еще один столбец, Р.Type, с постоянным значением "Project" (проект). И, аналогично, во втором предложении UNION JOIN таблица SKILLS заменена другим вложенным предложением SELECT, которое добавляет к столбцам, взятым из этой таблицы, еще один столбец, S.Type, с постоянным значением "Skill" (квалификация). В каждой строке значением Р.Type является или NULL, или "Project", а значением S.Type – или NULL, или "Skill".

В списке внешнего предложения SELECT указано выполнение операции COALESCE, при которой два столбца Type должны стать одним, также имеющим имя Type. Затем этот новый столбец Type указывается в предложении ORDER BY, которое таким образом сортирует все строки, чтобы вначале шли строки с проектами, а затем – с квалификационными навыками. Результат показан в табл. 10.10.

Таблица 10.10. Усовершенствованный результат применения операции union join вместе с предложением coalesce.

ID	Name	Type	ProjectName	Skill
1	Ferguson	Project	X-63 Structure	NULL
1	Ferguson	Project	X-64 Structure	NULL
1	Ferguson	Skill	NULL	Mechanical Design
1	Ferguson	Skill	NULL	Aerodynamic Loading
2	Frost	Project	X-63 Guidance	NULL
2	Frost	Project	X-64 Guidance	NULL
2	Frost	Skill	NULL	Analog Design
2	Frost	Skill	NULL	Gyroscope Design
3	Toyon	Project	X-63 Telemetry	NULL
3	Toyon	Project	X-64 Telemetry	NULL
3	Toyon	Skill	NULL	Digital Design
3	Toyon	Skill	NULL	R/F Design

Полученная теперь таблица представляет собой отчет – причем очень удобный для чтения – об опыте участия в проектах и о квалификации всех сотрудников, перечисленных в таблице EMPLOYEE.

Если учесть количество имеющихся сейчас разных операций объединения (JOIN), то связывание данных из разных таблиц не должно создавать проблему, какой бы ни была структура этих таблиц. Поверьте, что если только в вашей базе имеются какие-либо сырые данные, то в SQJL2OO3 найдутся средства, чтобы их оттуда извлечь, а затем показать в каком-либо содержательном виде.

ON или WHERE

Роль, которую в объединениях разных видов играют предложения ON и WHERE, бывает достаточно запутанной. Прояснить ситуацию можно с помощью следующих фактов.

· Предложение ON является частью внутренних, левых, правых и полных объединений. В перекрестных объединениях и объединениях-слияниях такого предложения нет, потому что ни одно из них никакой фильтрации данных не выполняет.

· Во внутреннем объединении предложения ON и WHERE логически эквивалентны; одно и то же условие можно указать или с помощью ON, или с помощью WHERE.

Во внешних объединениях предложения ON и WHERE отличаются друг от друга. Предложение WHERE всего-навсего фильтрует строки, возвращаемые предложением FROM. Строки, отбракованные фильтром, просто не попадут в результат. А предложение ON, используемое во внешнем объединении, вначале фильтрует строки перекрестного произведения, а затем добавляет в результат и отбракованные строки, расширенные неопределенными значениями.