5. Лекция: Группировка и условия раздела HAVING, порождаемые и соединенные таблицы

В этой лекции мы завершаем обсуждение основных (традиционных) конструкций оператора SELECT языка SQL. В разделе "Агрегатные функции, группировка и условия раздела HAVING" обсуждаются разделы GROUP BY и HAVING. Основной акцент делается на способах конструирования условий раздела HAVING. На примерах демонстрируется, что разделы GROUP BY и HAVING действительно полезны, а иногда и необходимы при формулировке запросов с вызовами агрегатных функций. В разделах "Ссылки на порождаемые таблицы в разделе FROM" и "Более сложные конструкции оператора выборки" мы возвращаемся к разновидностям ссылок на таблицу в разделе FROM и последовательно обсуждаем порождаемые таблицы, соединенные таблицы и порождаемые таблицы с горизонтальной связью.

Введение

В предыдущих двух лекциях мы обсудили допускаемые в стандарте SQL виды ссылок на таблицы в разделе FROM оператора SELECT и подробно, с многочисленными примерами, рассмотрели возможные способы построения условных выражений раздела WHERE. Данную лекцию мы начинаем с анализа возможностей и целесообразности использования в запросах разделов GROUP BY и HAVING. Соответствующий раздел "Агрегатные функции, группировка и условия раздела HAVING" формально похож на раздел "Логические выражения раздела WHERE" лекции 14: обсуждаются виды предикатов, которые можно использовать в условных выражениях раздела HAVING, и приводятся иллюстрирующие примеры. Но в действительности мы преследуем большую цель: показать, что во многих случаях разделы GROUP BY и HAVING являются избыточными; запрос можно сформулировать более понятным образом без их использования. Применение разделов GROUP BY и HAVING оказывается действительно полезным, а иногда и необходимым, в тех случаях, когда в запросе присутствует несколько вызовов агрегатных функций на группах строк.

После обсуждения разделов GROUP BY и HAVING можно будет считать, что мы полностью рассмотрели базовые конструкции оператора выборки (раздел ORDER BY не заслуживает дополнительного обсуждения). Поэтому в разделах "Ссылки на порождаемые таблицы в разделе FROM" и "Более сложные конструкции оператора выборки" мы возвращаемся к отложенным в лекции 13 темам порождаемых таблиц, соединенных таблиц и порождаемых таблиц с горизонтальной связью.

В обычных порождаемых таблицах SQL нет ничего особенного. По всей видимости, возможность указывать в разделе FROM выражения запросов, а не только ссылки на базовые или представляемые таблицы, была введена в SQL на основе следующих естественных соображений. Результатом вычисления выражения запросов в SQL является таблица. Следовательно, в любой конструкции языка, где может присутствовать ссылка на таблицу SQL, следует допустить присутствие выражения запросов. Одновременное наличие возможностей определения представляемых таблиц, указания именованного выражения запросов в разделе WITH и указания выражения запросов порождаемой таблицы непосредственно в списке раздела FROM, очевидно, является избыточным.

Соединенные таблицы появились еще в стандарте SQL/92, и внедрение в стандарт SQL этой возможности было действительно обоснованным. В соответствии с традиционной общей семантикой оператора SELECT в нем вообще не предусматривалось явных средств для выражения потребности в соединении двух или более таблиц. Наличие возможности указывать несколько ссылок на таблицы в разделе FROM и спецификации произвольного логического выражения в разделе WHERE для ограничения расширенного декартова произведения этих таблиц позволяет выражать с помощью традиционных средств SQL соединение общего вида в смысле Кодда, и до поры до времени это считалось достаточным.

Внешние соединения

Но имеются два важных частных случая соединений, которые выражаются с помощью традиционных средств SQL излишне громоздко,- это естественные и внешние соединения. При наличии возможности определения внешних ключей таблицы кажется достаточно странной потребность всякий раз явно указывать в запросах условие естественного соединения. Например, во многих примерах запросов в лекции 14 присутствует условие соединения EMP.DEPT_NO = DEPT.DEPT_NO в тех случаях, когда в действительности нам требовался результат операции EMP NATURAL JOIN DEPT.

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

Пусть имеются отношения r1 и r2, совместимые относительно операции взятия расширенного декартова произведения. Пусть s является результатом операции r1 LEFT OUTER JOUN r2 WHERE comp (левое внешнее соединение r1 и r2 по условию comp). Тогда Hs = Hr1 union Hr2. Пусть tr1 Br1 и tr2 Br2. Тогда tr1 union tr2 Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr1 Br1, для которого нет ни одного кортежа tr2 r2, такого, что comp (tr1 union tr2) = true, то tr1 union tr2null Bs, где tr2null - кортеж, соответствующий Hr2, все значения которого являются неопределенными1).

Пусть s является результатом операции r1 RIGHT OUTER JOUN r2 WHERE comp (правое внешнее соединение r1 и r2 по условию comp). Тогда Hs = Hr1 union Hr2. Пусть tr1 Br1 и tr2 Br2. Тогда tr1 union tr2 Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr2 Br2, для которого нет ни одного такого кортежа tr1 Br1, что comp (tr1 union tr2) = true, то tr1null union tr2 Bs, где tr1null - кортеж, соответствующий Hr1, все значения которого являются неопределенными.

Наконец, пусть s является результатом операции r1 FULL OUTER JOUN r2 WHERE comp (полное внешнее соединение r1 и r2 по условию comp). Тогда Hs = Hr1 union Hr2. Пусть tr1 Br1 и tr2 Br2. Тогда tr1 union tr2 Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr1 Br1, для которого нет ни одного кортежа tr2 Br2, такого, что comp (tr1 union tr2) = true, то tr1 union tr2null Bs, где tr2null - кортеж, соответствующий Hr2, все значения которого являются неопределенными. Если имеется кортеж tr2 Br2, для которого нет ни одного кортежа tr1 Br1, такого, что comp (tr1 union tr2) = true, то tr1null union tr2 Bs, где tr1null - кортеж, соответствующий Hr1, все значения которого являются неопределенными.

Понятно, что традиционными средствами SQL можно выразить все виды внешних соединений (например, с использованием переключателей), но такие запросы будут очень громоздкими. Компании-производители SQL-ориентированных СУБД пытались обеспечивать выразительные средства внешних соединений путем расширения системы обозначений для операций сравнения. Этот подход был не слишком удачным и не обеспечивал общего решения.

В стандарте языка SQL специфицирован отдельный специализированный подъязык для формирования выражений соединения таблиц. Такие выражения называются соединенными таблицами, и их можно использовать в качестве ссылок на таблицы в списке раздела FROM. Разработчики стандарта SQL не любят мельчить - в языке допускается 14 видов соединений:

  • прямое соединение;
  • внутреннее соединение по условию;
  • внутреннее соединение по совпадению значений указанных одноименных столбцов;
  • естественное внутреннее соединение;
  • левое внешнее соединение по условию;
  • правое внешнее соединение по условию;
  • полное внешнее соединение по условию;
  • левое внешнее соединение по совпадению значений указанных одноименных столбцов;
  • правое внешнее соединение по совпадению значений указанных одноименных столбцов;
  • полное внешнее соединение по совпадению значений указанных одноименных столбцов;
  • естественное левое внешнее соединение;
  • естественное правое внешнее соединение;
  • естественное полное внешнее соединение;
  • соединение объединением.

