3.3.1 Простые запросы и выборки из одной таблицы

3.3.1.1 Запрос: выбрать всю информацию о сотрудниках "Корпорации Кинга".

SELECT * 
FROM employee

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

3.3.1.2 Запрос: выбрать список фамилий сотрудников с кодами отделов, в которых они работают.

SELECT last_name, department_id 
  FROM employee

Здесь в списке выборки перечисляются имена тех столбцов, которые необходимо выбрать - реляционная операция проекции.

3.3.1.3 Запрос: выбрать сотрудников-продавцов (код должности продавца - 670).

SELECT * 
  FROM employee
  WHERE job_id=670

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

3.3.1.4 Запрос: выбрать список фамилий сотрудников-продавцов с кодами отделов, в которых они работают:

SELECT last_name, department_id 
  FROM employee
  WHERE job_id=670

В этом запросе применены и ограничение, и проекция.

3.3.1.5 Запрос: выбрать список штатов, в которых имеются покупатели "Корпорации Кинга".

SELECT DISTINCT state
  FROM customer

Обратите внимание на слово DISTINCT в этом запросе. Без него мы получим 33 строки, во многих из которых названия штатов будут повторяться. Со словом DISTINCT результирующая выборка будет содержать всего 5 строк без повторений.

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

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

SELECT last_name, commission/salary*100 
  FROM employee

Если вы выполните эту выборку, то обратите внимание, что во второй колонке результирующей таблицы у многих сотрудников будет пустое место (Oracle) или прочерк (DB2). Многие сотрудники не получают комиссионные, у них значение в столбце commission - NULL, следовательно, и значение выражения, которое мы выбираем, тоже NULL. Если мы хотим, чтобы у таких сотрудников во второй колонке стояло число 0, нам придется прибегнуть к некоторому усложнению запроса. DB2 позволяет решить эту задачу в простом запросе при помощи операции CASE:

SELECT last_name, 
       CASE 
         WHEN commission IS NULL THEN 0
         ELSE commission/salary*100 
       END
  FROM employee

В Oracle операции CASE нет, один из возможных вариантов решения для Oracle рассматривается в пп. 3.3.3.9.

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

Запрос: выбрать для каждого сотрудника его два инициала с точкой и фамилию, например, W.J.SMITH.

SELECT SUBSTR(first_name,1,1)||'.'||
       Middle_initial||'.'||last_name 
  FROM employee

3.3.1.8 Условия ограничения, задаваемые фразой WHERE, могут быть сколь угодно сложными за счет применения в них логических операций NOT, AND, OR.

Запрос: выбрать тех сотрудников, которые имеют код должности 670 и работают в отделе 23, но не получают комиссионных.

SELECT last_name
  FROM employee
  WHERE job_id=670
  AND department_id=23
  AND commission IS NOT NULL

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

Запрос: выбрать те товары, которые связаны с теннисом. Мы исходим из того, что в названии таких товаров должно быть слово 'TENNIS'.

SELECT description
   FROM product
   WHERE description LIKE '%TENNIS%'

Выберется:

ACE TENNIS RACKET I
ACE TENNIS RACKET II
ACE TENNIS BALLS-3 PACK
ACE TENNIS BALLS-6 PACK
ACE TENNIS NET
SP TENNIS RACKET
RH: "GUIDE TO TENNIS"

3.3.1.10 Стоит подробнее остановиться на запросах, связанных с датой, так как этот тип данных по-разному обрабатывается в разных СУБД.

Запрос: выбрать тех сотрудников, которые поступили на работу до 15 апреля 1985 года. В принципе его решение одинаково для обеих СУБД.

Для Oracle:

SELECT last_name, hire_date 
  FROM employee 
  WHERE hire_date<'15-APR-1985'

Для DB2:

SELECT last_name, hire_date 
  FROM employee 
  WHERE hire_date<'15/04/1985'

3.3.1.11 Запрос: сколько времени проработал в "Корпорации Кинга" сотрудник ADAMS?

