Общие свойства конструкции inner JOIN и конструкции where

До сих пор при описании особенностей конструкции INNER JOIN фактически за­трагивались только те концепции, которые применимы к соединениям любых других типов, поскольку принципы определения порядка расположения столбцов в результи­рующем наборе и применения псевдонимов являются полностью одинаковыми для конструкций JOIN любых типов. А то, в чем конструкция INNER JOIN отличается от конструкций JOIN других типов осталось не рассмотренным. Отметим, что ее помощью можно создавать исключительное со­единение, т.е. соединение, в котором исключены все строки, не имеющие опреде­ленного значения в обеих таблицах (в левой таблице, как называют таблицу, указанную в первую очередь, и в правой таблице, заданной во вторую очередь).

Рассмотрим несколько примеров того, как проявляется это свойство.

Допустим, что имеется таблица Departments, в которой собраны все данные о департаментах компании. Но наличие большого списка департаментов от­нюдь не означает, что на текущий момент в компании были увольнения во всех департаментах. Фактически можно смело предположить, что в текущий момент имеются такие департаменты, в которых не было увольнений. Например, требуется выполнить запрос, позволяющий отобрать информацию обо всех департаментах, в которых были увольнения.

Пример 3.

SELECT DISTINCT d.department_id, d.department_name

FROM departments d

inner join job_history j

on d.department_id=j.department_id

 

Рисунок №51 – Результат выборки из примера №3.

 

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

Теперь попытаемся получить информацию об общем количестве заказчиков и для этого вызовем на выполнение следующий простой запрос с агрегирующей функцией COUNT:

 

Пример 4.

SELECT COUNT (*) AS "No. Of Records" FROM departments

 

Рисунок №52 – Результат выборки из примера №4

 

Применение конструкции INNER JOIN приводит к исключению строк в связи с тем, что не обнаруживаются соответству­ющие им строки в другой таблице, а использование конструкции WHERE приводит к исключению строк из возвращаемого набора, поскольку эти строки не соответствуют сданным критериям.

Рассмотрим еще один пример – база данных имеет таблицы departments, Jobs и Employees (таблица 11).

 

 

Таблица 11 - Столбцы таблиц departments, Jobs и Employees.

Departments	Jobs	Employees
DEPARTMENT_ID	JOB_ID	EMPLOYEE_ID
DEPARTMENT_NAME	JOB_TITLE	FIRST_NAME
MANAGER_ID	MIN_SALARY	LAST_NAME
LOCATION_ID	MAX_SALARY	EMAIL
		PHONE_NUMBER
		HIRE_DATE
		JOB_ID
		SALARY
		COMMISSION_PCT
		MANAGER_ID
		DEPARTMENT_ID

 

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

Теперь необходимо приступить к соединению двух указанных таблиц. Но для ИТОГО требуется найти столбец, по которому должно быть выполнено соединение. В связи с этим в ходе решения данной задачи возникает первая проблема - обнару­живается, что такой столбец отсутствует. Оказывается, что в этих двух таблицах нет общих столбцов, на которых можно было бы сформировать соединение с помощью конструкции JOIN. Теперь становится ясно, для чего нужна третья из таблиц, приведенных в таблице 6. Иногда соединения могут быть сформированы только с помощью такой таблицы, как employees, которая позволяет проложить связь между двумя другими таблицами. Для обозначения подобных таблиц, обеспечивающих возможность связать две другие таблицы, применяются разные термины, но чаще всего приходилось встречаться с терминами связующая таблица, или таблица ассоциации.

Связующей таблицей (иногда называемой также таблицей ассоциации, или таблицей сли­яния) называют любую таблицу, основным назначением которой является не хранение соб­ственных данных, а создание связей между данными, хранимыми в других таблицах. Такие таблицы можно рассматривать как средства "обеспечения взаимодействие", или "созда­ния связей" между двумя или несколькими таблицами. В частности, связующие таблицы позволяют найти выход в такой часто складывающейся ситуации, когда имеет место так называемая связь "многие ко многим" между таблицами. В такой ситуации две таблицы содержат связанные друг с другом данные, причем и в той и в другой таблице может на­ходиться большое количество строк, которые согласуются со многими строками в другой таблице. СУБД SQL Server не позволяет непосредственно реализовывать подобные свя­зи, поэтому применяются связующие таблицы, позволяющие разделить связь "многие ко многим" на две связи "один ко многим", а последние поддерживаются СУБД SQL Server.

Данная конкретная таблица, employees, не отвечает всем критериям определе­ния связующей таблицы в самом строгом смысле этого термина, но все же соответ­ствует общему назначению связующих таблиц, поэтому рассматривается именно как таковая. Применение указанной третьей таблицы, employees, позволяет косвенно соединить таблицы departments и jobs, фор­мируя соединения между каждой из этих таблиц и связующей таблицей. Соединение между таблицами departments и employees формируется на основе столбца department_id, a соединение между таблицами jobs и employees — на основе столбца job_id.

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

 

Пример 5:

SELECT distinct d.department_name, j.job_title

FROM departments d

join employees e

on d.department_id = e.department_id join jobs j

on e.job_id = j.job_id

 

Рисунок №53 - Результат выборки из примера №5.

 

Обратите внимание на то, что таблицам присвоены псевдонимы, поэтому необ­ходимо вернуться в начало оператора и внести изменения в конструкцию SELECT с учетом использования псевдонимов, но на этом составление оператора SELECT с со­единением трех таблиц заканчивается.

 