Во всех этих операциях нет ничего сложного, но их неформальное описание исключительно громоздко. Поэтому в разделе "Более сложные конструкции оператора выборки" мы определяем операции на формальном уровне, а потом иллюстрируем их на примерах.

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

Агрегатные функции, группировка и условия раздела HAVING

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

Семантика агрегатных функций

Агрегатные функции (в стандарте SQL они называются функциями над множествами)2) определяются следующими синтаксическими правилами:

<set_function_specification> ::=
     COUNT(*) 
   | set_function_type ([DISTINCT | ALL ] value_expression) 
   | GROUPING (column_reference)
<set_function_type> ::= 
   { AVG | MAX | MIN | SUM | EVERY | ANY | SOME | COUNT } 

Как видно из этих правил, в стандарте SQL:1999 определены пять стандартных агрегатных функций: COUNT - число строк или значений, MAX - максимальное значение, MIN - минимальное значение, SUM - суммарное значение и AVG - среднее значение, а также две "кванторные" функции EVERY и SOME (ANY). В последних двух случаях выражение должно иметь булевский тип. Обсуждение функции GROUPING мы отложим до следующей лекции.

Агрегатные функции предназначены для того, чтобы вычислять некоторое значение для заданного мультимножества строк. Таким мультимножеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или (в вырожденных случаях) вся таблица. Для всех агрегатных функций, кроме COUNT(*), фактический (т. е. требуемый семантикой) порядок вычислений состоит в следующем. На основании параметров агрегатной функции из заданного мультимножества строк производится список значений. Затем по этому списку значений производится вычисление функции. Если список оказался пустым, то значением функции COUNT для него является 0, значением функции SOME - false, значением функции ALL - true, а значением всех остальных функций - NULL.

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

  • Результат вычисления функции COUNT - это точное число с точностью и шкалой, которые определяются в реализации.
  • Тип результата значений функций MAX и MIN совпадает с T. При вычислении функций SUM и AVG тип T не должен быть типом символьных строк.
    • Если T представляет собой тип точных чисел, то и типом результата функции является тип точных чисел с определяемыми в реализации точностью и шкалой.
    • Если T представляет собой тип приблизительных чисел, то и типом результата функции является тип приблизительных чисел с определяемыми в реализации точностью.
  • Для функций EVERY и SOME T является булевским типом.
    • Первая функция принимает значение true в том и только в том случае, когда вычисление выражения-аргумента дает значение true для каждой строки из заданного набора строк; false - в том и только в том случае, когда значение выражения-аргумента есть false хотя бы для одной строки из заданного набора строк и uknown - во всех остальных случаях.
    • Функция SOME принимает значение false в том и только в том случае, когда значение выражения-аргумента есть false для каждой строки из заданного набора строк; true - в том и только в том случае, когда значение выражения-аргумента есть true хотя бы для одной строки из заданного набора строк; uknown - во всех остальных случаях.

Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном мультимножестве. Все строки считаются различными, даже если они состоят из одного столбца со значением null во всех строках.3)

Если "арифметическая" (AVG, MAX, MIN, SUM, COUNT) агрегатная функция специфицирована с ключевым словом DISTINCT, то множество значений, на котором она вычисляется, строится из значений указанного выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и в нем устраняются значения-дубликаты (т. е. образуется множество). После этого вычисляется указанная функция.

Если агрегатная функция специфицирована без ключевого слова DISTINCT (или с ключевым словом ALL), то мультимножество значений формируется из значений выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и производится вычисление агрегатной функции.

Результаты запросов и агрегатные функции

Об использовании агрегатных функций в разделах HAVING и SELECT оператора выборки упоминалось в разделе "Общие синтаксические правила построения скалярных выражений" лекции 13. В данном подразделе уместно повторить и уточнить этот материал.

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

Если результат табличного выражения R не является сгруппированной таблицей (т. е. в табличном выражении отсутствуют разделы GROUP BY и HAVING), то появление в списке выборки хотя бы одного вызова агрегатной функции от (мульти) множества строк R приводит к тому, что R неявно рассматривается как сгруппированная таблица, состоящая из одной (или нуля, если R пусто) групп с отсутствующими столбцами группирования. Поэтому в данном случае в выражениях списка выборки не допускается прямое использование имен столбцов R: все они должны находиться внутри спецификаций вызова агрегатных функций. Результатом запроса является таблица, состоящая не более чем из одной строки, значения столбцов которой получены путем применения агрегатных функций к R.

Аналогично обстоит дело в том случае, когда R представляет собой сгруппированную таблицу, но табличное выражение не содержит раздела GROUP BY (и, следовательно, содержит раздел HAVING). В этом случае считается, что результат табличного выражения явно объявлен сгруппированной таблицей, состоящей из одной группы, и результат запроса можно формировать только путем применения агрегатных функций к данной группе строк. Опять результатом запроса является таблица, состоящая не более чем из одной строки, значения столбцов которой получены путем применения агрегатных функций к R.

Наконец, рассмотрим случай, когда R представляет собой "настоящую" сгруппированную таблицу, т. е. табличное выражение содержит раздел GROUP BY, и, следовательно, определен по крайней мере один столбец группирования (т. е. имеется хотя бы один такой столбец, что для любой группы его значения одинаковы во всех строках группы). В этом случае правила формирования списка выборки полностью соответствуют правилам формирования условия выборки раздела HAVING. Другими словами, в выражениях, являющихся элементами списка выборки, допускается прямое использование имен столбцов группирования, а спецификации остальных столбцов R могут появляться только внутри спецификаций агрегатных функций. Результатом запроса является таблица, число строк в которой равно числу групп в R. Значения столбцов каждой строки формируются на основе значений столбцов группирования и вызовов агрегатных функций для соответствующей группы.

Логические выражения раздела HAVING

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

Предикаты сравнения
SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING COUNT(*) = 30;
Пример 15.1. Найти номера отделов, в которых работает ровно 30 сотрудников.

Конечно, этот запрос можно сформулировать и без использования разделов GROUP BY и HAVING. Например, возможна следующая формулировка (пример 15.1.1):

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT COUNT (*)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO) = 30;
Пример 15.1.1.

Обратите внимание, что в формулировке 15.1.1 отдельная проверка условия DEPT_NO IS NOT NULL не требуется.

   
SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG(EMP_SAL) > 12000.00;
Пример 15.2. Найти номера всех отделов, в которых средний размер зарплаты сотрудников превосходит 12000 руб.

Очевидно, что и в этом случае возможна формулировка запроса без использования разделов GROUP BY и HAVING (пример 15.2.1):

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT AVG(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO) > 12000.00;
Пример 15.2.1.