Решение для Oracle:

SELECT SYSDATE-hire_date
  FROM employee
  WHERE last_name='ADAMS'

Даст нам на сегодняшний день (22 апреля 2004г):

6310.3415

Решение для DB2:

SELECT CURRENT DATE-hire_date
  FROM employee
  WHERE last_name='ADAMS'

Даст:

170310.

Оба результата нельзя назвать удобочитаемыми. Попробуем преобразовать их в удобный вид.

В Oracle для этого придется прибегнуть к достаточно сложным преобразованиям:

SELECT 
    /* число лет вычисляется как 
       частное от деления разности месяцев на 12 */
    FLOOR( MONTHS_BETWEEN(
     SYSDATE,hire_date)/12) AS year,
   /* число месяцев вычисляется как 
       остаток от деления разности месяцев на 12 */
    FLOOR( MOD(
     MONTHS_BETWEEN(
      SYSDATE,hire_date),12)) AS month,
   /* число дней вычисляется как разность текущей даты
       и дней в полных прошедших месяцах */
    FLOOR(SYSDATE- 
     ADD_MONTHS(hire_date,
      MONTHS_BETWEEN(SYSDATE,hire_date))) AS day
  FROM employee
   WHERE last_name='ADAMS'

Результат будет:

     YEAR     MONTH       DAY
--------- --------- ---------
       17         3        10

В DB2 аналогичный результат можно использовать десятичную арифметику: выделить десятичные разряды из 8-значной десятичной разности дат (формат разности - YYYYMMDD):

SELECT 
   --- старшие 4 разряда - число лет
    INTEGER(CURRENT DATE-hire_date)/10000 AS year,
   --- следующие 2 разряда - число месяцев
    MOD(INTEGER(CURRENT DATE-hire_date),10000)/100 
       AS month,
   --- следующие 2 разряда - число дней
    MOD(INTEGER(CURRENT DATE-hire_date),100) AS day
  FROM employee
   WHERE last_name='ADAMS'

Можно также преобразовать разность дат в символьную строку ('YYYYMMDD.') и выделять отдельные части строки:

SELECT
    SUBSTR(CHAR(CURRENT DATE-hire_date),1,4) 
      AS year,
    SUBSTR(CHAR(CURRENT DATE-hire_date),5,2) 
      AS month,
    SUBSTR(CHAR(CURRENT DATE-hire_date),7,2) 
      AS day
  FROM employee
   WHERE last_name='ADAMS'

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

SELECT
    YEAR(CURRENT DATE-hire_date) AS year,
    MONTS(CURRENT DATE-hire_date) AS month,
    DAY(CURRENT DATE-hire_date) AS day
  FROM employee

3.3.1.12 Предикат IN позволяет проверить принадлежность значения к множеству значений.

Запрос: выбрать фамилии всех сотрудников, имеющих код должности 667, 668 или 670.

SELECT last_name, job_id
  FROM employee
  WHERE job_id IN (667,668,670)

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

SELECT last_name, job_id
  FROM employee
  WHERE job_id=667 OR job_id=668 OR job_id=670

3.3.1.13 Агрегатные функции могут применяться по отношению ко всей выборке или к ее ограничению.

Запрос: выбрать максимальную, минимальную и среднюю зарплату продавцов (код должности - 670) в "Корпорации Кинга".

SELECT MAX(salary), MIN(salary), AVG(salary)
  FROM employee
  WHERE job_id=670

3.3.1.14 Можно вычислять общие показатели и по группам.

Запрос: выбрать максимальную, минимальную и среднюю зарплату продавцов по каждому отделу в "Корпорации Кинга".

SELECT department_id, MAX(salary), 
       MIN(salary), AVG(salary)
  FROM employee
  WHERE job_id=670
  GROUP BY department_id

3.3.1.15 Часто бывает необходимо применять агрегатные функции в условии фразы HAVING. Запрос: выбрать покупателей, которые сделали больше 10 заказов.

