3.1.1 Выражение подзапроса

Основой языка SQL является "выражение подзапроса". Подзапрос может являться частью полного запроса, а в частном случае и сам может составлять полный запрос. Синтаксис подзапроса приведен на рис. 3.1.

Рисунок 3.1 - Общий синтаксис подзапроса

Фразы SELECT и FROM являются обязательными, остальные нет. Фраза HAVING формально может применяться и отдельно, но имеет она смысл только с фразой GROUP BY.

В диалекте SQL Oracle в подзапрос может входить еще фраза CONNECT BY, эта фраза рассматривается нами в п.3.5.1.

Подзапрос может быть "прочитан" как:

    ВЫБРАТЬ (SELECT) то-то
    ИЗ (FROM) оттуда-то
    ГДЕ (WHERE) выполняются такие-то условия
    ГРУППАМИ ПО (GROUP BY) таким-то параметрам
    ИМЕЮЩИМИ (HAVING) такие-то свойства

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

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

    Выбрать значение максимальной зарплаты в отделе 23

является гарантированно скалярным, а запрос:

    Выбрать имя сотрудника отдела 23, получающего максимальную зарплату

- нет, так как в отделе могут быть несколько сотрудников с одинаково высокой зарплатой.

Фраза SELECT подзапроса определяет те столбцы, из которых будет состоять таблица - результат выполнения подзапроса. Синтаксис фразы SELECT показан на рис.3.2.

Рисунок 3.2 - Синтаксис фразы SELECT

Ключевые слова ALL или DISTINCT определяют, будут ли в результирующей таблице повторяющиеся строки. Если задано ключевое слово DISTINCT, то оставляется только один экземпляр повторяющейся строки. По умолчанию предполагается ALL.

Основное содержание фразы SELECT - список выборки, элементами которого могут быть конструкции с символом * и/или столбцовые выражения. Элементы списка выборки разделяются запятыми.

Символ * в фразе SELECT означает "выбрать все столбцы". Имя таблицы обязательно указывается, если запрос обращается к нескольким таблицам и надо указать одну из них. При выборке всех столбцов их имена в результирующей таблице будут те же, что и в исходной.

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

Синтаксис столбцового выражения показан на рис.3.3.

Рисунок 3.3 - Синтаксис столбцового выражения

Итак, чем может быть столбцовое выражение?

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

Столбцом результирующей таблицы может быть также константа любого типа или неопределенное значение (NULL). Однако, "просто" NULL может выбираться только в Oracle, в DB2 неопределенное значение должно быть приведено к какому-либо типу при помощи операции CAST (см. ниже).

Специальные регистры DB2 представляют собой область памяти, которую менеджер СУБД определяет для каждого приложения и записывает в нее некоторую общую информацию, могущую быть полезной в составе запроса. В Oracle для тех же целей используются некоторые функции SQL (эти функции не имеют параметров). Некоторые, на наш взгляд, наиболее полезные специальные регистры/функции приведены в таблице.

Функции языка SQL делятся на скалярные и агрегатные, они описываются в следующем пункте.

Выражение-функция CAST (стандарт SQL и DB2) приводит столбцовое выражение к заданному типу. CASE-выражение (стандарт SQL и DB2) позволяет внести в запрос элементы процедурной логики. Синтаксис CASE-выражения приведен на рис.3.4.

Рисунок 3.4 - Синтаксис CASE-выражения

Условная фраза WHEN в CASE-выражении задает выбор по одному из условий и похожа на конструкцию языка С: if (...) ... else if (...) ... else if...

Пример:

    CASE 
      WHEN salary<=1000 THEN salary*0.1
      WHEN salary>1000 AND salary<=2000 THEN salary*0.15
      WHEN salary>2000 AND salary<=3000 THEN salary*0.12
      ELSE salary*0.25
    END

Простая фраза WHEN задает выбор по значению некоторого выражения и похожа на оператор switch языка С.

Пример:

    CASE job_id
      WHEN 670 THEN salary*0.1
      WHEN 671 THEN salary*0.15
      WHEN 675 THEN salary*0.12
      ELSE salary*0.25
    END

Стандартом SQL предусматривается возможность использования в качестве столбцового выражения скалярного запроса, но в Oracle эта возможность не реализована.

Столбцовые выражения могут являться операндами более сложных выражений, в которых употребляются арифметические операции: +, -, *, /. Стандартом языка предусматривается также операция возведения в степень: **, но ни в Oracle, ни в DB2 она не реализована.

В выражении может употребляться также строковая операция конкатенации - CONCAT или ||.

