Опубликован: 10.10.2005 | Уровень: специалист | Доступ: платный | ВУЗ: Московский государственный университет имени М.В.Ломоносова
Лекция 5:

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

Аннотация: В этой лекции мы завершаем обсуждение основных (традиционных) конструкций оператора SELECT языка SQL. В разделе "Агрегатные функции, группировка и условия раздела HAVING" обсуждаются разделы GROUP BY и HAVING. Основной акцент делается на способах конструирования условий раздела HAVING. На примерах демонстрируется, что разделы GROUP BY и HAVING действительно полезны, а иногда и необходимы при формулировке запросов с вызовами агрегатных функций. В разделах "Ссылки на порождаемые таблицы в разделе FROM" и "Более сложные конструкции оператора выборки" мы возвращаемся к разновидностям ссылок на таблицу в разделе FROM и последовательно обсуждаем порождаемые таблицы, соединенные таблицы и порождаемые таблицы с горизонтальной связью.
Ключевые слова: SQL, предикат, условное выражение, разделы, запрос, агрегатная функция, ORDER, соединенная таблица, порождаемая таблица с горизонтальной связью (lateral derived table), ПО, вычисление выражения, таблица, ссылка, SQL/92, логические выражения, декартово произведение, естественное правое внешнее соединение, внешний ключ, естественное соединение, EMP, natural, joining, неопределенное значение, левое внешнее соединение, внешнее соединение, симметричное внешнее соединение, outer, COMP, UNION, кортеж, модель данных, полное внешнее соединение, стандарт языка, прямое соединение (CROSS JOIN), внутреннее соединение по условию (INNER JOIN … ON), внутреннее соединение по совпадению значений указанных одноименных столбцов (INNER JOIN … USING), естественное внутреннее соединение (NATURAL INNER JOIN), левое внешнее соединение по условию (LEFT OUTER JOIN … ON), правое внешнее соединение по условию (RIGHT OUTER JOIN … ON), полное внешнее соединение по условию (FULL OUTER JOIN … ON), левое внешнее соединение по совпадению значений указанных одноименных столбцов (LEFT OUTER JOIN … USING), правое внешнее соединение по совпадению значений указанных одноименных столбцов (RIGHT OUTER JOIN … USING), полное внешнее соединение по совпадению значений указанных одноименных столбцов (FULL OUTER JOIN … USING), естественное левое внешнее соединение (NATURAL LEFT OUTER JOIN), естественное правое внешнее соединение (NATURAL RIGHT OUTER JOIN), естественное полное внешнее соединение (NATURAL FULL OUTER JOIN), соединение объединением (UNION JOIN), систематический, Мультимножество, стандарт SQL:1999, AVG, среднее значение, булевский тип, списки значений, скалярное выражение, тип значения агрегатной функции, тип символьной строки, значение выражения, distinction, логическое выражение раздела HAVING, табличное выражение, подзапрос, предикат like, EXISTS, ссылка на порождаемые таблицы в разделе FROM, MNG, заработная плата, PRO, предикат сравнения, CP/M, cross-section, NATURAL INNER JOIN, соответствующий столбец соединения, список выборки соответствующих столбцов соединения, выражение с переключателем, UNION JOIN, C2, коммутативность, NATURAL LEFT OUTER JOIN, NATURAL FULL OUTER JOIN, мощность, избыточность, программирование, компилятор, план выполнения, базы данных, метаданные, правильный ответ

Введение

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

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

В обычных порождаемых таблицах 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 JOIN r2 WHERE comp (левое внешнее соединение r1 и r2 по условию comp ). Тогда Hs = Hr1 union Hr2. Пусть tr1 \in  Br1 и tr2 \in  Br2. Тогда tr1 \ union\  tr2 \in  Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr1 \in  Br1, для которого нет ни одного кортежа tr2 \in  r2, такого, что comp (tr1 union tr2) = true, то tr1 \ union \ tr2_{null} \in  Bs, где tr2null - кортеж, соответствующий Hr2, все значения которого являются неопределенными1Здесь мы прибегаем к компромиссу между реляционной терминологией и моделью данных SQL: конечно, в реляционной модели кортеж из неопределенных значений не может соответствовать заголовку отношения, поскольку NULL не является значением ни одного типа данных..

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

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

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

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

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

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

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

Алексей Ковтун
Алексей Ковтун

При попытке исполнения запроса:

CREATE DOMAIN EMP_NO AS INTEGER

    CHECK (VALUE BETWEEN 1 AND 10000);

Выдается ошибка: Неизвестный тип объекта "DOMAIN" в интсрукции CREATE, DROP или ALTER. 

Используется SQL Server MS SQL 2008R2

Александра Каева
Александра Каева