SELECT customer_id, COUNT(*) 
  FROM sales_order
  GROUP BY customer_id
  HAVING COUNT(*)>10

3.3.2 Соединение таблиц и выборки из нескольких таблиц

3.3.2.1 Результатом выполнения оператора

SELECT *
   FROM location, department

будет:

LOCATION_ID REGIONAL_GROUP DEPARTMENT_ID NAME       LOCATION_ID
----------- -------------- ------------- ---------- -----------
122         NEW YORK       10            ACCOUNTING 122
124         DALLAS         10            ACCOUNTING 122
123         CHICAGO        10            ACCOUNTING 122
167         BOSTON         10            ACCOUNTING 122
122         NEW YORK       20            RESEARCH   124
124         DALLAS         20            RESEARCH   124
123         CHICAGO        20            RESEARCH   124
167         BOSTON         20            RESEARCH   124
122         NEW YORK       30            SALES      123
124         DALLAS         30            SALES      123
123         CHICAGO        30            SALES      123
167         BOSTON         30            SALES      123
122         NEW YORK       40            OPERATIONS 167
124         DALLAS         40            OPERATIONS 167
123         CHICAGO        40            OPERATIONS 167
167         BOSTON         40            OPERATIONS 167
122         NEW YORK       12            RESEARCH   122
124         DALLAS         12            RESEARCH   122
123         CHICAGO        12            RESEARCH   122
167         BOSTON         12            RESEARCH   122
122         NEW YORK       13            SALES      122
124         DALLAS         13            SALES      122
123         CHICAGO        13            SALES      122
167         BOSTON         13            SALES      122
122         NEW YORK       14            OPERATIONS 122
124         DALLAS         14            OPERATIONS 122
123         CHICAGO        14            OPERATIONS 122
167         BOSTON         14            OPERATIONS 122
122         NEW YORK       23            SALES      124
124         DALLAS         23            SALES      124
123         CHICAGO        23            SALES      124
167         BOSTON         23            SALES      124
122         NEW YORK       24            OPERATIONS 124
124         DALLAS         24            OPERATIONS 124
123         CHICAGO        24            OPERATIONS 124
167         BOSTON         24            OPERATIONS 124
122         NEW YORK       34            OPERATIONS 123
124         DALLAS         34            OPERATIONS 123
123         CHICAGO        34            OPERATIONS 123
167         BOSTON         34            OPERATIONS 123
122         NEW YORK       43            SALES      167
124         DALLAS         43            SALES      167
123         CHICAGO        43            SALES      167
167         BOSTON         43            SALES      167

Как видно из приведенного результата, простое перечисление в фразе FROM нескольких таблиц дает декартово произведение этих таблиц. Мы нарочно привели результат в полном объеме, чтобы продемонстрировать, что такая выборка будет чрезвычайно избыточной. Для того чтобы выполнить более информативное соединение таблиц, следует применять ограничение, задаваемое фразой WHERE. Имеет смысл соединять в одной строке название отдела не с любым городом, а только с тем городом, в котором он расположен. Таблицы location и department связаны между собой через столбец location_id, который является первичным ключом в location и внешним ключом - в department. "Осмысленный" запрос можно сформулировать так: вывести список отделов с указанием города, в котором отдел находится. Решение будет иметь вид:

SELECT name, regional_group
   FROM location, department
   WHERE location.location_id = department.location_id

и даст результат:

NAME           REGIONAL_GROUP
-------------- --------------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON
RESEARCH       NEW YORK
SALES          NEW YORK
OPERATIONS     NEW YORK
SALES          DALLAS
OPERATIONS     DALLAS
OPERATIONS     CHICAGO
SALES          BOSTON

Для DB2 возможен и другой вариант решения:

SELECT name, regional_group
   FROM location JOIN department
      ON location.location_id = 
             department.location_id

3.3.2.2 Запрос: вывести список штатов, в которых находятся покупатели, обслуживаемые отделами, расположенными в городе NEW YORK.