Круглые скобки в выражении меняют порядок выполнения операций.

В языке SQL есть богатый набор встроенных функций. Функции SQL подразделяются на скалярные и агрегатные.

3.1.3.1 Скалярные функции

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

Числовые функции

Строковые функции

Строковые функции, возвращающие целое число

Функции преобразования типов

В DB2 и в стандарте SQL функции преобразования типов имеют имена целевого типа: CHAR(x), DECIMAL(x), DOUBLE(x) и т.д.

В Oracle основные функции преобразования типов: TO_CHAR(x, format_string), TO_NUMBER(x, format_string), TO_DATE(x, format_string).

Функции, работающие с датой и временем, описаны нами в разделе 2.3.

3.1.3.2 Агрегатные функции

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

Синтаксис большинства агрегатных функций такой же, как и функций скалярных, но перед выражением, являющимся параметром агрегатной функции, может стоять ключевое слово DISTINCT или ALL. Употребление ключевого слова DISTINCT означает, что при вычислении функции одинаковые значения будут учитываться только один раз. По умолчанию предполагается ALL. Наиболее часто употребляемые агрегатные функции:

• MIN - минимальное значение из множества значений;
• MAX - максимальное значение из множества значений;
• AVG - среднее значение по множеству значений;
• SUM - сумма множества значений;
• COUNT - общее количество значений в множестве.

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

Фраза FROM содержит перечисление источников, из которых выбираются данные. Синтаксис фразы FROM показан на рис.3.5.

Рисунок 3.5 - Синтаксис фразы FROM

Как видно из рисунка, основное содержание фразы FROM - перечень табличных выражений - источников выборки. Табличным выражением может быть таблица, запрос (результатом запроса является таблица) или результат операции соединения (стандарт SQL и DB2).

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

Табличному выражению, упомянутому в фразе FROM, может быть присвоено имя, по которому на эту таблицу можно ссылаться в данном запросе. DB2 требует, чтобы запросу в составе фразы FROM имя присваивалось обязательно. Oracle допускает запросы без имени (но на такое табличное выражение, естественно, ссылаться невозможно).

Синтаксис операции соединения на рис.3.5 показан так, как он определен стандартом SQL. В форме JOIN-USING задается перечень столбцов, по которым осуществляется соединение. В форме NATURAL JOIN соединение осуществляется по всем одноименным столбцам. Oracle 8 не поддерживает операцию соединения. В DB2 операция соединения отличается от стандартной. На рис.3.6 приведен упрощенный синтаксис операции соединения для DB2 (полная форма рассматривается в п.3.4.2). Как видно из синтаксической диаграммы, соединение можно осуществлять по любому условию, а не только по равенству значений в одноименных столбцах.

Фраза WHERECHECK. Условие, задаваемое фразой WHERE, проверяется для каждой строки отдельно. Синтаксис фразы WHERE показан на рис.3.7.

Рисунок 3.7 - Синтаксис фразы WHERE

Как видно из рисунка, содержание фразы WHERE составляет один или несколько предикатов, объединяемых логическими операциями. Предикат - это выражение, результат которого является логическим значением. Следует, однако, отметить, что в отличие от процедурных языков программирования, в которых результат логического выражения может принимать одно из двух значений - TRUE или FALSE, в SQL применяется 3-значная логика с возможными значениями логического типа: TRUE, FALSE и NULL. Ниже приводится таблица истинности для операций 3-значной логики.

Далее мы рассматриваем отдельные предикаты, которые могут употребляться в условии.

3.1.5.1 Предикат сравнения

Синтаксис предиката сравнения приведен на рис.3.8.

Рисунок 3.8 - Синтаксис предиката сравнения.

Как видно из рисунка, SQL, во-первых, предоставляет те же возможности сравнения столбцовых выражений, что и процедурные языки программирования.

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

В третьих, SQL позволяет сравнивать строки таблицы, то есть, определенные наборы значений в столбцах. Строка может определяться либо запросом, который гарантированно возвращает одну строку, либо перечислением столбцовых выражений, составляющих столбцы в строке. DB2 позволяет использовать запрос и в левой, и в правой части такого сравнения, Oracle - только в правой части. Естественно, что строки могут сравниваться только на равенство/неравенство, но не на больше/меньше.

3.1.5.2 Предикат BETWEEN

Предикат BETWEEN проверяет нахождение значения между заданными границами. Синтаксис предиката BETWEEN показан на рис.3.9.

Рисунок 3.9 - Синтаксис предиката BETWEEN

Предикат BETWEEN является избыточным, и условие

    x BETWEEN x1 AND x2

