Оператор SELECT

Оператор SELECT относится к подразделу языка манипулирования данными DML (Data Manipulation Language).

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

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

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

SELECT [DISTINCT | ALL] <выходное поле> [, <выходное поле>]
    FROM <источники> [<соединяемые источники>]
    [WHERE <условие выборки>]
    [GROUP BY <условие группирование выбранных данных>
    [HAVING <условие выборки>]]
    [UNION [DISTINCT | ALL] <другой набор данных>]
    [ORDER BY <выражение для порядка выборки>]
    [OFFSET <n> {ROW | ROWS}] [FETCH {FIRST | NEXT} [<m>] {ROW | ROWS} ONLY]]
    [FOR UPDATE [OF <имя столбца> [, <имя столбца>]...] [WITH LOCK]
    [INTO [:]<переменная> [,[:]<переменная> ... ]]

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

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

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

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

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

Предложение UNION дает возможность объединить в выходном наборе данных несколько таблиц с одинаковой структурой.

Предложение ORDER BY задает упорядоченность выходного набора данных. Здесь также можно указать количество строк, которое должно быть помещено в результирующий набор данных (OFFSET, FETCH).

Необязательное предложение WITH LOCK накладывает блокировки на выбранные записи и таким образом оберегает их от параллельного использования в других транзакциях. СУБД Firebird/RedDatabase реализуют многоверсионную архитектуру, при которой читатели записи не блокируют писателей, избегая явных пессимистических блокировок. Однако иногда это бывает полезно.

С помощью предложения INTO в PSQL (хранимых процедурах, функциях, триггерах и т.п.) результаты выборки команды SELECT могут быть построчно загружены в локальные переменные (число, порядок и типы локальных переменных должны соответствовать полям SELECT).

Рассмотрим несколько простых примеров оператора SELECT.

SELECT * FROM COUNTRY;

Выбирает все записи и все столбцы из таблицы COUNTRY.

SELECT CURRENCY FROM COUNTRY WHERE COUNTRY='Russia';

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

SELECT COUNT(*) FROM CUSTOMER;

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

SELECT E.FULL_NAME FROM EMPLOYEE E ORDER BY E.FULL_NAME;

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

Список выбора

Список полей содержит одно или более выражений, разделённых запятыми. Результатом каждого выражения является значение соответствующего поля в наборе данных команды SELECT. Исключением является выражение * («звёздочка»), которое возвращает все поля отношения.

Синтаксис:

SELECT [ALL | DISTINCT] <поле>, <поле>, ... FROM T

, где <поле> может быть:

  • ТАБЛИЦА.ПОЛЕ

  • ПРОЦЕДУРА.ВЫХОДНОЕ_ПОЛЕ

  • Константа

  • NULL

  • Выражение

  • Конструкция CASE

  • NEXT VALUE FOR

  • Любое выражение, возвращающее единственное значение

Вместо ТАБЛИЦА можно указать псевдоним, представление.

Хорошим тоном является указание полного имени поля вместе с именем алиаса или таблицы/представления/хранимой процедуры, к которой это поле принадлежит.

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

Алиасы (псевдонимы) заменяют оригинальное имя таблицы/ представления/ хранимой процедуры: как только определён алиас для соответствующего отношения, использовать оригинальное имя нельзя.

В начало списка полей могут быть добавлены ключевые слова DISTINCT или ALL:

  • DISTINCT удаляет дубликаты строк: то есть, если две или более записей содержат одинаковые значения во всех соответствующих полях, только одна из этих строк будет включена в результирующий набор данных.

  • ALL включает все строки в результирующий набор данных. ALL включено по умолчанию и поэтому редко используется: явное указание поддерживается для совместимости со стандартом SQL.

Примеры списков выбора:

SELECT * FROM RDB$DATABASE;
SELECT CURRENT_USER FROM RDB$DATABASE;

Выбирает все поля из таблицы RDB$DATABASE. Особенность данной таблицы в том, что в ней существует всегда одна единственная запись. Часто ее используют чтобы просто вычислить значения какой-либо функции. При этом сама запись фактически не нужна, как показано во втором случае.

SELECT CUSTOMER.CUSTOMER, CUSTOMER.PHONE_NO, CITY FROM CUSTOMER;

Выбирает поля из таблицы CUSTOMER используя полное имя поля.

SELECT LAST_NAME, SALARY * 12 AS ANNUAL_SALARY FROM EMPLOYEE;

Запрос, в котором используется выражение в списке выбора. При этом для каждой результатирующей строки столбец SALARY будет умножен на 12.

SELECT iif(CITY IS NULL, 'Murom', CITY) FROM CUSTOMER;

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

SELECT UPPER(COUNTRY) FROM COUNTRY;

Пример приведения строкового поля к верхнему регистру. Это поле в результатирующем наборе данных будет иметь значения только в верхнем регистре.

Ограничения выборки

FIRST и SKIP

Позволяет получить части строк из упорядоченного набора. Констракция появилась в Firebird исторически раньше чем вошла в стандарт SQL, поэтому в настоящее время не рекомендуется ее использовать. Предпочтительнее использовать FETCH и OFFSET.

Синтаксис использования:

SELECT [FIRST <число строк>] [SKIP <число строк>] FROM ...

<число строк> - может быть выражением, возвращающим целое число, в том числе подзапросом. Любой аргумент FIRST или SKIP, который не является целым числом или параметром SQL должен был заключён в круглые скобки. Это, означает, что в случае использования вложенной команды SELECT в качестве параметра для FIRST или SKIP, он должен быть вложен в две пары скобок.

Выражение FIRST ограничивает результирующий набор данным указанным числом записей.

Выражение SKIP пропускает указанное число записей перед выдачей результирующего набора данных.

Когда эти выражения используются совместно, например FIRST m SKIP n, то в результате n записей будет пропущено и, из оставшихся, m записей будет возвращено в результирующем наборе данных.

FETCH, OFFSET

Предложения FETCH и OFFSET являются SQL:2008 совместимым эквивалентом предложениям FIRST/SKIP и альтернативой предложению ROWS. Предложение OFFSET указывает, какое количество строк необходимо пропустить. Предложение FETCH указывает, какое количество строк необходимо получить.

Предложения OFFSET и FETCH могут применяться независимо уровня вложенности выражений запросов.

Синтаксис:

SELECT ... FROM ...[ORDER BY <expr_order_list>]
    [OFFSET <число строк> {ROW | ROWS}]
    [FETCH {FIRST | NEXT} [<число строк>] {ROW | ROWS} ONLY]

<число строк> - может быть выражением, возвращающим целое число, в том числе подзапросом.

Примеры запросов:

Следующий запрос возвращает все строки кроме первых 10, упорядоченных по столбцу COL1:

SELECT * FROM T1 ORDER BY COL1 OFFSET 10 ROWS

В этом примере возвращается первые 10 строк, упорядоченных по столбцу COL1:

SELECT * FROM T1 ORDER BY COL1 FETCH FIRST 10 ROWS ONLY

Использование предложений OFFSET и FETCH в производной таблице, результат которой ограничивается ещё раз во внешнем запросе.

SELECT * FROM (
        SELECT * FROM T1    ORDER BY COL1 DESC
        OFFSET 1 ROW FETCH NEXT 10 ROWS ONLY
    ) a
ORDER BY a.COL1 FETCH FIRST ROW ONLY

Выражение FROM

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

Синтаксис использования:

SELECT ... FROM <источник> [[AS] <псевдоним>] [<joins>] [...]

где :

  • таблица

  • представление

  • селективная хранимая процедура

  • производная таблица

  • общее табличное выражение

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

Если вы дадите таблице или представлению псевдоним (алиас), то вы должны везде использовать этот псевдоним, а не имя таблицы, при обращении к именам столбцов.

Например, следующие запросы корректны:

SELECT LAST_NAME FROM CUSTOMER;
SELECT CUSTOMER.LAST_NAME FROM CUSTOMER;
SELECT LAST_NAME FROM CUSTOMER C;
SELECT C.LAST_NAME FROM CUSTOMER C;

А этот нет, т.к. таблице назначен псевдоним, а в списке выбора поле указано с использованием имени таблицы:

SELECT CUSTOMER.LAST_NAME FROM CUSTOMER C;

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

  • Содержать, по крайней мере, один выходной параметр;

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

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

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

SELECT name, az, alt
    FROM visible_stars('Brugge', current_date, '22:30')
    WHERE alt >= 20

Производная таблица — это корректная команда SELECT, заключённая в круглые скобки, опционально обозначенная псевдонимом таблицы и псевдонимами полей.

Синтаксис использования производной таблицы:

(запрос) [[AS] <псевдоним производной таблицы>] [(<псевдоним столбца 1>, ...)]

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

Пример, демонстрирующий использование псевдонима производной таблицы и списка псевдонимов столбцов (оба опциональные):

SELECT DBINFO.DESCR, DBINFO.DEF_CHARSET
    FROM (SELECT * FROM RDB$DATABASE)
        DBINFO (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)

  • Производные таблицы могут быть вложенными;

  • Производные таблицы могут быть объединениями и использоваться в объединениях. Они могут содержать агрегатные функции, подзапросы и соединения, и сами по себе могут быть использованы в агрегатных функциях, подзапросах и соединениях. Они также могут быть хранимыми процедурами или запросами из них. Они могут иметь предложения WHERE, ORDER BY и GROUP BY, указания FIRST, SKIP или ROWS и т.д.;

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

  • Список псевдонимов столбцов опциональный, но если он присутствует, то должен быть полным (т.е. он должен содержать псевдоним для каждого столбца производной таблицы);

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

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

Предположим, что у нас есть таблица COEFFS, которая содержит коэффициенты для ряда квадратных уравнений, которые мы собираемся решить. Она может быть определена примерно так:

CREATE TABLE coeffs (
    a DOUBLE PRECISION NOT NULL,
    b DOUBLE PRECISION NOT NULL,
    c DOUBLE PRECISION NOT NULL,
    CONSTRAINT chk_a_not_zero CHECK (a <> 0)
)

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

SELECT
    IIF ((b*b - 4*a*c) >= 0, (-b - sqrt(b*b - 4*a*c)) / 2*a, NULL) AS sol_1,
    IIF ((b*b - 4*a*c) > 0, (-b + sqrt(b*b - 4*a*c)) / 2*a, NULL) AS sol_2
FROM coeffs

Если использовать производную таблицу, то запрос можно сделать гораздо более элегантным:

SELECT
    IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) AS sol_1,
    IIF (D > 0, (-b + sqrt(D)) / denom, NULL) AS sol_2
FROM
    (SELECT b, b*b - 4*a*c, 2*a FROM coeffs) (b, D, denom)

Если мы захотим показывать коэффициенты рядом с решениями уравнений, то мы можем модифицировать запрос следующим образом:

SELECT
    a, b, c,
    IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) sol_1,
    IIF (D > 0, (-b + sqrt(D)) / denom, NULL) sol_2
FROM
    (SELECT a, b, c, b*b - 4*a*c AS D, 2*a AS denom FROM coeffs)

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