В данном случае мы имеем дело с соединением, ограниченным дополнительным условием (город NEW YORK). Информация о покупателях и штатах находится в таблице customer, название города, в котором расположен отдел - в таблице location. Прямой связи между этими двумя таблицами нет, но эта связь может быть установлена по цепочке: location - department - employee - customer. Таким образом, для того, чтобы выполнить запрос, нужно выполнить естественное соединение всех таблиц, участвующих в указанной цепочке, а затем ограничить соединение дополнительным условием. Поскольку в таблице customer почти наверняка имеется несколько покупателей в каждом штате, имеет смысл исключить повторение названия одного и того же штата для разных покупателей.

SELECT DISTINCT state
 FROM customer, employee, department, location
 WHERE salesperson_id = employee_id
 AND employee.department_id = 
            department.department_id
 AND department.location_id = location.location_id
 AND regional_group = 'NEW YORK'

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

1. Определить код города NEW YORK.
2. Выбрать коды тех отделов, для которых код города совпадает с кодом, полученным на шаге 1.
3. Выбрать коды сотрудников, которые работают в отделах, выбранных на шаге 2.
4. Выбрать покупателей, которых обслуживают сотрудники, определенные на шаге 3, и выбрать штаты размещения этих покупателей.

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

SELECT DISTINCT state
  FROM customer
  WHERE salesperson_id IN
    (SELECT employee_id 
      FROM employee
      WHERE department_id IN
        (SELECT department_id
          FROM department
          WHERE location_id=
            (SELECT location_id
              FROM location
              WHERE regional_group = 'NEW YORK' )))

Какое решение лучше? С точки зрения формулирования, лучше тот запрос, который нам легче сформулировать и понять. С точки зрения эффективности лучше первое решение. В документации обеих наших СУБД указывается, что запросы с соединением эффективнее запросов с вложенными подзапросами, но "SQL-машина" автоматически преобразовывает вторые в первые. Таким образом, соображения эффективности не являются значимыми, а важна только семантическая ясность запроса.

Следует указать на еще один возможный вариант решения:

SELECT DISTINCT state
 FROM customer, employee, department, 
     (SELECT location_id
        FROM location
         WHERE regional_group = 'NEW YORK' ) tmp
 WHERE salesperson_id = employee_id
 AND employee.department_id = 
                   department.department_id
 AND department.location_id = l.location_id

Ограничение обеспечивается вложенным запросом, который вынесен в фразу FROM. Выборка производится не из таблицы location (среди прочих), а из временной таблицы, которой присвоено имя tmp и которая содержит только код города NEW YORK.

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

Название товара находится в таблице product, цена - в таблице price. Между этими таблицами существует естественная связь через внешний-первичный ключ product_id. Но между ценой и зарплатой сотрудника (которая находится в таблице employee) никакой связи нет. Мы сами устанавливаем такую связь, причем не по простому равенству значений в столбцах, а по сложному соотношению между ними:

SELECT description, FLOOR(salary/min_price)
  FROM price, product, employee
  WHERE price.product_id=product.product_id
  AND last_name='JONES'
  AND min_price*1000<salary
  AND end_date IS NULL
  ORDER BY 2 DESC

Некоторые дополнительные пояснения к этому решению. Во-первых, мы считаем, что JONES, как сотрудник фирмы будет покупать товары по минимальной цене. Дополнительно к названию товара мы выводим и количество его экземпляров, доступное для JONES. В условии фразы WHERE первая часть задает естественное соединение таблиц price и product. Вторая часть ограничивает выборку только заданным сотрудником. Третья часть задает условие доступности 1000 экземпляров товара - это и есть дополнительное условие. Наконец, четвертая часть ограничивает принимаемые во внимание цены только теми, которые действуют на настоящий момент. Для удобства восприятия мы упорядочиваем выборку по уменьшению доступности товара.

3.3.2.4 Таблица может быть соединена и сама с собой.

Запрос: вывести фамилии всех сотрудников с указанием для каждого фамилии его непосредственного начальника.

