Соединение (JOIN)
Операция соединения используется в языке SQL для вывода связанной информации, хранящейся в нескольких таблицах, в одном запросе. В этом проявляется одна из наиболее важных особенностей запросов SQL - способность определять связи между многочисленными таблицами и выводить информацию из них в рамках этих связей. Именно эта операция придает гибкость и легкость языку SQL.
После изучения этого раздела мы будем способны:
- соединять данные из нескольких таблиц в единую результирующую таблицу;
- задавать имена столбцов двумя способами;
- записывать внешние соединения;
- создавать соединения таблицы с собой.
Операции соединения подразделяются на два вида - внутренние и внешние. Оба вида соединений задаются в предложении WHERE запроса SELECT с помощью специального условия соединения. Внешние соединения (о которых мы поговорим позднее) поддерживаются стандартом ANSI-92 и содержат зарезервированное слово "JOIN", в то время как внутренние соединения (или просто соединения) могут задаваться как без использования такого слова (в стандарте ANSI-89), так и с использованием слова "JOIN" (в стандарте ANSI-92).
Связывание производится, как правило, по первичному ключу одной таблицы и внешнему ключу другой таблицы - для каждой пары таблиц. При этом очень важно учитывать все поля внешнего ключа, иначе результат будет искажен. Соединяемые поля могут (но не обязаны!) присутствовать в списке выбираемых элементов. Предложение WHERE может содержать множественные условия соединений. Условие соединения может также комбинироваться с другими предикатами в предложении WHERE.
Внутренние соединения
Внутреннее соединение возвращает только те строки, для которых условие соединения принимает значение true. SELECT first_name, last_name, department FROM employee, department WHERE job_code = "VP"
получить список сотрудников, состоящих в должности "вице-президент", а также названия их отделов
| FIRST_NAME | LAST_NAME | DEPARTMENT |
| Robert | Nelson | Corporate Headquarters |
| Mary S. | MacDonald | Corporate Headquarters |
| Robert | Nelson | Sales and Marketing |
| Mary S. | MacDonald | Sales and Marketing |
| Robert | Nelson | Engineering |
| Mary S. | MacDonald | Engineering |
| Robert | Nelson | Finance |
| Mary S. | MacDonald | Finance |
Этот запрос ("без соединения") возвращает неверный результат, так как имеющиеся между таблицами связи не задействованы. Отсюда и появляется дублирование информации в результирующей таблице. Правильный результат дает запрос с использованием операции соединения: SELECT first_name, last_name, department
получить список сотрудников, состоящих в должности "вице-президент", а также названия их отделов
| FIRST_NAME | LAST_NAME | DEPARTMENT |
| Robert | Nelson | Engineering |
| Mary S. | MacDonald | Sales and Marketing |
В вышеприведенном запросе использовался способ непосредственного указания таблиц с помощью их имен. Возможен (а иногда и просто необходим) также способ указания таблиц с помощью алиасов (псевдонимов). При этом алиасы определяются в предложении FROM запроса SELECT и представляют собой любой допустимый идентификатор, написание которого подчиняется таким же правилам, что и написание имен таблиц. Потребность в алиасах таблиц возникает тогда, когда названия столбцов, используемых в условиях соединения двух (или более) таблиц, совпадают, а названия таблиц слишком длинны...
Замечание 1: в одном запросе нельзя смешивать использование написания имен таблиц и их алиасов.
Замечание 2: алиасы таблиц могут совпадать с их именами.
получить список сотрудников, состоящих в должности "вице-президент", а также названия их отделов
| FIRST_NAME | LAST_NAME | DEPARTMENT |
| Robert | Nelson | Engineering |
| Mary S. | MacDonald | Sales and Marketing |
А вот пример запроса, соединяющего сразу три таблицы: SELECT first_name, last_name, job_title, department FROM employee e, department d, job j WHERE d.mngr_no = e.emp_no AND e.job_code = j.job_code AND e.job_grade = j.job_grade AND e.job_country = j.job_country
получить список сотрудников с названиями их должностей и названиями отделов
| FIRST_NAME | LAST_NAME | JOB_TITLE | DEPARTMENT |
| Robert | Nelson | Vice President | Engineering |
| Phil | Forest | Manager | Quality Assurance |
| K. J. | Weston | Sales Representative | Field Office: East Coast |
| Katherine | Young | Manager | Customer Support |
| Chris | Papadopoulos | Manager | Research and Development |
| Janet | Baldwin | Sales Co-ordinator | Pacific Rim Headquarters |
| Roger | Reeves | Sales Co-ordinator | European Headquarters |
| Walter | Steadman | Chief Financial Officer | Finance |
В данном примере последние три условия необходимы в силу того, что первичный ключ в таблице JOB состоит из трех полей - см. рис.1.
Мы рассмотрели внутренние соединения с использованием стандарта ANSI-89. Теперь опишем новый (ANSI-92) стандарт:
- условия соединения записываются в предложении FROM, в котором слева и справа от зарезервированного слова "JOIN" указываются соединяемые таблицы;
- условия поиска, основанные на правой таблице, помещаются в предложение ON;
- условия поиска, основанные на левой таблице, помещаются в предложение WHERE.
получить список служащих (а заодно и название отдела), являющихся сотрудниками отдела "Customer Support", фамилии которых начинаются с буквы "P"
| FIRST_NAME | LAST_NAME | DEPARTMENT |
| Leslie | Phong | Customer Support |
| Bill | Parker | Customer Support |
Самосоединения
В некоторых задачах необходимо получить информацию, выбранную особым образом только из одной таблицы. Для этого используются так называемые самосоединения, или рефлексивные соединения. Это не отдельный вид соединения, а просто соединение таблицы с собой с помощью алиасов. Самосоединения полезны в случаях, когда нужно получить пары аналогичных элементов из одной и той же таблицы. SELECT one.last_name, two.last_name, one.hire_date FROM employee one, employee two WHERE one.hire_date = two.hire_date AND one.emp_no < two.emp_no
получить пары фамилий сотрудников, которые приняты на работу в один и тот же день
| LAST_NAME | LAST_NAME | HIRE_DATE |
| Nelson | Young | 28-DEC-1988 |
| Reeves | Stansbury | 25-APR-1991 |
| Bishop | MacDonald | 1-JUN-1992 |
| Brown | Ichida | 4-FEB-1993 |
получить список пар отделов с одинаковыми годовыми бюджетами
| DEPARTMENT | DEPARTMENT | BUDGET |
| Software Development | Finance | 400000.00 |
| Field Office: East Coast | Field Office: Canada | 500000.00 |
| Field Office: Japan | Field Office: East Coast | 500000.00 |
| Field Office: Japan | Field Office: Canada | 500000.00 |
| Field Office: Japan | Field Office: Switzerland | 500000.00 |
| Field Office: Singapore | Quality Assurance | 300000.00 |
| Field Office: Switzerland | Field Office: East Coast | 500000.00 |
Напомним, что внутреннее соединение возвращает только те строки, для которых условие соединения принимает значение true. Иногда требуется включить в результирующий набор большее количество строк.
Вспомним, запрос вида SELECT first_name, last_name, department FROM employee e, department d WHERE e.dept_no = d.dept_no
возвращает только те строки, для которых условие соединения (e.dept_no = d.dept_no) принимает значение true.
Внешнее соединение возвращает все строки из одной таблицы и только те строки из другой таблицы, для которых условие соединения принимает значение true. Строки второй таблицы, не удовлетворяющие условию соединения (т.е. имеющие значение false), получают значение null в результирующем наборе.
Существует два вида внешнего соединения: LEFT JOIN и RIGHT JOIN.
В левом соединении (LEFT JOIN) запрос возвращает все строки из левой таблицы (т.е. таблицы, стоящей слева от зарезервированного словосочетания "LEFT JOIN") и только те из правой таблицы, которые удовлетворяют условию соединения. Если же в правой таблице не найдется строк, удовлетворяющих заданному условию, то в результате они замещаются значениями null.
Для правого соединения - все наоборот. SELECT first_name, last_name, department FROM employee e LEFT JOIN department d ON e.dept_no = d.dept_no
получить список сотрудников и название их отделов, включая сотрудников, еще не назначенных ни в какой отдел
| FIRST_NAME | LAST_NAME | DEPARTMENT |
| Robert | Nelson | Engineering |
| Bruce | Young | Software Development |
В результирующий набор входит и отдел "Software Products Div." (а также отдел "Field Office: Singapore", не представленный здесь), в котором еще нет ни одного сотрудника.
|