КОНСТРУКЦИИ OUTER JOIN

Применение конструкции JOIN такого типа, как OUTER JOIN, скорее можно счи­тать исключением, а не правилом:

Чаще всего при выборке данных с использованием оператора соединения не­обходимо обеспечить, чтобы данные соответствовали всем заданным критери­ям, а этого позволяет добиться только конструкция INNER JOIN.

- Многие разработчики, использующие язык SQL, осваивают лишь внутреннее соединение, осуществляемое с помощью конструкции INNER JOIN, но так и не заходят глубже; иными словами, многие разработчики просто не умеют пользо­ваться разновидностью оператора соединения с конструкцией OUTER.

- Цели, которые позволяет достичь применение конструкции OUTER JOIN, часто достижимы с помощью других методов.

- Разработчики зачастую просто забывают о том, что может использоваться по­добная конструкция.

При использовании конструкции INNER JOIN исключают­ся все строки, не соответствующие всем заданным критериям, а при использовании конструкции OUTER существует возможность включить в результирующий набор строки, которые со­ответствуют хотя бы одному из заданных критериев.

 

Простой вариант оператора с конструкцией OUTER JOIN

Задача освоения первого варианта синтаксиса является несложной, и большин­ство разработчиков с ней успешно справляются:

SELECT *

FROM имя_таблицы1 LEFT|RIGHT OUTER JOIN имя_таблицы2

ON имя_тоблицы1. столбец1 = имя_таблицы2.столбец2

 

Следует отметить, что ключевое слово OUTER является необязательным, достаточно лишь включить ключевое слово LEFT или RIGHT (например, LEFT JOIN). Таблица, имя кото­рой упоминается перед ключевым словом JOIN, рассматривается как левая таблица, LEFT, а таблица, имя которой следует за ключевым словом JOIN, — как правая табли­ца, RIGHT.

Как уже было сказано, применение операторов с конструкцией OUTER JOIN приво­дит к получению не только тех данных, которые соответствуют всем критериям, но и данных, соответствующих лишь некоторым критериям, а то, какие именно строки, соответствующие лишь некоторым критериям, будут включены в результирующий на­бор, зависит от выбора той или иной стороны соединения. При использовании кон­струкции LEFT OUTER JOIN включается вся информация из таблицы, указанной слева ОТ ключевого слова JOIN, а при использовании конструкции RIGHT OUTER JOIN— вся информация из таблицы, указанной справа от этого ключевого слова. Рассмотрим практические примеры применения запросов с левым и правым внешним соедине­ниями, позволяющие лучше понять сказанное.

 

Предположим, что необходимо узнать, из каких департаментов состоит фирма, имеются ли уволенные, идентификационный номер сотрудника и занимаемой им должности. В базе данных находятся таблицы departments и job_history, которые определены следующим образом:

 

Departments	Job_history
DEPARTMENT_ID	EMPLOYEE_ID
DEPARTMENT_NAME	START_DATE
MANAGER_ID	END_DATE
LOCATION_ID	JOB_ID
	DEPARTMENT_ID

 

Эти таблицы имеют общий столбец, department_id, поэтому можно попытаться непо­средственно выполнить их соединение. Оператор, составленный с применением обычной конструкции INNER JOIN, должен выглядеть примерно таким образом:

 

Пример 6:

SELECT d.department_id, department_name, job_id, employee_id

FROM departments d

inner join job_history jh

ON (d.department_id = jh.department_id)

 

 

Рисунок №54 - Результат выборки из примера №6.

 

Однако наше задание отнюдь нельзя назвать выполненным. Нам требовались ре­зультаты, в которых содержалась бы информация обо всех департаментах, а не только о тех, в которых были увольнения. Тем не менее выполнение данного запроса привело к получению только данных о департаментах компании, в которых были увольнения, а ответ на заданный вопрос не получен.

В действительности требуется такая конструкция, которая позволяет получить сведения обо всех департаментах. Для получения сведений о каждом департаменте, достаточно только изменить тип конструкции JOIN в запросе следующим образом:

Пример 7:

SELECT d.department_id, department_name, job_id, employee_id

FROM departments d

LEFT OUTER JOIN job_history jh

ON (d.department_id = jh.department_id)

 

Рисунок №55 - Результат выборки из примера №7.

 

Теперь рассмотрим, что произойдет после перехода к применению соединения типа RIGHT OUTER JOIN

 

Пример 8:

SELECT d.department_id, department_name, job_id, employee_id

FROM departments d

RIGHT OUTER JOIN job_history jh

ON (d.department_id = jh.department_id)

 

 

Рисунок №56 - Результат выборки из примера №8.

 

Даже несмотря на то, что эта модификация на первый взгляд кажется весьма не­значительной, фактически она приводит к резкому изменению состава результирую­щего набора. Если теперь оператор SELECT * будет выполнен применительно к таблице departments, то обнаружится, что в состав результатов запроса включены все строки из таблицы job_history, причем при наличии соответствующей строки в таблице departments отображается относящаяся к этой строке информация о должности. А во всех остальных слу­чаях столбцы, взятые из таблицы таблице job_history, заполняются NULL-значениями. Итак, если допустить, что таблица departments всегда будет упоминаться в запросе в первую очередь, а таблица таблице job_history — во вторую, то, чтобы получить информацию обо всех департаментах, нужно использовать конструкцию LEFT JOIN, а для ознакомления с инфор­мацией обо всех увольнениях - конструкцию RIGHT JOIN.