SELECT x.last_name, y.last_name
   FROM employee x, employee y
   WHERE x.manager_id=y.employee_id
   ORDER BY 1

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

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

Запрос: выбрать коды тех отделов, в которых есть должности, имеющиеся в отделе 23. (Обратите внимание: в задаче не оговаривается, что в выбираемом отделе должны быть все должности отдела 23 и что в нем должны быть только должности из отдела 23.)

SELECT DISTINCT department_id
  FROM employee
  WHERE job_id IN
    (SELECT DISTINCT job_id
      FROM employee
      WHERE department_id=23)
  AND department_id<>23

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

3.3.3.2 Запрос: выбрать тех сотрудников, которые имеют подчиненных. Логика решения очевидна: следует выбрать идентификаторы manager_id всех, кто фигурирует в качестве начальника любого сотрудника, а затем выбрать тех сотрудников, чьи идентификаторы employee_id не входят в выбранное множество manager_id:

SELECT last_name
  FROM employee
  WHERE employee_id IN
    (SELECT DISTINCT manager_id
      FROM employee)

Однако другой запрос: выбрать тех сотрудников, которые не имеют подчиненных, решается не так просто. Казалось бы, для его решения достаточно в предыдущем запросе заменить предикат IN на NOT IN. Но при выполнении такого запроса не будет выбрано ни одной строки. Следует помнить, что результат выполнения предиката IN будет FALSE, если в проверяемом множестве есть хотя бы одно значение NULL. Поэтому правильное решение такое:

SELECT last_name
  FROM employee
  WHERE employee_id IN
    (SELECT DISTINCT manager_id
      FROM employee
      WHERE manager_id IS NOT NULL)

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

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

SELECT last_name, salary
  FROM employee
  WHERE job_id=667
  AND salary< 
      (SELECT AVG(salary)
        FROM employee
        WHERE job_id=667)

Как и в примере пп.3.3.2.2, здесь возможно перенесение вложенного запроса в фразу FROM:

SELECT last_name, salary
  FROM employee,
       (SELECT AVG(salary) AS avs
         FROM employee
         WHERE job_id=667)
  WHERE job_id=667
  AND salary<avs 

3.3.3.4 Запрос: определить в каждом отделе самого высокооплачиваемого сотрудника.

SELECT last_name, employee.department_id, salary
  FROM employee,
       (SELECT department_id,MAX(salary) AS ms
                  FROM employee
                  GROUP BY department_id) t
  WHERE employee.department_id=t.department_id
  AND salary=ms

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

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

SELECT last_name, salary-avs
  FROM employee,
      (SELECT AVG(salary) AS avs
        FROM employee
        WHERE job_id=667)
  WHERE job_id=667

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

SELECT last_name, 
       salary-(SELECT AVG(salary) AS avs
                FROM employee
                WHERE job_id=667)
  FROM employee
  WHERE job_id=667

3.3.3.6 Если в фразе FROM табличному выражению присваивается псевдоним, то на этот псевдоним нельзя ссылаться в фразе FROM вложенного запроса. Запрос: выбрать название товара, которого в 1990 г. было продано максимальное количество экземпляров. Решение (для Oracle):

SELECT description, sss
  FROM product,
    (SELECT product_id, SUM(quantity) sss
      FROM item, sales_order
      WHERE TO_CHAR(order_date,'YYYY')='1990'
      AND item.order_id=sales_order.order_id      
      GROUP BY product_id) t
  WHERE t.product_id=product.product_id
  AND sss=
    (SELECT MAX(sss) 
       FROM (SELECT product_id,SUM(quantity) AS sss 
              FROM item, sales_order
              WHERE 
                TO_CHAR(order_date,'YYYY')='1990'
              AND item.order_id=sales_order.order_id
              GROUP BY product_id)
           )

