Примеры запросов с использованием предиката сравнения

Пример 18.1. Найти номера отделов, в которых работают служащие с фамилией'Smith'.

SELECT DISTINCT EMP.DEPT_NO

FROM EMP

WHERE EMP.EMP_NAME = 'Smith';

Мы добавили спецификациюDISTINCTв разделSELECT, потому что в одном отделе могут работать несколько служащих с фамилией'Smith', а их число нас в данном случае не интересует. Кстати, если бы нас интересовало число служащих с фамилией'Smith'в каждом отделе, где такие служащие работают, то следовало бы, например, написать такой запрос (пример 18.1.1):

SELECT EMP.DEPT_NO, COUNT(*)

FROM EMP

WHERE EMP.NAME = 'Smith'

GROUP BY EMP.DEPT_NO;

В этом варианте запроса спецификация DISTINCTне требуется, поскольку в запросе содержится разделGROUP BY, группировка производится в соответствии со значениями столбцаEMP.DEPT_NO, и строка результата соответствует одной группе.

Пример 18.2. Найти номера, имена и номера отделов служащих, родившихся после 15 апреля 1965 г.

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO

FROM EMP

WHERE EMP.EMP_BDATE > DATE '1965-04-15';

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

Пример 18.3. Найти номера, имена и номера отделов служащих, размер заработной платы которых составляет больше одной десятой объема фонда заработной платы их отделов.

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO

FROM EMP

WHERE EMP.EMP_SAL > 0.1 *

(SELECT DEPT_TOTAL_SAL

FROM DEPT

WHERE DEPT.DEPT_NO = EMP.DEPT_NO);

В этом SQL-запросе имеются две интересные особенности, которые мы до сих пор не обсуждали. Во-первых, второй операнд операции сравнения содержит подзапрос, возвращающий единственное значение, поскольку логическое выражение разделаWHEREэтого подзапроса состоит из условия, однозначно определяющего значение первичного ключа таблицыDEPT. Во-вторых, в условии разделаWHEREподзапроса используется ссылка на столбец таблицыEMP, указанной в разделеFROM«внешнего» запроса. Подобные подзапросы в терминологии SQL традиционно называются корреляционными, и их следует понимать следующим образом¹³⁶⁾.

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

Кстати, эквивалентная формулировка на языке SQLпримера 18.3выглядит следующим образом (пример 18.3.1):

136 Здесь снова идет речь о семантике выполнения оператора SELECT. В стандарте, естественно, не требуется, чтобы в реализации языка запросы с корреляционными подзапросами выполнялись в точности так, как описывается ниже. Суть в том, что какой бы реальный алгоритм выполнения такого запроса не использовался, результат выполнения должен быть точно таким же, как если бы запрос выполнялся по описываемой схеме.

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO

FROM EMP, DEPT

WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND

EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL;

Мы видим, что в терминах реляционной алгебры этот запрос представляет собой ограничение (по условию EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL)эквисоединениятаблицEMPиDEPT(по условиюEMP.DEPT_NO = DEPT.DEPT_NO). Подобную операцию часто называютполусоединением (semijoin), поскольку в результирующей таблице используются столбцы только одного из операндов операции эквисоединения. Мы привели вторую формулировку запроса, преследуя две цели: (1) продемонстрировать, каким образом предикат сравнения можно использовать для задания условия соединения, и (2) показать, что запросы, содержащие вложенные запросы, часто могут быть переформулированы в запросы с соединениями.

Пример 18.4. Найти номера, имена, номера отделов и имена руководителей отделов служащих, размер заработной платы которых меньше 15000 руб.

SELECT EMP1.EMP_NO, EMP1.EMP_NAME,

EMP1.DEPT_NO, EMP2.EMP_NAME

FROM EMP AS EMP1, EMP AS EMP2, DEPT

WHERE EMP1.EMP_SAL < 15000.00 AND

EMP1.DEPT_NO = DEPT.DEPT_NO AND

DEPT.DEPT_MNG = EMP2.EMP_NO;

Этот запрос представляет собой эквисоединение ограничения таблицыEMP(по условиюEMP_SAL < 15000.00) с таблицамиDEPTиEMP(по условиямEMP.DEPT_NO = DEPT.DEPT_NOиDEPT.DEPT_MNG = EMP2.EMP_NOсоответственно). ТаблицаEMPучаствует в качестве операнда операции эквисоединения два раза. Поэтому в разделеFROMей присвоены два псевдонима –EMP1иEMP2. Следуя предписанному стандартом порядку выполнения запроса, можно считать, что введение этих псевдонимов обеспечивает переименование столбцов таблицыEMP, требуемое для выполнения разделаFROMс образованием расширенного декартова произведения таблиц-операндов.¹³⁷⁾Заметим также, что в данном случае мы имеем дело сполнымэквисоединением трех таблиц (а не с полусоединением), поскольку в списке выборки присутствуют имена столбцов каждой из них.

Покажем способ формулировки этого запроса с использованием вложенного подзапроса в качестве элемента списка выборки (пример 18.4.1):

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO,

(SELECT EMP_NAME

FROM EMP

WHERE EMP_NO = DEPT_MNG)

FROM EMP, DEPT

WHERE EMP.EMP_SAL < 15000.00 AND

EMP.DEPT_NO = DEPT.DEPT_NO;

Как показывает последний пример, в условии выборки подзапроса, участвующего в списке выборки, можно использовать имена столбов таблиц внешнего запроса. Из этой возможности языка SQL видно, что в подразделе 17.2.1. «Общие синтаксические правила построения скалярных выражений»предыдущей лекции для облегчения понимания

137 Кстати, в этом случае можно было бы обойтись введением одного псевдонима, оставив в качестве неявного второго псевдонима имя таблицы – EMP.

материала мы немного исказили семантику оператора выборки. Там было сказано следующее: «После выполнения раздела WHERE(если в запросе отсутствуют разделыGROUP BYиHAVING, случай (a)) или выполнения явно или неявно заданного разделаHAVING(случай (b)) выполняется разделSELECT. При выполнении этого раздела на основе таблицыT1в случае (a) или на основе сгруппированной таблицыT3в случае (b) строится таблицаT4, содержащая столько строк, сколько строк или групп строк содержится в таблицахT1илиT3соответственно». В действительности, в общем случае очередная строка таблицыT4должна строиться в тот момент, когда очередная строка или группа строк заносится в таблицуT1илиT3соответственно.