эквивалентно условию

    x>=x1 AND x<=x2

3.1.5.3 Предикат LIKE

Предикат LIKE используется для сравнения символьной строки с шаблоном. Синтаксис предиката LIKE показан на рис.3.10.

Рисунок 3.10 - Синтаксис предиката LIKE

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

Шаблон - это та строка, с которой сравнивается строка в столбцовом выражении. В шаблоне могут быть специальные символы: '%' (процент) и '_' (подчерта). Символ '%' в шаблоне означает "любое количество любых символов", Символ '_' означает "один любой символ". Так, например, предикат

    first_name LIKE 'MAR%'

даст результат TRUE для значений first_name: MARK, MARTA, MARTIN, MARVIN, а предикат

    first_name LIKE 'MAR%'

даст результат TRUE только для значения MARK.

Необязательное подвыражение ESCAPE в этом предикате дает возможность переопределить специальные символы.

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

3.1.5.4 Предикаты IS NULL, IS NOT NULL

Предикат IS NULL дает значение TRUE, если значение проверяемого столбцового выражения - NULL. Синтаксис предиката IS NULL показан на рис.3.11.

Рисунок 3.11 - Синтаксис предиката IS NULL

Возникает вопрос: почему для проверки значения NULL вводится специальный предикат, почему нельзя использовать для этих целей операции ...=NULL и ... <>NULL? Дело в том, что значение NULL не вполне однозначно. С одной стороны, NULL часто используется в смысле "пусто", "ничего нет и быть не может" - именно в таком смысле это значение применяется, например, для столбца commission в "Корпорации Кинга". С другой стороны, более правильный смысл значения NULL - "не определено", "может быть все, что угодно" - именно такой смысл имеет это значение в операциях сравнения. Поэтому результатом, например, сравнения a<>b, при a=NULL будет FALSE при любом значении b.

3.1.5.5 Предикат IN

Предикат IN проверяет попадание значения в некоторое множество значений. Синтаксис предиката IN показан на рис.3.12.

Рисунок 3.12 - Синтаксис предиката IN

Множество, в которое должно попадать проверяемое значение, задается либо перечислением его элементов, либо формируется как результат выполнения запроса. Проверяться на попадание во множество может не единственное столбцовое значение, а их набор, который должен попадать в множество строк таблицы, формирующейся как результат запроса. Если во вложенном запросе выбирается хотя бы одно значение NULL, то результатом предиката NOT IN будет FALSE.

3.1.5.6 Предикат EXISTS

Предикат EXISTS возвращает значение TRUE, если в результате выполнения запроса - параметра предиката выбрана хотя бы одна строка. Синтаксис предиката EXISTS показан на рис.3.13.

Рисунок 3.13 - Синтаксис предиката EXISTS

3.1.5.7 Предикаты с кванторами

Синтаксис предикатов с кванторами показан на рис.3.14.

Рисунок 3.14 - Синтаксис предикатов с кванторами

Поясним семантику предикатов сначала по верхней ветви диаграммы.

Предикат с ключевым словом ALL дает результат TRUE, если выполнение заданной операции сравнения столбцового выражения с каждым значением из множества, выбранного запросом, дает результат TRUE. Предикат с ключевым словом ANY или SOME (эти слова являются синонимами) дает результат TRUE, если выполнение заданной операции сравнения столбцового выражения с хотя бы одним значением из множества, выбранного запросом, дает результат TRUE.

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

Синтаксис фраз GROUP BY и HAVING показан на рис.3.15.

Рисунок 3.15 - Синтаксис фраз GROUP BY и HAVING

Список столбцовых выражений, являющийся параметром фразы GROUP BY, определяет значения, по которым производится группирование. У всех строк, включаемых в одну группу, значения этого списка одинаковы. Неопределенные (NULL) значения рассматриваются в данном случае как равные. Столбцовые выражения в GROUP BY могут содержать любые столбцы из таблиц, указанных в фразе FROM, независимо от того, фигурируют ли они в списке выборки. Эти выражения, однако, не могут содержать вложенные запросы.

Столбцовое выражение - параметр группирования - может использоваться и в условии фразы HAVING, и в списке выборки фразы SELECT.

Если подзапрос содержит GROUP BY, то в списке выборки фразы SELECT могут быть только:

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

Запрос (полный запрос) представляет собой подзапрос или несколько подзапросов, объединенных реляционными операциями. Синтаксис запроса приведен на рис. 3.16.

Рисунок 3.16 - Синтаксис полного запроса

Реляционные операции, применяемые в запросе, сведены в следующую таблицу:

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