Немного задержимся на этих примерах и обсудим, что означает различие в формулировках запросов. В соответствии с семантикой оператора SELECT, при выполнении запросов 15.1.1 и 15.2.1 для каждой строки таблицы EMP в цикле просмотра внешнего запроса будет выполняться подзапрос, который в случае наших примеров выберет из таблицы EMP (EMP1) все строки со значением столбца DEPT_NO, равным значению этого столбца в текущей строке внешнего цикла. Другими словами, для каждой строки внешнего цикла образуется группа, для нее проверяется условие выборки, и в списке выборки используется имя столбца этой неявной группировки. Из-за того, что группа образуется и оценивается для каждой строки таблицы EMP, мы вынуждены указать в разделе SELECT спецификацию DISTINCT.

Формулировки 15.1 и 15.2 обеспечивают более четкие указания для выполнения запроса. Нужно сразу сгруппировать таблицу EMP в соответствии со значениями столбца DEPT_NO, отобрать нужные группы, и для каждой отобранной группы вычислить значения выражений списка выборки. В этом случае семантика выполнения запроса не предписывает выполнения лишних действий. Конечно, в развитой реализации SQL компилятор должен суметь понять, что формулировки 15.1.1 и 15.2.1 эквивалентны формулировкам 15.1 и 15.2 соответственно, и избежать выполнения лишних действий.

SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING SUM(EMP_SAL) < (SELECT SUM(EMP1.EMP_SAL)
   FROM EMP EMP1, DEPT
   WHERE EMP1.EMP_NO = DEPT_MNG); 
Пример 15.3. Найти номера всех отделов, в которых суммарный объем зарплаты сотрудников меньше суммарного объема зарплаты всех руководителей отделов.

И в этом случае возможна формулировка без использования разделов GROUP BY и HAVING (пример 15.3.1). Эта формулировка является более сложной, чем в случае двух предыдущих примеров, но и к ней применимы приведенные выше замечания.

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT SUM(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO) < 
   (SELECT SUM(EMP1.EMP_SAL)
   FROM EMP EMP1, DEPT
   WHERE EMP1.EMP_NO = DEPT_MNG);
Пример 15.3.1.
SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), 
   MIN(EMP1.EMP_SAL), MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO
GROUP BY DEPT.DEPT_NO, DEPT.DEPT_MNG, EMP.EMP_NO, EMP.EMP_NAME
HAVING DEPT.DEPT_MNG = EMP.EMP_NO;
Пример 15.4. Для каждого отдела найти его номер, имя руководителя, число сотрудников, минимальный, максимальный и средний размеры зарплаты сотрудников.

Этот запрос иллюстрирует несколько интересных особенностей языка SQL. Во-первых, это первый пример запроса с соединениями, в котором присутствуют разделы GROUP BY и HAVING. Во-вторых, одно условие соединения находится в разделе WHERE, а другое - в разделе HAVING. На самом деле, можно было бы перенести в раздел WHERE и второе условие соединения, и, скорее всего, на практике использовалась бы формулировка, приведенная в примере 15.4.1:

SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), 
   MIN(EMP1.EMP_SAL), MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO 
   AND DEPT.DEPT_MNG = EMP.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP.EMP_NAME;
Пример 15.4.1.

Но первая формулировка тоже верна, поскольку второе условие соединения определено на столбцах группировки.

Наконец, легко видеть, что по существу группировка производится по значениям столбца DEPT.DEPT_NO. Остальные столбцы, указанные в списке столбцов группировки, функционально определяются столбцом DEPT.DEPT_NO. Тем не менее, в первой формулировке мы включили в этот список столбцы DEPT.DEPT_MNG и EMP.EMP_NO, чтобы их имена можно было использовать в условии раздела HAVING, и столбец EMP.EMP_NAME, чтобы можно было использовать его имя в списке выборки раздела SELECT. Другими словами, мы вынуждены расширять запрос избыточными данными, чтобы выполнить формальные синтаксические требования языка. Как видно, во второй формулировке мы смогли удалить из списка группировки два столбца. Кстати, не следует думать, что многословие первой формулировки помешает СУБД выполнить запрос настолько же эффективно, как запрос во второй формулировке. Грамотно построенный оптимизатор SQL сам приведет первую формулировку ко второй.

Наконец, и этот запрос можно сформулировать без использования раздела GROUP BY за счет использования подзапросов в списке раздела SELECT (пример 15.4.2):

