Введение в DELPHI

       

Соединение (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_NAMELAST_NAMEDEPARTMENT
RobertNelsonCorporate Headquarters
Mary S.MacDonaldCorporate Headquarters
RobertNelsonSales and Marketing
Mary S.MacDonaldSales and Marketing
RobertNelsonEngineering
Mary S.MacDonaldEngineering
RobertNelsonFinance
Mary S.MacDonaldFinance

Этот запрос ("без соединения") возвращает неверный результат, так как имеющиеся между таблицами связи не задействованы. Отсюда и появляется дублирование информации в результирующей таблице. Правильный результат дает запрос с использованием операции соединения: SELECT first_name, last_name, department

получить список сотрудников, состоящих в должности "вице-президент", а также названия их отделов

FIRST_NAMELAST_NAMEDEPARTMENT
RobertNelsonEngineering
Mary S.MacDonaldSales and Marketing

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

Замечание 1: в одном запросе нельзя смешивать использование написания имен таблиц и их алиасов.

Замечание 2: алиасы таблиц могут совпадать с их именами.

получить список сотрудников, состоящих в должности "вице-президент", а также названия их отделов

FIRST_NAME LAST_NAME DEPARTMENT
RobertNelsonEngineering
Mary S.MacDonaldSales 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_NAMELAST_NAMEJOB_TITLEDEPARTMENT
RobertNelsonVice PresidentEngineering
PhilForestManagerQuality Assurance
K. J.WestonSales RepresentativeField Office: East Coast
KatherineYoungManagerCustomer Support
ChrisPapadopoulosManagerResearch and Development
JanetBaldwinSales Co-ordinatorPacific Rim Headquarters
RogerReevesSales Co-ordinatorEuropean Headquarters
WalterSteadmanChief Financial OfficerFinance

В данном примере последние три условия необходимы в силу того, что первичный ключ в таблице JOB состоит из трех полей - см. рис.1.

Мы рассмотрели внутренние соединения с использованием стандарта ANSI-89. Теперь опишем новый (ANSI-92) стандарт:

  • условия соединения записываются в предложении FROM, в котором слева и справа от зарезервированного слова "JOIN" указываются соединяемые таблицы;
  • условия поиска, основанные на правой таблице, помещаются в предложение ON;
  • условия поиска, основанные на левой таблице, помещаются в предложение WHERE.
SELECT first_name, last_name, department FROM employee e JOIN department d ON e.dept_no = d.dept_no AND department = "Customer Support" WHERE last_name starting with "P"

получить список служащих (а заодно и название отдела), являющихся сотрудниками отдела "Customer Support", фамилии которых начинаются с буквы "P"

FIRST_NAMELAST_NAMEDEPARTMENT
LesliePhongCustomer Support
BillParkerCustomer 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_NAMELAST_NAMEHIRE_DATE
NelsonYoung28-DEC-1988
ReevesStansbury25-APR-1991
BishopMacDonald1-JUN-1992
BrownIchida4-FEB-1993
SELECT d1.department, d2.department, d1.budget FROM department d1, department d2 WHERE d1.budget = d2.budget AND d1.dept_no < d2.dept_no

получить список пар отделов с одинаковыми годовыми бюджетами

DEPARTMENTDEPARTMENTBUDGET
Software DevelopmentFinance400000.00
Field Office: East CoastField Office: Canada 500000.00
Field Office: JapanField Office: East Coast500000.00
Field Office: JapanField Office: Canada500000.00
Field Office: JapanField Office: Switzerland500000.00
Field Office: SingaporeQuality Assurance300000.00
Field Office: SwitzerlandField Office: East Coast500000.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_NAMELAST_NAME DEPARTMENT
RobertNelsonEngineering
BruceYoungSoftware Development

В результирующий набор входит и отдел "Software Products Div." (а также отдел "Field Office: Singapore", не представленный здесь), в котором еще нет ни одного сотрудника.

|



Содержание раздела