Табличные операции и составные запросы

Стандарт SQL2 определяет составной запрос как универсальный способ определения таблицы результатов запроса. В составном запросе могут использоваться следующие базовые строительные блоки :

Спецификация запроса, описанная в предыдущем параграфе (select ... from. . ) Ее значением является таблица результатов запроса.

Табличный конструктор, о котором рассказывалось выще (values . . .), Его значением является таблица, построенная из заданных в конструкторе элементов,

Явная ссылка на таблицу (table имя таблицы). Ее значением является содержимое указанной таблицы.

SQL2 позволяет комбинировать результаты всех трех конструкций с помощью следующих операций:

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

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

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

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

Операции UNION, INTERSECT и EXCEPT

Операции union, intersect и except в SQL2 представляют три базовые операции создания одной выходной таблицы из двух входных: сложение, вычитание и пересечение. Все три операции требуют, чтобы входные таблрщы бьиш совместимыми - имели одинаковое количество столбцов и соответствующие столбцы имели одинаковые типы данных. Вот несколько примеров запросов в SQL2, в которых используются эти три операции.

Вывести список товаров, для которых имеются заказы на сумму более $30000, а также тех товаров, которых на складе имеется на сумму более $30000.

(SELECT MFR, PRODUCT

FROM ORDERS

WHERE AMOUNT > 30000.00) UNION

(SELECT MFR ID, PRODUCT ID

FROM PRODUCTS

WHERE (PRICE * QTY ON HAND) > 30000)

Вывести список товаров, для которых имеются заказы на сумму более $30000 и которых при этом имеется на складе на сумму более $30000.

(SELECT MFR, PRODUCT FROM ORDERS WHERE AMOUNT > 30000.00) INTERSECT

(SELECT MFR ID, PRODUCT ID FROM PRODUCTS WHERE (PRICE * QTY ON HAND) > 30000)

Вывести список товаров, для которых имеются заказы на сумму более $30000, jq исключением тех, которые стоят менее $1000.

(SELECT MFR, PRODUCT FROM ORDERS WHERE AMOUNT > 30000.00) EXCEPT

(SELECT MFR ID, PRODUCT ID FROM PRODUCTS / WHERE PRICE < 1000)

По умолчанию операции union, intersect и except удаляют из результирующей таблицы повторяющиеся строки. Как правило, это хорошо; в частности, в приведенных выше примерах именно такой результат и нужен, но в некоторых случаях может потребоваться оставить все повторяющиеся строки. Для этого используются спещ1аль-ные формы трех операщга: union all, intersect all и except all.

Обратите внимание на то, что в каждом из приведенных примеров создается таблица с двумя столбцами. Ее строки извлекаются из двух разных таблиц: orders и products. Однако столбцы этих таблиц имеют одинаковые типы данных, так что к ним могут бьггь применены операции сложения, вычитания и пересечения. В нашей учебной базе данных соответствующие столбцы имеют разные имена (например, вдентификатор производителя в таблице orders назьшается mfr, а в таблице products - mfr id). Но на практике подобные столбцы часто называются одинаково. В таких случаях стандарт SQL2 позволяет задать соответствующие столбцы один раз в предложении corresponding (введенном просто ради удобства), которое можно включать в операции union, intersect и except. Вот модификация предыдущего примера с операцией union для ситуации, когда столбцы идентификаторов продуктов в таблицах orders и products названы одинаково:

Вывести список товаров, для которых имеются заказы на сумму более $30000, а также тех товаров, которых на складе имеется на сумму более $30000.

(SELECT *

FROM ORDERS WHERE AMOUNT > 30000.00) UNION CORRESPONDING BY (MFR, PRODUCT) (SELECT *

FROM PRODUCTS WHERE (PRICE * QTY ON HAND) > 30000)

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

(SELECT *

FROM ORDERS

WHERE AMOUNT > 30000.00) UNION CORRESPONDING (SELECT *

FROM PRODUCTS WHERE (PRICE * QTY ON HAND) > 30000)

Наконец, в любой из спецификаций запросов, результаты которых объединяются с помощью операции union, можно задать псевдонимы столбцов. Тогда для объединения таблиц orders и products в их исходном виде, с разными именами столбцов, можно будет также использовать операцию union corresponding;

(SELECT *

FROM ORDERS WHERE AMOUNT > 30000.00) UNION CORRESPONDING

(SELECT MFR ID AS MFR, PRODUCT ID AS PRODUCT FROM PRODUCTS WHERE (PRICE * QTY ON HAND) > 30000)

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

Запросы в предложении FROM

Составной запрос в SQL2 - это гораздо более мощное и гибкое средство создания и комбинирования таблиц результатов запроса, нежели обычный механизм подчиненных запросов и операция union, предлагавшиеся стандартом SQL1. Чтобы сделать составные запросы еще более универсальными, SQL2 разрешает использовать их практически везде, где в запросах SQL1 допускались ссылки на таблицы. В частности, запрос может указываться вместо имени таблицы в предложении from. Вот простейший пример запроса на языке SQL2, в котором используется эта возможность:

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

SELECT COMPANY, TOT ORDERS

FROM CUSTOMERS, (SELECT CUST, SUM (AMOUNT) AS TOT ORDERS FROM ORDERS GROUP BY CUST) WHERE (CREDIT LIMIT > 50000.00) AND (CUST NUM = CUST)

Если первая спецификация таблицы в предложении from - это, как обычно, имя таблицы базы данных, то вторая спецификация - вовсе не имя таблицы, а полноценный запрос. Фактически последний может быть намного сложнее и включать собственные операции union или join. Когда запрос включается в предложение from, как в данном примере, СУБД сначала выполняет этот запрос и создает временную таблицу его результатов, а затем выполняет внешний запрос, используя в нем созданную таблицу в качестве источника данных точно так же, как она использовала бы обычную таблицу, хранящуюся в базе данных. В нашем примере содержимым временной таблицы является информация, извлеченная из таблицы orders, и к этой информации присоединяется таблица customers для получения названий компаний.