SELECT DEPT.DEPT_NO, EMP.EMP_NAME, 
   (SELECT COUNT(*)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
   (SELECT MIN(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
   (SELECT MAX(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
   (SELECT AVG(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO)
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO;
Пример 15.4.2.

Здесь мы снова имеем замаскированную группировку строк по значениям столбца DEPT.DEPT_NO и вычисление агрегатных функций для каждой группы. Формально группа строится каждый раз заново при вызове каждой агрегатной функции. Хороший компилятор SQL должен привести формулировку 15.4.2 к виду 15.4.1.

И последнее замечание. Во всех приведенных формулировках в результат не попадут данные об отделах, в которых отсутствует руководитель (столбец DEPT.DEPT_MNG может содержать неопределенное значение). Вообще говоря, это не противоречит условию запроса, но если бы мы хотели выдавать в результате NULL в качестве имени руководителя отдела с отсутствующим руководителем, то можно было немного усложнить формулировку запроса, например, следующим образом (пример 15.4.3):

         
SELECT DEPT.DEPT_NO, 
   CASE WHEN DEPT.DEPT_MNG IS NULL THEN NULL 
     ELSE (SELECT EMP.EMP_NAME
       FROM EMP
       WHERE EMP.EMP_NO = DEPT.DEPT_MNG),
     COUNT(*), MIN(EMP1.EMP_SAL),
     MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO
GROUP BY DEPT.DEPT_NO;
Пример 15.4.3.
Предикат between
SELECT DEPT_NO, MIN(EMP_SAL), MAX(EMP_SAL)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG(EMP_SAL) BETWEEN
   (SELECT AVG(EMP_SAL)
   FROM EMP) AND 30000.00;
Пример 15.5. Найти номера отделов и минимальный и максимальный размер зарплаты сотрудников для отделов, в которых средний размер зарплаты сотрудников не меньше среднего размера зарплаты сотрудников во всей компании и не больше 30000 руб.

Еще раз приведем возможную формулировку этого запроса без использования разделов GROUP BY и HAVING (пример 15.5.1):

          SELECT DISTINCT DEPT_NO, (SELECT MIN(EMP1.EMP_SAL)    FROM EMP EMP1    WHERE EMP1.DEPT_NO = EMP.DEPT_NO),    (SELECT MAX(EMP1.EMP_SAL)    FROM EMP EMP1    WHERE EMP1.DEPT_NO = EMP.DEPT_NO) FROM EMP WHERE (SELECT AVG(EMP1.EMP_SAL)      FROM EMP EMP1      WHERE EMP1.DEPT_NO = EMP.DEPT_NO) BETWEEN    (SELECT AVG(EMP_SAL)      FROM EMP) AND 30000.00; 
Пример 15.5.1.

Как видно, отказ от использования раздела GROUP BY приводит к размножению однотипных подзапросов, в каждом из которых строится одна и та же группа строк, над которой вычисляется агрегатная функция.

Предикат null
SELECT DEPT.DEPT_NO, COUNT(*) FROM DEPT, EMP EMP1, EMP EMP2 WHERE DEPT.DEPT_NO = EMP2.DEPT_NO  AND DEPT.DEPT_MNG = EMP1.EMP_NO GROUP BY DEPT.DEPT_NO, EMP1.DEPT_NO HAVING EMP1.DEPT_NO IS NULL; 
Пример 15.6. Найти номера и число сотрудников отделов, данные о руководителях которых не содержат номер отдела (конечно, в этом случае нас интересуют только те отделы, у которых имеется руководитель).

Как и в примере 15.4, условие раздела HAVING можно переместить в раздел WHERE и получить вторую формулировку (пример 15.6.1):

SELECT DEPT.DEPT_NO, COUNT(*)
FROM DEPT, EMP EMP1, EMP EMP2
WHERE DEPT.DEPT_NO = EMP2.DEPT_NO AND
   DEPT.DEPT_MNG = EMP1.EMP_NO AND
   EMP1.DEPT_NO IS NULL
GROUP BY DEPT.DEPT_NO;
Пример 15.6.1.

Кстати, в этом случае, поскольку в запросе присутствует только один вызов агрегатной функции, формулировка без использования раздела GROUP BY оказывается более понятной и не менее эффективной (даже при следовании предписанной семантике выполнения оператора SELECT), что показывает пример 15.6.2:

SELECT DEPT.DEPT_NO, (SELECT COUNT(*)
   FROM EMP
   WHERE DEPT.DEPT_NO = EMP.DEPT_NO)
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND
      EMP.DEPT_NO IS NULL;
Пример 15.6.2.
Предикат in
SELECT DEPT.DEPT_NO
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING AVG(EMP.EMP_SAL) IN
   (SELECT MAX(EMP1.EMP_SAL)
     FROM EMP, DEPT DEPT1
     WHERE EMP.DEPT_NO = DEPT1.DEPT_NO
     AND DEPT1.DEPT_NO <> DEPT.DEPT_NO
     GROUP BY DEPT.DEPT_NO);
Пример 15.7. Найти номера отделов, в которых средний размер зарплаты сотрудников равен максимальному размеру зарплаты сотрудников какого-либо другого отдела.

Этот запрос, помимо прочего, демонстрирует наличие в условии раздела HAVING вложенного подзапроса с корреляцией. Как и раньше, можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 15.7.1):

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE (SELECT AVG(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO) IN
   (SELECT MAX(EMP1.EMP_SAL)
   FROM EMP, DEPT DEPT1
   WHERE EMP.DEPT_NO = DEPT1.DEPT_NO
     AND DEPT1.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY DEPT.DEPT_NO);
Пример 15.7.1.

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

Предикат like
SELECT EMP_NAME, COUNT(*)
FROM EMP, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO 
GROUP BY DEPT.DEPT_NO, EMP_NAME
HAVING COUNT(*) > 1 
   AND EMP.EMP_NAME LIKE (SELECT EMP1.EMP_NAME
     FROM EMP EMP1
     WHERE EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';
Пример 15.8. Во всех отделах найти имена и число сотрудников, у которых в данном отделе имеются однофамильцы и фамилии которых начинаются со строки символов, изображающей фамилию руководителя отдела.

Конечно, и в этом случае условие с предикатом LIKE можно переместить из раздела HAVING в раздел WHERE. Этот запрос можно переформулировать в виде, лишенном разделов GROUP BY и HAVING (пример 15.8.1), но вряд ли это разумно, поскольку формулировка является менее понятной и существенно более сложной.

SELECT EMP_NAME, (SELECT COUNT(*)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO
     AND EMP1.EMP_NAME = EMP.EMP_NAME
     AND EMP1.EMP_NO <> EMP.EMP_NO) + 1
FROM EMP
WHERE (SELECT COUNT(*)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO
     AND EMP1.EMP_NAME = EMP.EMP_NAME
     AND EMP1.EMP_NO <> EMP.EMP_NO) > 1 
 AND EMP_NAME LIKE (SELECT EMP1.EMP_NAME
   FROM EMP EMP1, DEPT
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO
   AND EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';
Пример 15.8.1.
Предикат exists
SELECT DEPT.DEPT_NO
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING EXISTS (SELECT *
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY EMP1.DEPT_NO 
   HAVING MAX (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));
Пример 15.9. Найти номера отделов, в которых средний размер зарплаты сотрудников равен максимальному размеру зарплаты сотрудников какого-либо другого отдела (другая формулировка для примера 15.7).

В этой формулировке основной интерес представляет подзапрос, в котором корреляция с внешним запросом происходит через вызов агрегатной функции от группы строк внешнего запроса. Здесь также можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 15.9.1):

         
SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS (SELECT EMP.DEPT_NO
   FROM EMP
   WHERE EMP.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY EMP.DEPT_NO 
   HAVING MAX (EMP.EMP_SAL)= 
     (SELECT AVG (EMP1.EMP_SAL)
       FROM EMP EMP1
       WHERE EMP1.DEPT_NO = DEPT.DEPT_NO));
Пример 15.9.1.
Предикат unique
SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING UNIQIUE (SELECT AVG (EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO IS NOT NULL
   GROUP BY EMP1.DEPT_NO 
   HAVING AVG (EMP1.EMP_SAL) = AVG (EMP.EMP_SAL));
Пример 15.10. Найти номера отделов и средний размер зарплаты сотрудников для таких отделов, где средний размер зарплаты сотрудников отличается от среднего размера зарплаты всех других отделов.

Вот альтернативная формулировка этого запроса с использованием предиката NOT EXISTS (пример 15.10.1):

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING NOT EXISTS (SELECT EMP1.DEPT_NO
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY EMP1.DEPT_NO 
   HAVING AVG (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));
Пример 15.10.1.
Предикаты сравнения с квантором
SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG (CURRENT_DATE - EMP_BDATE)< SOME
   (SELECT AVG (CURRENT_DATE - EMP1.EMP_BDATE) 
     FROM EMP EMP1
     WHERE EMP1.DEPT_NO IS NOT NULL
     GROUP BY EMP1.DEPT_NO);
Пример 15.11. Найти номера отделов и средний возраст сотрудников для таких отделов, что найдется хотя бы один другой отдел, средний возраст сотрудников которого больше, чем в данном.

Напомним, что "ниладическая" функция CURRENT_DATE выдает текущую дату, и, следовательно, значением выражения CURRENT_DATE - EMP_BDATE является интервал, представляющий текущий возраст сотрудника. На наш взгляд, формулировка этого запроса несколько упрощается, если пользоваться предикат предикатом EXISTS (пример 15.11.1):

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING EXISTS (SELECT EMP1.DEPT_NO
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO IS NOT NULL
   GROUP BY EMP1.DEPT_NO
   HAVING AVG (CURRENT_DATE - EMP1.EMP_BDATE) >
     AVG (CURRENT_DATE - EMP.EMP_BDATE));
Пример 15.11.1.
SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG (CURRENT_DATE - EMP_BDATE) <= ALL
   (SELECT AVG (CURRENT_DATE - EMP_BDATE)
     FROM EMP
     WHERE DEPT_NO IS NOT NULL
     GROUP BY DEPT_NO);
Пример 15.12. Найти номера отделов и средний возраст сотрудников для отделов с минимальным средним возрастом сотрудников.

Этот запрос легко формулируется в более понятном виде с использованием предиката NOT EXISTS (пример 15.12.1):

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING NOT EXISTS (SELECT EMP1.DEPT_NO
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO IS NOT NULL
   GROUP BY EMP1.DEPT_NO
   HAVING AVG (CURRENT_DATE - EMP1.EMP_BDATE) <
     AVG (CURRENT_DATE - EMP.EMP_BDATE));
Пример 15.12.1.
Предикат distinct
SELECT DEPT.DEPT_NO
FROM DEPT, EMP EMP1, EMP EMP2
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND
   DEPT.DEPT_MNG = EMP2.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE 
HAVING (EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)) DISTINCT FROM
   (SELECT EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)
     FROM DEPT DEPT1, EMP EMP1, EMP EMP2
     WHERE DEPT1.DEPT_NO = EMP1.DEPT_NO AND
     DEPT1.DEPT_MNG = EMP2.EMP_NO AND
     DEPT1.DEPT_NO <> DEPT.DEPT_NO
     GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE);
Пример 15.13. Найти номера отделов, которые можно отличить от любого другого отдела по дате рождения руководителя и среднему размеру зарплаты.

Ссылки на порождаемые таблицы в разделе FROM

В этом разделе мы приведем несколько примеров запросов, в разделе FROM которых содержатся выражения запросов (ссылки на порождаемые таблицы, см. раздел "Общие синтаксические правила построения скалярных выражений" лекции 13).

Еще один способ формулировки запросов

Прежде всего, на простом примере покажем, как использование ссылок на порождаемые таблицы расширяет возможности формулировки запросов.

SELECT MNG.DEPT_NO, MNG.MNG_NAME
FROM (SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL
   FROM DEPT, EMP
   WHERE DEPT.DEPT_MNG = EMP.EMP_NO) 
   AS MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL)
WHERE DEPT_NO_1 = DEPT_NO_2
   AND MNG_SAL = (SELECT MAX (EMP_SAL)
     FROM EMP
     WHERE EMP.DEPT_NO = DEPT_NO_1);
Пример 15.14. Найти номера отделов и имена руководителей отделов, которые числятся в тех же отделах, которыми руководят, и получают зарплату, размер которой является максимальным для сотрудников данного отдела.

В этом запросе порождаемая таблица MNG содержит по одной строке для каждого служащего, являющегося руководителем отдела. Первый столбец этой таблицы - DEPT_NO_1 - содержит номер отдела, которым руководит данный служащий. В столбце DEPT_NO_1 хранятся номера отделов, в которых числятся руководители отделов, а в столбцах EMP_NAME и EMP_SAL содержатся имя служащего-руководителя отдела и размер его заработной платы соответственно.

Конечно, этот запрос можно сформулировать и без использования ссылки на порождаемую таблицу в разделе FROM, например, следующим образом (пример 15.14.1):

SELECT DEPT.DEPT_NO, EMP.EMP_NAME
FROM DEPT, EMP 
   WHERE DEPT.DEPT_MNG = EMP.EMP_NO
     AND DEPT.DEPT_NO = EMP.DEPT_NO
     AND EMP.EMP_SAL = (SELECT MAX(EMP_SAL)
       FROM EMP
       WHERE EMP.DEPT_NO = DEPT.DEPT_NO);
Пример 15.14.1.

А вот как можно сформулировать тот же запрос с использованием раздела WITH (пример 15.14.2):

WITH MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL) AS
   (SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL
   FROM DEPT, EMP
   WHERE DEPT.MNG_NO = EMP.EMP_NO),
   MAX_DEPT_SAL (MAX_SAL, DEPT_NO) AS
   (SELECT MAX (EMP_SAL), DEPT_NO
   FROM EMP
   WHERE DEPT_NO IS NOT NULL
   GROUP BY DEPT_NO)
SELECT DEPT_NO_1, MNG_NAME
FROM MNG
WHERE DEPT_NO_1 = DEPT_NO_2
   AND MNG_SAL = (SELECT MAX_SAL
     FROM MAX_DEPT_SAL
     WHERE MAX_DEPT_SAL.DEPT_NO = DEPT_NO_1);
Пример 15.14.2.

Случаи, в которых без порождаемых таблиц обойтись невозможно

На самом деле, пример 15.14 демонстрирует лишь возможность альтернативных формулировок запросов с использованием ссылок на порождаемые таблицы в разделе FROM. Но в некоторых случаях без подобных конструкций просто невозможно обойтись. Вот простой пример.

SELECT SUM (TOTAL_EMP), MAX_SAL
FROM (SELECT MAX (EMP_SAL), COUNT (*)
   FROM EMP
   WHERE DEPT_NO IS NOT NULL
   GROUP BY DEPT_NO ) AS DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP)
GROUP BY MAX_SAL;
Пример 15.15. Найти общее число сотрудников и максимальный размер зарплаты в отделах с одинаковым максимальным размером зарплаты.

И в этом случае выражение запросов, содержащееся в разделе FROM, можно перенести в раздел WITH (пример 15.15.1):

WITH DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) AS
   (SELECT MAX (EMP_SAL), COUNT (*)
     FROM EMP
     WHERE DEPT_NO IS NOT NULL
     GROUP BY DEPT_NO)
SELECT SUM (TOTAL_EMP), MAX_SAL
FROM DEPT_MAX_SAL
GROUP BY MAX_SAL;
Пример 15.15.1.

Здесь мы не можем обойтись "одноуровневой" конструкцией запроса, поскольку требуется двойная группировка, причем вторая группировка должна быть получена в соответствии с результатами первой. Еще один пример.

SELECT COUNT (*), PRO_EDATE, AVG_SAL
FROM (SELECT PRO_EDATE, AVG (EMP_SAL)
   FROM (SELECT PRO_SDATE + PRO_DURAT, PRO_NO
     FROM PRO) AS PRO1 (PRO_EDATE, PRO_NO), EMP
   WHERE PRO1.PRO_NO = EMP.PRO_NO
   GROUP BY PRO1.PRO_NO ) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL)
GROUP BY PRO_EDATE, AVG_SAL;
Пример 15.16. Найти число проектов, дату их завершения и средний размер зарплаты сотрудников, участвующих в проекте, для проектов с одной и той же датой завершения и одним и тем же средним размером зарплаты сотрудников, участвующих в проекте.

Заметим, что выражение запросов на третьей и четвертой строках примера необходимо только по той причине, что нам требуется группировка по дате окончания проектов, соответствующий столбец в таблице PRO отсутствует, а в списке группировки можно использовать только имена столбцов. Для упрощения вида формулировки это выражение разумно вынести в раздел WITH (пример 15.16.1):

WITH PRO1 (PRO_EDATE, PRO_NO) AS 
   (SELECT PRO_SDATE + PRO_DURAT, PRO_NO
     FROM PRO)
SELECT COUNT (*), PRO_EDATE, AVG_SAL
FROM (SELECT PRO_EDATE, AVG (EMP_SAL)
   FROM PRO1, EMP
   WHERE PRO1.PRO_NO = EMP.PRO_NO
   GROUP BY PRO1.PRO_NO) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL)