Логика этого решения следующая. Мы создаем временную таблицу t, в которой для каждого товара указывается его код и суммарное количество экземпляров, проданное в 1990 г. Product_id в этой таблице является внешним ключом, который ссылается на таблицу product. Выполняется естественное соединение временной таблицы с таблицей product. Результат соединения ограничивается теми строками, в которых количество проданных экземпляров совпадает с максимальным количеством проданных экземпляров. В этом решении у нас есть два совершенно идентичных вложенных подзапроса: один - формирующий временную таблицу в фразе FROM, другой - вложенный в условие ограничения. Сослаться в условии ограничения на таблицу t нельзя, приходится повторять уже имеющийся в запросе подзапрос. Это, конечно, не означает, что повторяющийся вложенный подзапрос выполняется дважды (вспомните про оптимизатор в "SQL-машине"), но запись решения становится довольно громоздкой. В разделе 3.5 мы увидим, как DB2 позволяет сократить запись для таких случаев.

3.3.3.7 Подзапрос в предикате IN может быть использован и для формирования множества запрещенных значений.

Запрос: выбрать товары, которые когда-то продавались, но сейчас не продаются. Решить эту задачу можно, используя информацию из таблицы price. В каждой строке таблицы есть информация о начале действия цены (столбец start_date) и о конце ее действия (столбец end_date). Строки, в которых значение в столбце end_date не определено, описывают цены, действующие в настоящий момент. Строки, в которых значение в этом столбце определено - отмененные цены. Типичной ошибкой начинающего SQL-программиста является запрос вида:

SELECT product_id
   FROM price
   WHERE end_date IS NOT NULL

Такой запрос выберет товары, цены на которые отменялись, но вслед за отменой для товара могла быть назначена новая цена, которая, возможно, действительна и сегодня. То есть, для этого товара может быть и другая строка в таблице price с неопределенным значением в этом столбце. Логика правильного решения задачи, которая формулируется в общем виде, как "выбрать объекты, которые НЕ удовлетворяют какому-то условию", такова: выбрать объекты, которые удовлетворяют этому условию и исключить их из выборки всех объектов. Вот решения, которые следуют этой логике:

SELECT product_id
  FROM product
  WHERE product_id NOT IN
    (SELECT product_id
      FROM price
      WHERE end_date IS NULL)

или

SELECT product_id
  FROM product
MINUS
SELECT product_id
  FROM price
  WHERE end_date IS NULL

(Операция MINUS применяется в Oracle, эквивалентная операция в DB2 - EXCEPT.)

3.3.3.8 Запрос: выбрать коды тех заказов, в которых одновременно заказывались товары с кодами 104351 и 104362.

SELECT order_id
  FROM item
  WHERE product_id=104351
INTERSECT
SELECT order_id
  FROM item
  WHERE product_id=104362

Логика решения: выбрать множество заказов товара 104351, выбрать множество заказов товара 104362, а затем определить пересечение этих множеств.

Другой вариант решения таков:

SELECT DISTINCT order_id
  FROM item
  WHERE order_id IN
    (SELECT order_id 
      FROM item
      WHERE product_id=104351)
  AND order_id IN
    (SELECT order_id 
      FROM item
      WHERE product_id=104362)

Логика второго решения очень сходная с логикой первого: выбрать те заказы, которые входят и во множество заказов товара 10451, и во множество заказов товара 10462.

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

3.3.3.9 Запрос: для каждого сотрудника выбрать сумму его комиссионных и зарплаты; для тех сотрудников, которые комиссионных не получают, выбирается только зарплата. "Ловушка" в этой задаче состоит в том, что если значение комиссионных NULL, то и сумма зарплаты и комиссионных тоже составляет NULL. Мы уже решали подобную задачу в пп.3.3.1.6, используя выражение CASE. Но выражение CASE доступно только в DB2, поэтому попробуем теперь обойтись без него.

SELECT last_name, commission+salary
  FROM employee
  WHERE commission IS NOT NULL
UNION
SELECT last_name, salary
  FROM employee
  WHERE commission IS NULL
ORDER BY 1

