Упорядочивание с использованием номеров столбцов

SELECT first_name, last_name, dept_no,

  job_code, salary * 1.1

FROM employee

ORDER BY 5

получить список сотрудников, упорядоченный по их зарплате с 10% надбавкой

FIRST_NAME	LAST_NAME	DEPT_NO	JOB_CODE
Ann	Bennet	120	Admin	25228.5
Kelly	Brown	600	Admin	29700
Sue Anne	O'Brien	670	Admin	34402.5
Mark	Guckenheimer	622	Eng	35200
Roger	Reeves	120	Sales	36982.6875
Bill	Parker	623	Eng	38500

Допускается использование нескольких уровней вложенности при упорядочивании выводимой информации по столбцам; при этом разрешается смешивать оба способа.

SELECT first_name, last_name, dept_no,

  job_code, salary * 1.1

FROM employee

ORDER BY dept_no, 5 DESC, last_name

получить список сотрудников, упорядоченный сначала по номерам отделов, в отделах - по убыванию их зарплаты (с 10%), а в пределах одной зарплаты - по фамилиям

FIRST_NAME	LAST_NAME	DEPT_NO	JOB_CODE
Oliver H.	Bender	000	CEO	234135
Terri	Lee	000	Admin	59172.3
Mary S.	MacDonald	100	VP	122388.75
Michael	Yanowski	100	SRep	48400.000000001
Luke	Leung	110	SRep	75685.5
Janet	Baldwin	110	Sales	67801.59375
Takashi	Yamamoto	115	SRep	8228000.0000001
Yuki	Ichida	115	Eng	6600000.0000001

Устранение дублирования (модификатор DISTINCT)

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

Иногда (в зависимости от задачи) бывает необходимо устранить все повторы строк из результирующего набора. Этой цели служит модификатор DISTINCT. Данный модификатор может быть указан только один раз в списке выбираемых элементов и действует на весь список.

SELECT job_code

FROM employee

получить список должностей сотрудников

JOB_CODE

========

VP

Eng

Eng

Mktg

Mngr

SRep

Admin

Finan

Mngr

Mngr

Eng

...

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

SELECT DISTINCT job_code

FROM employee

получить список должностей сотрудников

JOB_CODE

========

Admin

CEO

CFO

Dir

Doc

Eng

Finan

Mktg

Mngr

PRel

SRep

Sales

VP

Два следующих примера показывают, что модификатор DISTINCT действует на всю строку сразу.

SELECT first_name, last_name

FROM employee

WHERE first_name = "Roger"

получить список служащих, имена которых - Roger

FIRST_NAME	LAST_NAME
Roger	De Souza
Roger	Reeves

 border=0 cellpadding=3 cellspacing=0>

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.

После изучения этого раздела мы будем способны:

соединять данные из нескольких таблиц в единую результирующую таблицу;

задавать имена столбцов двумя способами;

записывать внешние соединения;

создавать соединения таблицы с собой.

Операции соединения подразделяются на два вида - внутренние и внешние. Оба вида соединений задаются в предложении 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.

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

Самосоединения

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

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. Иногда требуется включить в результирующий набор большее количество строк.

Вспомним, запрос вида

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", не представленный здесь), в котором еще нет ни одного сотрудника.