GROUP BY PRO_EDATE, AVG_SAL;
Пример 15.16.1.

Более сложные конструкции оператора выборки

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

Соединенные таблицы

В примерах предыдущей и данной лекций присутствовало много запросов с соединениями двух или более таблиц. Условия соединения задавались предикатами сравнения столбцов таблиц, специфицированных в разделе FROM, и входили в состав логических выражений раздела WHERE (или, реже, раздела HAVING). Поскольку на практике требуются разные виды соединений, в стандарте SQL/92 появилась альтернативная возможность спецификации соединений - соединенная таблица (joined table). Соответствующая конструкция может использоваться в разделе FROM выражения запросов и фактически позволяет строить выражения соединений таблиц. Синтаксические правила построения таких выражений выглядят следующим образом:

joined_table ::= cross_join
   | qualified_join
   | natural_join
   | union_join
cross_join ::= table_reference CROSS JOIN table_primary
qualified_join ::= table_reference [ join_type ] JOIN 
   table_primary join_specification
natural_join ::= table_reference NATURAL [ join_type ] 
   JOIN table_primary
union_join ::= table_reference UNION JOIN table_primary
join_type ::= INNER | { LEFT | RIGHT | FULL } [ OUTER ]
join_specification ::= ON conditional_expression
   | USING (column_comma_list)