Мы выбираем фамилию и процентное отношение для тех, у кого есть комиссионные, выбираем фамилию и 0 для тех, у кого нет комиссионных, а затем объединяем эти две выборки. Обратите внимание: операция ORDER BY относится уже к результату объединения. Каждая из объединяемых выборок перед объединением упорядочена быть не может.

3.3.3.10 Следует, однако, предупредить об одной возможной ошибке при применении операций UNION, INTERSECT и MINUS (EXCEPT). Рассмотрим ее на таком запросе: определить средний доход (зарплата + комиссионные) сотрудников фирмы. Казалось бы, в основу решения можно положить предыдущее решение:

SELECT AVG(s) 
  FROM (
   SELECT commission+salary AS s
     FROM employee
     WHERE commission IS NOT NULL
   UNION
   SELECT salary AS s
     FROM employee
     WHERE commission IS NULL
  )

Но правильным будет такое решение:

SELECT AVG(s) 
  FROM (
   SELECT commission+salary AS s
     FROM employee
     WHERE commission IS NOT NULL
   UNION ALL
   SELECT salary AS s
     FROM employee
     WHERE commission IS NULL
   )

Выполните эти два запроса в любой СУБД и убедитесь, что первое решение даст меньший результат. Решения отличаются тем, что в первом применена операция UNION, а во втором - UNION ALL. В первом решении при выполнении объединения были исключены все повторения одной и той же суммы, что привело к искажению правильного результата. Здесь проявляется некоторая непоследовательность языка SQL: по умолчанию в выборках допускается дублирование строк и для исключения дубликатов нужно применять специальное ключевое слово (DISTINCT). При выполнении же реляционных операций такое исключение производится по умолчанию и специальное ключевое слово (ALL) нужно применять для сохранения дубликатов.

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

3.3.4.1 Запрос: выбрать в каждом отделе самого высокооплачиваемого сотрудника. Мы уже предлагали решение этого запроса в пп.3.3.3.4. Альтернативное решение:

SELECT department_id, last_name, salary
  FROM employee x
  WHERE salary >= ALL
        (SELECT salary 
          FROM employee
          WHERE employee.department_id=x.department_id)
  ORDER BY 1

Концепцию выполнения этого запроса можно описать следующим образом. При выполнении внешнего запроса перебираются строки таблицы employee. Для каждой выбранной во внешнем запросе строки выполняется вложенный запрос, в котором выбираются зарплаты всех сотрудников, работающих в том же отделе, что и сотрудник, строка которого выбрана во внешнем запросе. Если зарплата сотрудника, выбранного во внешнем запросе, больше или равна всем зарплатам, выбранным во вложенном запросе, строка попадает в результирующую таблицу. Во внешнем запросе мы присвоили таблице employee псевдоним x. Во вложенном запросе имя x.department_id относится к значению в строке, выбранной во внешнем запросе, а имя employee.department_id - к значению, выбираемому во вложенном запросе.

Если бы коррелированные запросы выполнялись в соответствии с логикой их построения, они выполнялись бы довольно медленно, так как вложенный запрос должен выполняться для каждой строки внешнего запроса. Но помните про "умную SQL-машину", которая оптимизирует наш запрос и приводит его к наиболее эффективной форме. На самом деле лишь очень небольшое число задач обязательно требует коррелированных запросов.

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

SELECT department_id
 FROM department
 WHERE NOT EXISTS
  (SELECT * 
    FROM employee
    WHERE department_id=23
    AND job_id NOT IN
     (SELECT job_id 
      FROM employee
      WHERE employee.department_id=
              department.department_id
     )
  )
 AND department_id<>23

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

employee[department_id,job_id] 
DIVIDE BY 
(employee WHERE department_id=23)[job_id]

Но операции реляционного деления в SQL нет, поэтому приходится прибегать к сложной логике построения запроса. Перебираются строки таблицы department, и для каждого выбранного отдела проверяется: нет ли в таблице employee такой должности в отделе 23 (первый вложенный запрос), которой бы не было в выбранном отделе (второй вложенный запрос).