 |
Подзапросы в логических выражениях WHERE и HAVING
|
|
|
|
Подзапросы могут использоваться в правой части логического предиката IN. Например, следующий запрос определяет преподавателей, которые преподают какие-либо курсы:
SELECT name, position FROM Staff
WHERE name IN (SELECT staff FROM Course); (8.1)
| NAME | POSITION |
| G.Anderson | Senior Lector |
| Won Kim | Full Professor |
На результирующее множество – значение подзапроса – накладываются естественные ограничения: количество и порядок полей и их типы в выборке подзапроса должны соответствовать типу значения в левой части предиката. Количество записей и количество дубликатов каждой записи в выборке подзапроса значения не имеет, но если выборка пустая, то значение предиката будет FALSE. В левой части предиката, помимо атомарного значения, можно использовать список значений, как в следующем запросе:
SELECT name, position FROM Staff
WHERE (name, 'Graphics')
IN (SELECT staff, title FROM Course); (8.2)
Процесс вычисления запроса 8.1 можно представить следующим образом. Сначала вычисляется подзапрос и образуется список значений, который затем «подставляется» в правую часть предиката IN, а затем этот предикат проверяется для каждой строки исходной таблицы. Иными словами, можно было бы сначала вычислить запрос SELECT DISTINCT staff, title FROM Course, а затем использовать его значение ('G.Anderson' и 'Won Kim') в тексте следующего эквивалентного запроса:
SELECT name, position FROM Staff
WHERE name IN ('G.Anderson', 'Won Kim'); (8.3)
Для многих (но не для всех) запросов с подзапросами можно написать эквивалентный запрос с соединением, например, для запроса 8.1 эквивалентным будет следующий запрос:
SELECT DISTINCT S.name, S.position
FROM Staff S JOIN Course C ON C.staff = S.name; (8.4)
Использование подзапросов, очевидно, является более наглядным, и упрощает написание текста сложных запросов. Рассмотрим, однако, следующий запрос с соединением:
|
|
|
SELECT DISTINCT S.name, S.position, C.title
FROM Staff S JOIN Course C ON C.staff = S.name; (8.5)
Эквивалентный запрос с подзапросами невозможно написать, поскольку не существует таблицы, в которой бы присутствовали все три требуемых поля, а «передать» значения из подзапроса во внешний запрос и поместить это значение в результирующее множество нельзя. Можно сказать, что подзапросы предназначены для того, чтобы проверить наличие данных из внешнего запроса в других таблицах.
Однако подзапросы могут использовать данные из таблиц, используемых во внешнем SELECT-выражении. Рассмотрим запрос, определяющий имя и должность преподавателя, проводящего курс ‘OS’:
SELECT name, position FROM Staff
WHERE name IN (SELECT name FROM Course
WHERE Staff.name = staff AND title = 'OS'); (8.6)
| NAME | POSITION |
| G.Anderson | Senior Lector |
В запросе 8.6 внутри текста подзапроса используется значение поля Staff.name. Такие подзапросы называются коррелированными. При вычислении внешнего запроса подзапрос должен вычисляться для каждой строки таблицы внешнего запроса, а не один раз для всех строк, как это было для предыдущих подзапросов (называемых соответственно некоррелированными), и значения подзапросов для каждой строки внешней таблицы будут различными. Можно сказать, что коррелированный подзапрос выполняет неявное соединение таблицы подзапроса и таблицы внешнего запроса. Так, запрос 8.6 эквивалентен следующему запросу:
SELECT S.name, S.position FROM Staff S, Course C
WHERE S.name = C.staff AND C.title = 'OS'; (8.7)
Запрос 8.6 можно преобразовать в более короткий, хотя и менее очевидный вид:
SELECT name, position FROM Staff
WHERE 'OS' IN
(SELECT title FROM Course
WHERE Staff.name = staff); (8.8)
Несмотря на то, что в левой части предиката IN присутствует константа, подзапрос по-прежнему является коррелированным.
Следующий запрос 8.9 определяет студента с максимальной оценкой:
SELECT student, course, result FROM CourseResult
WHERE result =
(SELECT MAX(result) FROM CourseResult) (8.9)
| STUDENT | COURSE | RESULT |
| Bill | C Programming | |
| Jane | OS |
|
|
|
Подзапросы можно использовать и в операциях сравнения. Например, запрос 8.9 определяет, какие студенты посещают более одного курса:
SELECT S.name, S.class FROM Student S
WHERE (SELECT COUNT(*) FROM Attends
WHERE student = S.name) > 1; (8.10)
| NAME | CLASS |
| Jane | |
| Bill |
Запрос 8.11 позволяет определить, какие студенты посещают более одного курса, читаемого преподавателем 'G.Anderson':
SELECT S.name, S.class FROM Student S
WHERE (SELECT COUNT(*) FROM Attends A
WHERE A.student = S.name AND 'G.Anderson' IN
(SELECT staff FROM Course
WHERE A.title = title))> 1; (8.11)
| NAME | CLASS |
| Jane |
На использование подзапросов в операциях сравнения накладываются некоторые дополнительные ограничения. В качестве примера рассмотрим два схожих запроса с некоррелированными подзапросами:
SELECT title, staff from Course WHERE staff =
(SELECT name FROM Staff
WHERE position = 'Senior Lector'); (8.12)
SELECT title, staff from Course WHERE staff =
(SELECT name FROM Staff
WHERE position = 'Full Professor'); (8.13)
Оба запроса являются синтаксически корректными, и запрос 8.12 действительно будет выполнен, сформировав следующую выборку:
| TITLE | STAFF |
| DBMS | G.Anderson |
| Graphics | G.Anderson |
| OS | G.Anderson |
Но при исполнении запроса 8.13 (при текущем состоянии данных в таблице Staff) возникнет следующая ошибка:
ORA-01427: single-row subquery returns more than one row.
Это означает, что выборка подзапроса содержит более одной строки; сравнение одиночных значений с множествами в языке SQL не предусмотрено.
Таким образом, при использовании подзапросов в операциях сравнения необходимо, чтобы (1) значение подзапроса состояло не более чем из одной записи, и (2) эта запись была совместима по типам полей со сравниваемым значением. Кстати, запись не обязана быть атомарным значением (т.е. состоять из одного поля); например, можно использовать следующий (несколько искусственный) вариант, использующий сравнение списочных выражений, одно из которых является подзапросом:
SELECT title, staff from Course
WHERE (staff, 'Senior Lector') =
(SELECT name, position FROM Staff
WHERE position = 'Senior Lector') (8.14)
Возвращаясь к запросам 8.12 и 8.13, следует отметить, что подобный тип запросов является не вполне удачным, поскольку его исполнение существенно зависит от конкретных значений используемых параметров (значения, сравниваемого с полем position) и текущего состояния таблицы Staff. Более безопасным является использование эквивалентного варианта с предикатом IN (8.15) или с коррелированным подзапросом (8.16), которые для любых данных будут работать корректно:
|
|
|
SELECT title, staff from Course WHERE staff IN
(SELECT name FROM Staff
WHERE position = 'Full Professor'); (8.15)
SELECT title, staff from Course WHERE staff IN
(SELECT name FROM Staff WHERE Staff.name = staff
AND position = 'Full Professor'); (8.16)
Запрос 8.15 с предикатом IN является более предпочтительным, поскольку запросы с некоррелированными подзапросами выполняются более эффективно.
Еще одним полезным предикатом является предикат EXISTS, который в качестве параметра получает подзапрос и возвращает значение TRUE, если результирующее множество подзапроса содержит хотя бы одну запись.
Следующий запрос определяет, по каким курсам существуют «задолжники»:
SELECT staff, title FROM Course C WHERE EXISTS
(SELECT * FROM CourseResult
WHERE course = C.title AND
(result IS NULL OR result < 3)) (8.17)
| STAFF | TITLE |
| G.Anderson | Graphics |
| G.Anderson | DBMS |
При использовании подзапросов совместно с предикатом EXISTS конкретный вид выражений после SELECT в подзапросе никакого значения не имеет; однако было бы ошибкой использовать, например, функцию COUNT(*), поскольку в этом случае подзапрос всегда содержал бы одну строку.
При помощи предиката EXISTS удобно находить «несвязанные» записи (т.е. выполнять неявное антисоединение); ниже приведен подобный запрос, эквивалентный для запроса 7.15:
SELECT C.title "Курс", staff "Преподавателя" FROM Course C
WHERE NOT EXISTS
(SELECT * FROM Attends A WHERE C.title = A.title) (8.18)
В сравнении с запросом 7.15, текст данного запроса выглядит более очевидным.
С подзапросами связано использование двух предикатов ANY и ALL, которые употребляются в операторах сравнения и в качестве параметра, аналогично предикату IN, используют подзапрос или списочное выражение. Например, следующий запрос определяет студентов с оценкой, которая не является минимальной из существующих (по любому курсу):
SELECT student, course, result FROM CourseResult
WHERE result > ANY
(SELECT result FROM CourseResult) (8.19)
| STUDENT | COURSE | RESULT |
| Bill | C Programming | |
| Jane | OS |
При вычислении этого запроса сначала вычисляется подзапрос, который является некоррелированным, и формируется список значений, а затем для каждой строки проверяется предикат ANY – если значение поля result текущей записи больше хотя бы одного значения из списка, то значение операции сравнения является TRUE. Обратите внимание, что пустые значения, которые присутствуют в значении подзапроса SELECT result FROM CourseResult, игнорируются.
|
|
|
Может показаться, что запрос 8.19 определяет студентов с максимальной оценкой. Однако это верно только применительно к текущим данным в таблице CourseResult. В ней, за исключением игнорируемых пустых значений в поле result, встречаются только значения 4 и 5, хотя возможно и появление значения 3. В этом случае значение запроса 8.19 не будет совпадать со значением запроса 8.9, который всегда будет возвращать правильный результат. Данное наблюдение иллюстрирует тот факт, что «правильность» некоторого SQL-запроса трудно оценить только методом тестирования, вычисляя его с некоторыми конкретными данными.
Предикат ALL, в отличие от предиката ANY, требует, чтобы условие сравнения выполнялось для всех значений из списка – значения подзапроса. В качестве примера построим запрос для нахождения студентов с максимальной оценкой, аналогично запросу 8.9.
SELECT student, course, result FROM CourseResult CR
WHERE result >= ALL
(SELECT result FROM CourseResult CR2
WHERE CR2.result IS NOT NULL) (8.20)
| STUDENT | COURSE | RESULT |
| Bill | C Programming | |
| Jane | OS |
Обратите внимание, что в запросе 8.20 подзапрос использует ту же самую таблицу, что и основной запрос, поэтому здесь необходимо использовать оператор сравнения >=, поскольку значение этого подзапроса в данном случае всегда включает значение result из левой части. Данный запрос можно переписать таким образом, чтобы для каждой записи внешней таблицы исключать из данных подзапроса эту запись; для этого будет необходимо использовать коррелированный подзапрос. Кроме того, этот запрос определяет максимальную оценку вне зависимости от курса; если необходимо получить максимальные оценки по каждому курсу в отдельности, запрос приобретет существенно более сложный вид:
SELECT student, course, result FROM CourseResult CR
WHERE result > ALL
(SELECT result FROM CourseResult CR2 WHERE NOT
(CR.student = CR2.student AND CR.course = CR2.course)
AND CR.course = CR2.course
AND CR2.result IS NOT NULL)
AND CR.result IS NOT NULL (8.21)
| STUDENT | COURSE | RESULT |
| Bill | C Programming | |
| Jane | OS | |
| Jane | DBMS |
Прежде всего, необходимо обратить внимание на условие CR.student = CR2.student AND CR.course = CR2.course, которое в обычных условиях используется как условие соединения двух таблиц по паре полей. Но в данном случае это условие используется с отрицанием, поскольку цель этого коррелированного подзапроса – построить неявное кросс-соединение таблицы с собой, удаляя для каждой записи внешней таблицы эту же запись из внутренней таблицы, чтобы избежать сравнения записи самой с собой. Проверка CR.result IS NOT NULL необходима, чтобы результирующее множество не содержало сведений о курсах, по которым не выставлено ни одной оценки. Наконец, условие CR.course = CR2.course (пятая строка текста запроса) необходимо, чтобы подзапрос каждый раз рассматривал только записи с одинаковыми названиями курса. Текст данного запроса можно переписать в более короткую форму, упростив логическое выражение (читателю рекомендуется самостоятельно выполнить шаги преобразования этого логического выражения):
|
|
|
SELECT student, course, result FROM CourseResult CR
WHERE result > ALL
(SELECT result FROM CourseResult CR2 WHERE
CR.student!= CR2.student
AND CR.course = CR2.course
AND CR2.result IS NOT NULL)
AND CR.result IS NOT NULL (8.22)
В конце подраздела рассмотрим еще несколько сложных примеров. Ниже приведены три запроса, одинаковых по значению, которые с содержательной точки зрения определяют, какие студенты посещают оба указанных курса, каждый из которых использует соответственно соединение, подзапросы с предикатом IN и подзапросы с предикатом EXISTS.
SELECT DISTINCT S1.name, S1.class FROM
Student S1 JOIN Attends A1 ON A1.student = S1.name JOIN
Attends A2 ON A2.student = S1.name
WHERE A1.title = 'OS' AND A2.title = 'DBMS' (8.23)
SELECT name, class FROM Student (8.24)
WHERE name in
(SELECT student FROM Attends WHERE title = 'OS')
AND name in
(SELECT student FROM Attends WHERE title = 'DBMS')
SELECT S.name, S.class FROM Student S
WHERE EXISTS
(SELECT * FROM Attends WHERE student = S.name
AND title = 'OS')
AND EXISTS
(SELECT * FROM Attends WHERE student = S.name
AND title = 'DBMS') (8.25)
| NAME | CLASS |
| Jane |
Во всех трех вариантах из таблицы Attends производятся две независимых выборки (для каждого курса в отдельности) - либо через повторное соединение, либо при помощи независимых подзапросов; в запросе 8.25 подзапросы коррелированные, поэтому этот вариант является менее предпочтительным.
Подзапросы после FROM
Подзапросы могут использоваться вместо имен таблиц в SELECT-запросах. При вычислении подобных запросов сначала вычисляется подзапрос, а затем значение подзапроса используется как исходная таблица при вычислении основного запроса. Рекомендуется в таком подзапросе явно указывать имена полей создаваемого результирующего множества, чтобы эти имена можно было далее использовать в выражениях основного запроса.
Следующий простейший пример формально иллюстрирует применение подобных подзапросов:
SELECT * FROM (SELECT * FROM Staff); (8.26)
Конечно, использование запроса 8.26 вряд ли можно считать целесообразным, поскольку этот запрос эквивалентен обычному запросу SELECT * FROM Staff, и здесь приводится исключительно для иллюстрации синтаксиса этого способа использования подзапросов. Следующий запрос 8.27 является более содержательными и демонстрирует применение подзапросов для дополнительного структурирования текста, однако и этот запрос может быть переписан без использования подзапроса:
SELECT "Имя", "Должность", "Количество" FROM
(SELECT S.name "Имя", S.position "Должность",
COUNT(*) "Количество"
FROM Staff S, Course C
WHERE C.staff = S.name
GROUP BY S.name, S.position)
WHERE "Имя" = 'Won Kim'; (8.27)
| Имя | Должность | Количество |
| Won Kim | Full Professor |
Следует обратить внимание, что приводимый ниже запрос 8.28 нельзя реализовать без использования подзапросов, поскольку результирующее множество каждого подзапроса группируется отдельно, а затем с ними выполняется соединение. С содержательной точки зрения, этот запрос формирует список преподавателей и посещающих их курсы студентов, причем для каждого студента и преподавателя вычисляется количество посещаемых или читаемых ими всего курсов соответственно.
SELECT SC."Имя", SC."Должность", SC."Кол.курсов",
AC."Студент", AC."Кол.курсов" FROM
(SELECT S.name "Имя", S.position "Должность",
COUNT(*) "Кол.курсов"
FROM Staff S JOIN Course C ON C.staff = S.name
GROUP BY S.name, S.position) SC
JOIN
(SELECT A.student "Студент", C.staff "Преподаватель",
COUNT(*) "Кол.курсов"
FROM Attends A JOIN Course C ON A.title = C.title
GROUP BY A.student, C.staff) AC
ON SC."Имя" = AC."Преподаватель"; (8.28)
| Имя | Должность | Кол.курсов | Студент | Кол.курсов |
| G.Anderson | Senior Lector | Polie | ||
| G.Anderson | Senior Lector | Jane | ||
| G.Anderson | Senior Lector | Bill | ||
| Won Kim | Full Professor | Bill |
Раздел 9. Оператор CASE
Оператор CASE предназначен для замены одних значений на другие в зависимости от выполнения логических условий, и напоминает операторы IF и SWITCH в процедурных языках программирования. Оператор CASE имеет два варианта, синтаксис и использование первого варианта иллюстрирует следующий запрос 9.1:
SELECT DISTINCT student,
CASE student WHEN 'Bill' THEN student || '-Boy'
WHEN 'Polie' THEN 'My Lovely ' || student
WHEN 'Jane' THEN 'Naughty ' || student
ELSE student || '...Who is that? '
END "An Opinion"
FROM CourseResult (9.1)
| STUDENT | An Opinion |
| Bill | Bill -Boy |
| Jane | Naughty Jane |
| Polie | My Lovely Polie |
Выражение после служебного слова CASE вычисляется и затем последовательно сравнивается со значениями, перечисляемыми в каждой конструкции WHEN; при совпадении с одним из этих значений в качестве значения оператора берется значение выражения после соответствующего THEN. Если значение ни в одном из выражений WHEN не найдено, используется значение после слова ELSE. Конструкцию CASE завершает ключевое слово END.
Во втором варианте оператора CASE после слова WHEN используется логическое выражение, а не литеральная константа, как в первом варианте:
SELECT student || ' gets ' || CASE WHEN Result = 5 THEN 'A'
WHEN Result = 4 THEN 'B'
WHEN Result = 3 THEN 'C'
WHEN Result IS NULL THEN 'nothing'
ELSE '...What is that? '
END "Result"
FROM CourseResult (9.2)
| Result |
| Bill gets A |
| Polie gets nothing |
| Jane gets A |
| Jane gets B |
| Bill gets nothing |
Существует также функция DECODE, аналогичная оператору CASE; эта функция использовалась в более ранних версиях Oracle. Ниже приведен запрос 9.3, аналогичный запросу 9.2, реализованный с использованием функции DECODE:
SELECT student || ' gets ' ||
DECODE(Result, 5, 'A',
4, 'B',
3, 'C',
NULL, 'nothing',
'...What is that? ') "Result"
FROM CourseResult (9.3)
|
|
|