Напомним, что синтаксические правила для table_reference и table_primary были показаны в лекции 13.

Как показывает сводка синтаксических правил, в SQL поддерживается много вариантов соединений. Чтобы объяснить особенности разных видов соединений на неформальном уровне, требуется очень большой объем текста с большим числом повторений. Поэтому сначала мы приведем достаточно формальное описание порядка определения заголовка и тела результирующей таблицы для всех разновидностей соединений. Фактически это описание напрямую позаимствовано из стандарта SQL:1999 с некоторыми незначительными упрощениями. Затем мы представим ряд иллюстрирующих примеров.

Формальные определения

Пусть требуется выполнить некоторую операцию соединения над таблицами table1 и table2. Тогда:

  • Обозначим через CP результат выполнения запроса4)
    SELECT *
    FROM table1, table2
    
  • Если задается операция JOIN (или NATURAL JOIN) без явного указания типа соединения (join_type), то по умолчанию имеется в виду INNER JOIN (или NATURAL INNER JOIN).
  • Если в спецификации соединения (join_specification) указано ключевое слово ON, то все ссылки на столбцы, встречающиеся в условном выражении (conditional_expression), должны указывать на столбцы таблиц table1 и table2 или на столбцы таблиц внешнего запроса. Если в этом условном выражении присутствует вызов агрегатной функции, то соединенная таблица может фигурировать только в подзапросах, используемых в разделах HAVING или SELECT внешнего запроса, и ссылка на столбец в вызове функции должна указывать на столбец таблицы внешнего запроса.
  • Для прямых соединений (CROSS JOIN) и всех других видов соединения, включающих раздел ON, заголовок результата операции совпадает с заголовком таблицы CP.
  • Если в спецификации вида соединения присутствуют ключевые слова NATURAL или USING, то заголовок результата операции определяется следующим образом:
    • если в спецификации вида соединения присутствует ключевое слово NATURAL, то будем называть соответствующими столбцами соединения (corresponding join column) все столбцы таблиц table1 и table2, которые имеют в заголовках этих таблиц одинаковые имена. Если в спецификации вида соединения присутствует ключевое слово USING, то будем называть соответствующими столбцами соединения (corresponding join column) все столбцы таблиц table1 и table2, имена которых входят в список имен столбцов раздела USING (эти столбцы должны быть одноименными в заголовках обеих таблиц). В обоих случаях типы данных каждой пары соответствующих столбцов должны быть совместимыми;
    • будем называть списком выборки соответствующих столбцов соединения (select_list of corresponding join columns - SLCC) список элементов вида COALESCE (table1.c, table2.c) AS c*, где c является именем соответствующего столбца соединения. Элементы располагаются в том порядке, в котором они появляются в заголовке таблицы table1. Обозначим через SLT1 (SLT2) список имен столбцов таблицы table1 (table2), которые не являются соответствующими столбцами соединения. Имена располагаются в том же порядке, в котором они появляются в заголовке соответствующей таблицы;
    • заголовок результата совпадает с заголовком результата запроса
      SELECT SLCC, SLT1, SLT2
      FROM table1, table2;
      
  • Набор строк результата (множество или мультимножество) определяется по следующим правилам. Обозначим через T следующие наборы строк:
    • если видом соединения является UNION JOIN, то T - пусто;
    • если видом соединения является CROSS JOIN, то T включает все строки, входящие в CP;
    • если в спецификацию вида соединения входит раздел ON, то T включает все строки CP, для которых результатом вычисления условного выражения является true;
    • если в спецификацию вида соединения входят разделы NATURAL или USING, и список SLCC не является пустым, то T включает все строки CP, для которых значения соответствующих столбцов соединения совпадают;5)
    • если в спецификацию вида соединения входят разделы NATURAL или USING, и список SLCC является пустым, то T включает все строки CP.
  • Обозначим через P1 (P2) набор (множество или мультимножество) всех строк таблицы table1 (table2), каждая из которых участвует в образовании некой строки T.
  • Обозначим через U1 (U2) набор (множество или мультимножество) всех строк таблицы table1 (table2), ни одна из которых не участвует в образовании какой-либо строки T.
  • Обозначим через X1 набор (множество или мультимножество) всех строк, образуемых из строк набора U1 путем добавления справа подстроки из неопределенных значений, содержащей столько неопределенных значений, сколько столбцов содержит таблица table2. Обозначим через X2 набор (множество или мультимножество) всех строк, образуемых из строк набора U2 путем добавления слева подстроки из неопределенных значений, содержащей столько неопределенных значений, сколько столбцов содержит таблица table1.
  • Для соединений вида CROSS JOIN и INNER JOIN пусть S обозначает тот же набор строк, что и T.
  • Для соединений вида LEFT OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов
    SELECT * FROM T
    UNION ALL
    SELECT * FROM X1;
    
  • Для соединений вида RIGHT OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов
    SELECT * FROM T
    UNION ALL
    SELECT * FROM X2;
    
  • Для соединений вида FULL OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов
    SELECT * FROM T
    UNION ALL
    SELECT * FROM X1
    UNION ALL
    SELECT * FROM X2;
    
  • Для соединений вида UNION JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов
    SELECT * FROM X1
    UNION ALL
    SELECT * FROM X2;
    
  • Если в спецификации вида соединения присутствуют ключевые слова NATURAL или USING, то результат операции совпадает с результатом выражения запросов
    SELECT SLCC, SLT1, SLT2
    FROM S;
    
  • Во всех остальных случаях результат операции совпадает с S.
Примеры соединений разного вида

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

Итак, пусть имеются таблицы table1 (a1, a2, c1, c2) и table2 (b1, b2, c1, c2) со следующими телами:

table1
a1a2c1c2
1111
1123
1123
234NULL
3NULLNULL5
table2
b1b2c1c2
1111
1223
3323
4444
3NULLNULL5
3NULLNULL5

