Файл: Краткий справочник по sql оператор select.pdf

Толмачев П.В. (
www.ptolmachev.ru
). Страница 11 из 16
JOB_CODE
========
VP
Eng
Eng
Mktg
Mngr
SRep
Данный пример некорректно решает задачу "получения" списка должностей сотрудников предприятия, так как в нем имеются многочисленные повторы, затрудняющие восприятие информации. Тот же запрос, включающий модификатор
DISTINCT, устраняющий дублирование, дает верный результат.
SELECT DISTINCT job_code
FROM employee получить список должностей сотрудников
JOB_CODE
========
Admin
CEO
CFO
Dir
Doc
Модификатор DISTINCT действует на всю строку сразу.
SELECT DISTINCT first_name, last_name
FROM employee
WHERE first_name = "Roger" получить список служащих, имена которых - Roger
FIRST_NAME LAST_NAME
Roger De Souza
Roger Reeves
Соединение (JOIN)
Операция соединения используется в языке SQL для вывода связанной информации, хранящейся в нескольких таблицах, в одном запросе. В этом проявляется одна из наиболее важных особенностей запросов SQL - способность определять связи между многочисленными таблицами и выводить информацию из них в рамках этих связей. Именно эта операция придает гибкость и легкость языку
SQL.

Толмачев П.В. (
www.ptolmachev.ru
). Страница 12 из 16
После изучения этого раздела мы будем способны:

соединять данные из нескольких таблиц в единую результирующую таблицу;

задавать имена столбцов двумя способами;

записывать внешние соединения;

создавать соединения таблицы с собой.
Операции соединения подразделяются на два вида - внутренние и внешние. Оба вида соединений задаются в предложении 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
Этот запрос ("без соединения") возвращает неверный результат, так как имеющиеся между таблицами связи не задействованы. Отсюда и появляется дублирование

Толмачев П.В. (
www.ptolmachev.ru
). Страница 13 из 16
информации в результирующей таблице. Правильный результат дает запрос с использованием операции соединения:
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

Толмачев П.В. (
www.ptolmachev.ru
). Страница 14 из 16
А вот пример запроса, соединяющего сразу три таблицы:
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 состоит из трех полей.
Мы рассмотрели внутренние соединения с использованием стандарта 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_NAME LAST_NAME DEPARTMENT
Leslie
Phong
Customer Support
Bill
Parker
Customer Support

Толмачев П.В. (
www.ptolmachev.ru
). Страница 15 из 16
Самосоединения
В некоторых задачах необходимо получить информацию, выбранную особым образом только из одной таблицы. Для этого используются так называемые самосоединения, или рефлексивные соединения. Это не отдельный вид соединения, а просто соединение таблицы с собой с помощью алиасов. Самосоединения полезны в случаях, когда нужно получить пары аналогичных элементов из одной и той же таблицы.
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
SELECT d1.department, d2.department, d1.budget
FROM department d1, department d2
WHERE d1.budget = d2.budget
AND d1.dept_no < d2.dept_no получить список пар отделов с одинаковыми годовыми бюджетами
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. Иногда требуется включить в результирующий набор большее количество строк.
Вспомним, запрос вида

Толмачев П.В. (
www.ptolmachev.ru
). Страница 16 из 16
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", не представленный здесь), в котором еще нет ни одного сотрудника.