 |
Ключевые слова ANY и ALL
|
|
|
|
Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел. Если подзапросу будет предшествовать ключевое слово ALL, условие сравнения считается выполненным только в том случае, если оно выполняется для всех значений в результирующем столбце подзапроса. Если тексту подзапроса предшествует ключевое слово ANY, то условие сравнения будет считаться выполненным, если оно удовлетворяется хотя бы для какого-либо (одного или нескольких) значения в результирующем столбце подзапроса. Если в результате выполнения подзапроса будет получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY — невыполненным. Согласно стандарту ISO дополнительно можно использовать ключевое слово SOME, являющееся синонимом ключевого слова ANY.
Пример 5.22. Использование ключевых слов ANY и SOME
Найдите всех работников, чья зарплата превышает зарплату хотя бы одного работника отделения компании под номером 'вооз '.
SELECT staffNo, fName, IName, position, salary
FROM Staff
WHERE salary > SOME(SELECT salary
FROM Staff
WHERE branchNo = 'B003');
Хотя этот запрос может быть записан с использованием подзапроса, определяющего минимальную зарплату персонала отделения под номером 'ВООЗ1, после чего внешний подзапрос сможет выбрать сведения обо всем персонале компании, чья зарплата превосходит это значение (см. пример 5.20), возможен и другой подход, заключающийся в использовании ключевых слов SOME/ANY. В этом случае внутренний подзапрос создает множество значений {12000, 18000, 24000}, а внешний запрос выбирает сведения о тех работниках, чья зарплата больше любого из значений в этом множестве (фактически больше минимального значения — 12 000). Подобный альтернативный метод можно считать более естественным, чем определение в подзапросе минимальной зарплаты. Но и в том и в ином случае вырабатываются одинаковые результаты выполнения запроса, которые представлены в табл. 5.25.
|
|
|
Таблица 5.25. Результат выполнения запроса из примера 5.22
Пример 5.23. Использование ключевого слова ALL.
Найдите всех работников, чья заработная плата больше заработной платы любого работника отделения компании под номером 'вооз '.
SELECT staffNo, fName, INarae, position, salary
FROM Staff
WHERE salary > ALL{SELECT salary
FROM Staff
WHERE branchNo = 'BOG3');
В целом этот запрос подобен предыдущему. И в данном случае можно было бы использовать подзапрос, определяющий максимальное значение зарплаты персонала отделения под номером ' В О О З ', после чего с помощью внешнего запроса
выбрать сведения обо всех работниках компании, зарплата которых превышает это значение. Однако в данном примере выбран подход с использованием ключевого слова ALL. Результаты выполнения запроса представлены в табл. 5.26.
Таблица 5.26. Результат выполнения запроса из примера 5.23
5.3.7. Многотабличные запросы
Все рассмотренные выше примеры имеют одно и то же важное ограничение: помещаемые в результирующую таблицу столбцы всегда выбираются из единственной таблицы. Однако во многих случаях этого оказывается недостаточно. Для того чтобы объединить в результирующей таблице столбцы из нескольких исходных таблиц, необходимо выполнить операцию соединения. В языке SQL операция соединения используется для объединения информации из двух таблиц посредством образования пар связанных строк, выбранных из каждой таблицы.
Помещаемые в объединенную таблицу пары строк составляются по равенству входящих в них значений указанных столбцов. Если необходимо получить информацию из нескольких таблиц, то можно либо применить подзапрос, либо выполнить соединение таблиц. Если результирующая таблица запроса должна содержать столбцы из разных исхо'дных таблиц, то целесообразно использовать механизм соединения таблиц. Для выполнения соединения достаточно в конструкции FROM указать имена двух и более таблиц, разделив их запятыми, после чего включить в запрос конструкцию WHERE с определением столбцов, используемых для соединения указанных таблиц. Помимо этого, вместо имен таблиц можно использовать псевдонимы, назначенные им в конструкции FROM. В этом случае имена таблиц и назначаемые им псевдонимы должны разделяться пробелами. Псевдонимы могут использоваться с целью уточнения имен столбцов во всех тех случаях, когда возможна неоднозначность в отношении того, к какой таблице относится тот или иной столбец. Кроме того, псевдонимы могут использоваться для сокращенного обозначения имен таблиц. Если для таблицы определен псевдоним, он может применяться в любом месте, где требуется указание имени этой таблицы.
|
|
|
Пример 5.24. Простое соединение
Составьте список имен всех клиентов, которые уже осмотрели хотя бы один сдаваемый в аренду объект и сообщили свое мнение по этому поводу.
SELECT c.clientNo, fName, IName, propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo;
В этом отчете требуется представить сведения как из таблицы Client, так и из таблицы Viewing, поэтому при построении запроса мы воспользуемся механизмом соединения таблиц. В конструкции SELECT перечисляются все столбцы, которые должны быть помещены в результирующую таблицу запроса. Обратите внимание, что для столбца с номером клиента (clientNo) необходимо уточнение, поскольку такой столбец может присутствовать и в другой таблице, участвующей в соединении. Поэтому необходимо явно указать, значения какой таблицы нас интересуют. (В данном примере с тем же успехом можно было выбрать значения столбца clientNo из таблицы Viewing.) Уточнение имени осуществляется путем указания в качестве префикса перед именем столбца имени соответствующей таблицы {или ее псевдонима). В нашем примере используется значение ' с', заданное как псевдоним таблицы Client. Для формирования результирующих строк используются те строки исходных таблиц, которые имеют идентичное значение в столбце clientNo. Это условие определяется посредством задания услония поиска с.clientNo=v.clientNo. Подобные столбцы исходных таблиц называют сочетаемыми столбцами. Описанная операция эквивалентна операции соединения по равенству реляционной алгебры, обсуждавшейся в разделе 4.1.3. Результаты выполнения запроса представлены в табл. 5.27.
|
|
|
Таблица 5.27. Результат выполнения запроса из примера 5.24
Чаще всего многотабличные запросы выполняются для двух таблиц, соединенных связью типа "один ко многим" (1:*), или родительско-дочерней связью (см. раздел 11.6.2). В приведенном выше примере, включающем обращение к таблицам Client и Viewing, последние соединены именно такой связью. Каждая строка таблицы Viewing (дочерней) связана лишь с одной строкой таблицы Client (родительской), тогда как одна и та же строка таблицы Client (родительской) может быть связана со многими строками таблицы Viewing (дочерней). Пары строк, которые генерируются при выполнении запроса, представляют собой результат всех допустимых комбинаций строк дочерней и родительской таблиц. В разделе 3.2.5 было подробно описано, как в реляционной базе данных первичный и внешний ключи таблиц создают "родительско-дочернюю" связь. Таблица, содержащая внешний ключ, обычно является дочерней, тогда как таблица, содержащая первичный ключ, всегда будет родительской. Для использования родительско-дочерней связи в запросе SQL необходимо указать условие поиска, в котором будут сравниваться внешний и первичный ключи.
В примере 5.24 первичный ключ таблицы Client (с. clientNo) сравнивается с внешним ключом таблицы Viewing (v. clientNo).
Стандарт SQL дополнительно предоставляет следующие способы определения данного соединения:
FROM Client с JOIN Viewing v ON с.clientNo = v.clientNo
FROM Client JOIN Viewing USING clientNo
FROM Client NATURAL JOIN Viewing
В каждом случае конструкция FROM замещает исходные конструкции FROM и WHERE. Однако в первом варианте создается таблица с двумя идентичными столбцами clientNo, тогда как в остальных двух случаях результирующая таблица будет содержать только один столбец clientNo.
Пример 5.25. Сортировка результатов соединения таблиц
Для каждого отделения компании перечислите табельные номера и имена работников, отвечающих за какие-либо сдаваемые в аренду объекты, а также укажите объекты, за которые они отвечают.
|
|
|
SELECT s.branchNo, s.staffNo, fName, IName, propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo, propertyNo;
Для того чтобы результаты стали более удобными для чтения, полученный вывод отсортирован с использованием номера отделения в качестве старшего ключа сортировки, а табельного номера и номера собственности — в качестве младших ключей. Результаты выполнения запроса представлены в табл. 5.28.
Таблица 5.28. Результат выполнения запроса из примера 5.25
Пример 5.26. Соединение трех таблиц
Для каждого отделения компании перечислите табельные номера и имена работников, отвечающих за какие-либо сдаваемые в аренду объекты, с указанием города, в котором расположено данное отделение компании, и номеров объектов, за которые отвечает каждый работник.
SELECT b.branchNo, b.city, s.staffNo, fName, IName, propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;
В результирующую таблицу необходимо поместить столбцы из трех исходных таблиц — Branch, Staff и PropertyForRent, поэтому в запросе следует выполнить соединение этих таблиц. Таблицы Branch и Staff могут быть соединены с помощью условия b.branchNo=*s.branchNo, в результате чего отделения компании будут связаны с работающим в них персоналом. Таблицы Staff и PropertyForRent могут быть соединены с помощью условия s. staff No=p. staff No. В результате каждый работник будет связан с теми сдаваемыми в аренду объектами, за которые он отвечает. Результаты выполнения запроса представлены в табл. 5.29.
Таблица 5.29. Результаты выполнения запроса из примера 5.26
Заметим, что стандарт SQL позволяет использовать альтернативный вариант формулировки конструкций FROM и WHERE:
FROM (Branch b JOIN Staff s USING branchNo) AS bs
JOIN PropertyForRent p USING staffNo
Пример 5.27. Группирование по нескольким столбцам
Определите количество сдаваемых в аренду объектов, за которые отвечает каждый из работников компании,
SELECT s.branchNo, S.staffNo, COUNT(*) AS count
FROM Staff s, PropertyForRent p
WHERE S.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;
Чтобы составить требуемый отчет, прежде всего необходимо выяснить, кто из работников компании отвечает за сдаваемые в аренду объекты. Эту задачу можно решить посредством соединения таблиц Staff и PropertyForRent по столбцу staffNo в конструкциях FROM/WHERE. Затем необходимо сформировать группы, состоящие из номера отделения и табельных номеров его работников, для чего следует применить конструкцию GROUP BY. Наконец, результирующая таблица должна быть отсортирована с помощью задания конструкции ORDER BY. Результаты выполнения запроса представлены а табл. 5,30.
Таблица 5.30. Результат выполнения запроса из примера 5.27
|
|
|
Алфавит и словарь языка Object Pascal
Благоприятствующие агрессии ключевые раздражители
В-ДЕСЯТЫХ, КАК ОБРАЗУЮТСЯ СЛОВА В ТАТАРСКОМ ЯЗЫКЕ
Другими словами, логически соединение становится внутренним.
Ключевые идеи (Часть 1)
Ключевые идеи (Часть 2)
Ключевые идеи (Часть 3)
Ключевые моменты современной системы взглядов на менеджмент
Ключевые положения теории систем
Ключевые поля таблиц