Обозначим через JR таблицу, являющуюся результатом соединения. Тогда для операции table1 INNER JOIN table2 ON a1=b1 AND a2<b2 (внутреннее соединение по условию) тело JR будет следующим:

JR
a1a2table1.c1table1.c2b1b2table2.c1table2.c2
11111223
11231223
11231223

Строки-дубликаты появились в JR, поскольку в первом операнде присутствовали строки-дубликаты, удовлетворяющие условию соединения.

Результатом операции table1 INNER JOIN table2 USING (c2) (внутреннее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица.

JR
a1a2table1.c1c2b1b2table2.c1
1111111
1123122
1123332
1123122
1123332
3NULLNULL53NULLNULL
3NULLNULL53NULLNULL

Результат операции table1 INNER JOIN table2 USING (c1,c2):

JR
a1a2c1c2b1b2
111111
112312
112333
112312
112333

Такой же результат будет получен при выполнении операции table1 NATURAL INNER JOIN table2 (естественное внутреннее соединение). Более того, для произвольных таблиц table1 и table2 результаты операций table1 INNER JOIN table2 USING (с1, c2, ...cn) и table1 INNER NATURAL JOIN table2 совпадают в том и только в том случае, когда список имен столбцов с1, c2, ...cn включает все имена столбцов, общие для таблиц table1 и table2.

Результатом операции table1 LEFT OUTER JOIN table2 ON a1=b1 AND a2<b2 (левое внешнее соединение по условию>) будет следующая таблица:

JR
a1a2table1.c1table1.c2b1b2table2.c1table2.c2
11111223
11231223
11231223
234NULLNULLNULLNULLNULL
3NULLNULL5NULLNULLNULLNULL

Как видно, в результате левого внешнего соединения сохраняются все данные первого (левого) операнда.

Результатом операции table1 RIGHT OUTER JOIN table2 ON a1=b1 AND a2<b2 (правое внешнее соединение по условию) будет следующая таблица:

JR
a1a2table1.c1table1.c2b1b2table2.c1table2.c2
11111223
11231223
11231223
NULLNULLNULLNULL1111
NULLNULLNULLNULL3323
NULLNULLNULLNULL4444
NULLNULLNULLNULL3NULLNULL5
NULLNULLNULLNULL3NULLNULL5

Как видно, в результате правого внешнего соединения сохраняются все данные второго (правого) операнда.

Результатом операции table1 FULL OUTER JOIN table2 ON a1=b1 AND a2<b2 (полное внешнее соединение по условию) будет следующая таблица:

JR
a1a2table1.c1table1.c2b1b2table2.c1table2.c2
11111223
11231223
11231223
234NULLNULLNULLNULLNULL
3NULLNULL5NULLNULLNULLNULL
NULLNULLNULLNULL1111
NULLNULLNULLNULL3323
NULLNULLNULLNULL4444
NULLNULLNULLNULL3NULLNULL5
NULLNULLNULLNULL3NULLNULL5

Как видно, в результате полного внешнего соединения сохраняются данные обоих операндов. Кстати, полное внешнее соединение иногда называют еще симметричным внешним соединением. Очевидно, что все операции внутреннего соединения и операция полного внешнего соединения коммутативны, а операции левого и правого соединения коммутативными не являются.

Результатом операции table1 LEFT OUTER JOIN table2 USING (c2) (левое внешнее соединение по совпадению значений указанных одноименных столбцов>) будет следующая таблица:

JR
a1a2table1.c1c2b1b2table2.c1
1111111
1123122
1123332
1123122
1123332
3NULLNULL53NULLNULL
3NULLNULL53NULLNULL
234NULLNULLNULLNULL

Результатом операции table1 RIGHT OUTER JOIN table2 USING (c2) (правое внешнее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица:

JR
a1a2table1.c1c2b1b2table2.c1
1111111
1123122
1123332
1123122
1123332
3NULLNULL53NULLNULL
3NULLNULL53NULLNULL
NULLNULLNULL4444

Результатом операции table1 FULL OUTER JOIN table2 USING (c2) (полное внешнее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица:

JR
a1a2table1.c1c2b1b2table2.c1
1111111
1123122
1123332
1123122
1123332
3NULLNULL53NULLNULL
3NULLNULL53NULLNULL
234NULLNULLNULLNULL
NULLNULLNULL4444

Результатом операции table1 LEFT OUTER JOIN table2 USING (c2, c1) (и операции table1 NATURAL LEFT OUTER JOIN table2 - естественное левое внешнее соединение) будет следующая таблица:

JR
a1a2c1c2b1b2
111111
112312
112333
112312
112333
234NULLNULLNULL
3NULLNULL5NULLNULL

Результатом операции table1 RIGHT OUTER JOIN table2 USING (c2, c1) (и операции table1 NATURAL RIGHT OUTER JOIN table2 - естественное правое внешнее соединение) будет следующая таблица:

JR
a1a2c1c2b1b2
111111
112312
112333
112312
112333
NULLNULL4444
NULLNULLNULL53NULL
NULLNULLNULL53NULL

Результатом операции table1 FULL OUTER JOIN table2 USING (c2, c1) (и операции table1 NATURAL FULL OUTER JOIN table2 - естественное полное внешнее соединение) будет следующая таблица:

JR
a1a2c1c2b1b2
111111
112312
112333
112312
112333
234NULLNULLNULL
3NULLNULL5NULLNULL
NULLNULL4444
NULLNULLNULL53NULL
NULLNULLNULL53NULL

Наконец, результатом операции table1 UNION JOIN table2 (соединение объединением) будет следующая таблица:

JR
a1a2table1.c1table1.c2b1b2table2.c1table2.c2
1111NULLNULLNULLNULL
1123NULLNULLNULLNULL
1123NULLNULLNULLNULL
234NULLNULLNULLNULLNULL
3NULLNULL5NULLNULLNULLNULL
NULLNULLNULLNULL1123
NULLNULLNULLNULL1223
NULLNULLNULLNULL3323
NULLNULLNULLNULL4444
NULLNULLNULLNULL3NULLNULL5
NULLNULLNULLNULL3NULLNULL5
Примеры запросов с использованием соединенных таблиц

Мы приведем всего пару примеров, чтобы проиллюстрировать формулировки запросов, в разделе FROM которых используются ссылки на соединенные таблицы, т. е. выражения соединений.

SELECT DEPT.DEPT_NO, EMP1.EMP_NAME, COUNT(*), MIN(EMP2.EMP_SAL),
   MAX(EMP2.EMP_SAL), AVG(EMP2.EMP_SAL)
FROM (DEPT NATURAL INNER JOIN EMP AS EMP2) 
   INNER JOIN EMP AS EMP1 ON DEPT.DEPT_MNG = EMP1.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP1.EMP_NAME;
Пример 15.17. Для каждого отдела найти его номер, имя руководителя, число сотрудников, минимальный, максимальный и средний размеры зарплаты сотрудников (еще одна формулировка запроса из примера 15.4).
SELECT EMP1.EMP_NO, EMP2.EMP_NAME
FROM (EMP AS EMP1 NATURAL INNER JOIN DEPT) 
   INNER JOIN EMP AS EMP2 ON DEPT.DEPT_MNG = EMP2.EMP_NO
WHERE EMP1.EMP_SAL > 30000.00;
Пример 15.18. Найти номера служащих и имена их начальников отделов для служащих, размер зарплаты которых больше 30000 руб.

Можно обойтись вообще без раздела WHERE, если пожертвовать "естественностью" первого соединения (пример 15.17.1):

SELECT EMP1.EMP_NO, EMP2.EMP_NAME
FROM (EMP AS EMP1 INNER JOIN DEPT 
   ON EMP1.DEPT_NO = DEPT.DEPT_NO AND
     EMP1.EMP_SAL > 30000.00) 
   INNER JOIN EMP AS EMP2 ON DEPT.MNG = EMP2.EMP_NO;
Пример 15.17.1.

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

Порождаемые таблицы с горизонтальной связью (lateral_derived_table)

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

Как показывают синтаксические правила, приведенные в лекции 13, один из возможных способов спецификации ссылки на таблицу состоит в следующем:

table_reference ::= LATERAL (query_expression)
   [ [ AS ] correlation_name
     [ ( derived_column_list ) ] ]

Таблица, ссылка на которую специфицируется таким образом, называется порождаемой таблицей с горизонтальной связью7) (lateral_derived_table; для краткости будем называть такие таблицы LD-таблицами). Отличие LD-таблицы от обычной порождаемой таблицы состоит в том, что в выражении запросов LD-таблицы разрешается использовать ссылки на столбцы таблиц, специфицированных ранее в разделе FROM (т. е. таких таблиц, ссылки на которые содержатся в списке раздела FROM слева от ссылки на данную LD-таблицу).8) Покажем на примере, каким образом наличие в списке раздела FROM ссылки на LD-таблицу меняет семантику этого раздела.

Предположим, что раздел FROM имеет вид FROM T1, T2, причем таблица T2 является LD-таблицей. Обозначим соответствующее выражение запросов через Q2. Тогда таблица T, являющаяся результатом раздела FROM, будет вычисляться следующим образом. Последовательно, строка за строкой просматривается таблица T1. Пусть s1 является очередной строкой T1. Тогда в Q2 все ссылки на столбцы вида T1.ck, где ck - имя некоторого столбца T1, заменяются значением s1.ck, и вычисляется полученное таким образом выражение запросов. Обозначим результирующую таблицу этого выражения через T2s1. Обозначим через T12s1 таблицу, являющуюся результатом расширенного декартова произведения s1 CROSS JOIN T2s1. Таблица T получается путем объединения с сохранением дубликатов таблиц T12s1, полученных для всех строк s1 таблицы T1.

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

SELECT EMP.EMP_NO 
FROM DEPT, LATERAL
   (SELECT EMP1_SAL
     FROM EMP EMP1
     WHERE EMP1.EMP_NO = DEPT.DEPT_MNG),
   LATERAL
   (SELECT EMP_NO
     FROM EMP
     WHERE EMP_SAL = EMP1_SAL AND
     EMP.EMP_NO <> DEPT.DEPT_MNG);
Пример 15.19. Найти номера сотрудников, не являющихся руководителями отделов и получающих заплату, размер которой равен размеру зарплаты какого-либо руководителя отдела (еще одна формулировка запроса из примера 14.10 из лекции 14).

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

Заключение

Думаю, что теперь читатели в состоянии в полной мере оценить мощность, разнообразие и избыточность средств языка SQL, предназначенных для формулировки запросов на выборку данных. Конечно, язык SQL (по крайней мере, ту часть SQL, которая обсуждается в этом курсе) нельзя считать языком программирования, но написание сложных запросов сродни программированию. И нельзя сказать, что SQL каким-либо образом дисциплинирует это "программирование". По всей видимости, в общем случае никто не может сказать, какая из формулировок одного и того же запроса является более правильной, это дело вкуса.

Зачастую десять студентов, одновременно формулирующих на SQL один и тот же запрос к одной и той же базе данных, выдают десять разных правильных решений. Один человек предпочитает формулировки запросов в классическом стиле, другой использует выражения запросов в разделе FROM, третий пытается сосредоточить все условия выборки в разделе HAVING. Люди с алгебраическими наклонностями предпочитают использовать выражения соединений. Приходилось встречать и формулировки со сложными вложенными подзапросами в списке выборки раздела SELECT.

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

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

  1)   Здесь мы прибегаем к компромиссу между реляционной терминологией и моделью данных SQL: конечно, в реляционной модели кортеж из неопределенных значений не может соответствовать заголовку отношения, поскольку NULL не является значением ни одного типа данных.
  2)   Оба термина являются приемлемыми. Речь идет об агрегатных функциях, поскольку аргументом функции является агрегатное (составное) значение. Речь идет о функциях над множествами, поскольку аргументом функции является множество (в общем случае, мультимножество) значений. Но более правильно было бы говорить о групповых функциях, поскольку в большинстве случаев такие функции работают на значениях столбцов групп строк.
  3)   Обратите внимание на то, что это еще один вид различения строк в SQL и еще одна скрытая интерпретация неопределенного значения. COUNT(*) работает так, как если бы выполнялось соотношение (NULL=NULL) false. Тем самым, в SQL применяются все три возможных интерпретации NULL. При вычислении логических выражений полагается (NULL=NULL) uknown; при определении строк-дубликатов неявно считается, что (NULL=NULL) true; наконец, при вычислении агрегатной функции COUNT(*) неявно полагается, что (NULL=NULL) false. Конечно, в такой "тройственности" нет ничего хорошего, но в контексте языка SQL приходится мириться с этими и другими негативными последствиями наличия неопределенных значений.
  4)   Интересно, что для этого запроса возможна альтернативная формулировка с использованием операции CROSS JOIN: SELECT * FROM table1 CROSS JOIN table2. Может возникнуть естественный вопрос: зачем вводить специальную конструкцию для декартова произведения? По мнению автора, эта конструкция была введена, главным образом, для повышения уровня общности языка SQL. Кроме того, использование явного ключевого слова CROSS JOIN является подтверждением того, что пользователь действительно может получить декартово произведение, а не упустил по ошибке раздел WHERE.
  5)   Для удобства читателей напомним, что по определению выражение COALESCE (V1, V2) эквивалентно следующему выражению с переключателем: CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END.
  6)   За очевидностью мы опустим примерCROSS JOIN.
  7)   Конечно, предлагаемый русский вариант термина lateral слишком громоздок. По всей видимости, если этот механизм войдет в практику пользователей SQL, можно будет использовать качестве термина что-то вроде литеральной порождаемой таблицы. Но здесь для нас главным является не обеспечение хорошей новой технологии, а обеспечение понимания материала.
  8)   Тем самым ссылка на LD-таблицу не может быть первой в списке раздела FROM. Кстати может возникнуть естественный вопрос: почему разрешаются ссылки только на таблицы, находящиеся в списке раздела FROM только слева LD-таблицы? Стандарт отвечает на этот вопрос весьма просто и бесхитростно. Если разрешить использовать ссылки, находящиеся и слева, и справа от спецификации ссылки на LD-таблицу, то это может привести к зацикливанию при выполнении раздела FROM. Поэтому нужно было вбирать одно из направлений, и было выбрано направление слева направо.