Подзапросы могут быть многократно вложенными.
ЗАПРОСЫ К НЕСКОЛЬКИМ ТАБЛИЦАМ
До этого каждый запрос основывался на одной таблице. В этой главе показано, как построить запрос к любому числу таблиц.
ОБЪЕДИНЕНИЕ ТАБЛИЦ
Одна из наиболее важных возможностей SQL - это способность в одном запросе определять связи между несколькими таблицами и выводить информацию из них в соответствии с этими связями. Этот вид операции называется объединением и является одним из основных для реляционных баз данных.
При объединении таблицы, представленные в списке предложения FROM запроса, разделяются запятыми. Предикат запроса может использовать любой столбец любой связанной таблицы, т.е. он может использоваться для связи между таблицами. Как правило, при проверке установленного условия WHERE сравниваются значения столбцов различных таблиц.
ИМЕНА ТАБЛИЦ И ИМЕНА СТОЛБЦОВ
Фактически, полное имя столбца таблицы состоит из имени таблицы, последующей точки и самого имени столбца.
Пример:
employee_data.salary
В запросах по одной таблице можно опускать имена таблиц, т.к. SQL самостоятельно присвоит соответствующий префикс - имя таблицы. В многотабличных запросах тоже можно опускать имена таблиц, если все их столбцы имеют различные имена.
СОЗДАНИЕ ОБЪЕДИНЕНИЯ
Предположим, что нужно поставить заказчиков в соответствие продавцам в городе проживания, чтобы получить все комбинации продавцов и заказчиков для конкретного города. Для этого необходимо выбирать всех продавцов по очереди и выбирать в таблице Заказчики всех заказчиков того же самого города. Это можно сделать следующим запросом:
|
|
|
SELECT Customers.cname, Salespeople.sname, Salespeople.city
FROM Salespeople, Customers
WHERE Salespeople.city = Customers.city;
Попробовать!!!
ОБЪЕДИНЕНИЕ ТАБЛИЦ НА ОСНОВЕ ССЫЛОЧНОЙ ЦЕЛОСТНОСТИ
Эта возможность часто используется для реализации связей, встроенных в базу данных. В предыдущем примере в объединении установлена связь между двумя таблицами через «общее» поле SNUM. Такая связь называется состоянием ссылочной целостности.
Используя объединение, можно получать данные на основе этой связи.
Например, чтобы получить имена всех заказчиков и обслуживающих их продавцов, можно использовать следующий запрос:
SELECT Customers.cname, Salespeople.sname
FROM Customers, Salespeople
WHERE Salespeople.snum = Customers.snum;
Попробовать!!!
ОБЪЕДИНЕНИЕ ПО РАВЕНСТВУ И ДРУГИЕ ЕГО ВИДЫ
Объединения, использующие предикаты равенства, называются объединениями по равенству.
Строки "city='London'" и "Salespeople.snum=Orders.snum" - примеры таких равенств, включенных в предикаты.
В объединении можно использовать любой из реляционных операторов.
|
|
|
Пример другого вида объединения:
SELECT sname, cname
FROM Salespeople, Customers
WHERE sname < cname AND rating < 200;
Этот запрос редко бывает полезен. Он выводит все комбинации продавцов и заказчиков, в которых имя продавца предшествует имени заказчика по алфавиту, а заказчик имеет рейтинг меньше 200. Обычно, сложные связи, подобные этой, не создаются, а используются наиболее общие объединения по равенству, но необходимо знать и о таких возможностях.
ОБЪЕДИНЕНИЕ БОЛЕЕ ДВУХ ТАБЛИЦ
Можно создавать запросы, объединяющие больше двух таблиц.
Предположим, что необходимо найти заказы заказчиков находящихся в городах, отличных от городов продавцов. Для этого необходимо связать все три учебные таблицы:
SELECT onum, cname, Orders.cnum, Orders.snum
FROM Orders, Salespeople, Customers
WHERE Customers.city <> Salespeople.city AND
Orders.cnum = Customers.cnum AND
Orders.snum = Salespeople.snum;
Попробовать!!!
ОБЪЕДИНЕНИЕ ТАБЛИЦЫ С САМОЙ СОБОЙ
Методика объединения двух и более таблиц может быть использована при объединении вместе двух копий одной таблицы.
КАК ОБЪЕДИНИТЬ ТАБЛИЦУ САМУ С СОБОЙ?
Для объединения таблицы с самой собой можно сделать каждую запись таблицы, одновременно, как комбинацией с собой, так и комбинацией с каждой другой записью таблицы. Затем, как и в объединении нескольких таблиц, оценивается предикат для каждой комбинации. Это позволяет создать определенные виды связей между различными записями внутри одной таблицы с помощью обнаруженных пар строк с одинаковым значением поля.
|
|
|
Например, объединение таблицы с самой собой можно изобразить, как объединение двух копий одной и той же таблицы.
Таблица на самом деле не копируется, но SQL выполняет запрос так, как если бы это было сделано. Другими словами, это объединение - такое же, как и любое другое объединение двух таблиц, за исключением того, что в данном случае обе таблицы идентичны.
ПСЕВДОНИМЫ
Синтаксис запроса, объединяющего таблицу саму с собой, тот же, что и для запроса, объединяющего нескольких таблиц.
При объединении таблицы с самой собой, все повторяющиеся имена столбцов дополняются префиксом имени таблицы. Чтобы использовать имена столбцов внутри запроса, необходимо иметь два различных имени для одной таблицы. Это можно сделать с помощью определения временных имен, называемых псевдонимами. Они определяются в предложении FROM запроса: вводится имя таблицы, пробел и псевдоним.
Вот пример запроса, находящего все пары заказчиков, имеющих одинаковый рейтинг:
|
|
|
SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating;
Где здесь псевдоним?
Попробовать!!!
Псевдонимы first и second (таблица Заказчики) были установлены сразу после имени таблицы в предложении FROM запроса.
Псевдонимы используются в предложении SELECT еще до их определения в предложении FROM.
Псевдоним существует до тех пор, пока выполняется запрос! Когда выполнение запроса закончено, использованные в нем псевдонимы перестают существовать.
УСТРАНЕНИЕ ИЗБЫТОЧНОСТИ
Вывод в примере имеет два значения для каждой комбинации, причем второй раз в обратном порядке. Это происходит оттого, что каждое значение выбрано первый раз из первого псевдонима, а второй раз (симметрично) из второго псевдонима. Т.е. значение A в первом псевдониме сначала выбирается в комбинации со значением B во втором псевдониме, а затем значение A во втором псевдониме выбирается в комбинации со значением B в первом псевдониме.
Например, Hoffman выбран с Clemens, а затем Clemens выбран с Hoffman, Cisneros и Grass, Liu и Giovanni, и т.д.
Кроме того, каждая запись была связана сама с собой, что дает такие строки, как Liu и Liu.
Простой способ избежать этого, состоит в том, чтобы наложить некий порядок на два сравниваемых значения, так, чтобы один мог бы быть меньше другого или предшествовал ему по алфавиту. Это делает предикат асимметричным и те же самые значения в обратном порядке не будут выбраны повторно
Пример:
SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating AND first.cname < second.cname;
Попробовать!!!
Hoffman предшествует Pereira в алфавитном порядке, поэтому комбинация удовлетворяет обеим условиям предиката и появляется в выводе. Когда эта же комбинация проверяется в обратном порядке, т.е. Periera первого псевдонима сравнивается с Hoffman второго псевдонима, то второе условие не выполняется. Аналогично, Hoffman-Hoffman не выбирается по равенству рейтингов, т.к. его имя не предшествует ему самому в алфавитном порядке. Если нужно включить сравнение в запросах строк с ними же самими, подобно этому, то вместо < можно использовать <=.
ПРОВЕРКА ОШИБОК
Т.к. каждый заказчик должен обслуживаться одним и тем же продавцом, то при вводе записи в таблицу Заказы с определенным номером заказчика, в ней должен быть соответствующий номер продавца.
Следующий запрос определяет любые несогласованности этих номеров:
SELECT first.onum, first.cnum, first.snum, second.onum, second.cnum, second.snum
FROM Orders first, Orders second
WHERE first.cnum = second.cnum AND first.snum <> second.snum;
Этот запрос выглядит сложно, но его логика достаточно проста. Он берет первую запись таблицы Заказы, запоминает ее под первым псевдонимом и проверяет в объединении с каждой записью таблицы Заказы со вторым псевдонимом. Если объединение записей удовлетворяет истинности предиката, то они выводятся. Т.е. предикат начнет рассматривать объединения, найдет объединение в котором CNUM=2008 и SNUM=1007, а затем рассмотрит каждое следующее объединение с тем же значением CNUM=2008. Если будет найдено, что какая-то из них имеет значение SNUM, отличное от 1007, то предикат примет истинное значение, и выбранные поля будут выведены для текущего объединения записей. Если же значение SNUM с данным значением CNUM в таблице имеет совпадения, то этот запрос не даст никакого вывода.
БОЛЬШЕ ПСЕВДОНИМОВ
Можно использовать псевдонимы и в других случаях.
Например, если таблицы имеют очень длинные и сложные имена, то можно определить простые односимвольные псевдонимы (типа A или B) и использовать их вместо имен в предложении SELECT и предикатах.
НЕКОТОРЫЕ БОЛЕЕ СЛОЖНЫЕ КОМПЛЕКСНЫЕ ОБЪЕДИНЕНИЯ
Запрос позволяет найти все возможные комбинации заказчиков, которые уже использованы:
SELECT a.cnum, b.cnum, c.cnum
FROM Customers a, Customers b, Customers c
WHERE a.rating = 100 AND b.rating = 200 AND c.rating = 300;
Попробовать!!!
Этот запрос находит все сочетания заказчиков с тремя значениями рейтинга, поэтому первый столбец состоит из заказчиков с рейтингом 100, второй с 200 и последний с рейтингом 300. Они повторяются во всех возможных комбинациях. Это - сортировка группировки, которая не может быть выполнена с GROUP BY или ORDER BY, поскольку они сравнивают значения только в одном столбце вывода.
В предложении SELECT не обязательно использовать все псевдонимы или таблицы, упомянутые в предложении FROM. Иногда псевдоним или таблица используются только потому, что они входят в предикат запроса.
Например, следующий запрос находит всех заказчиков, размещенных в городах, где продавец Serres (SNUM=1002) имеет заказчиков:
SELECT b.cnum, b.cname
FROM Customers a, Customers b
WHERE a.snum = 1002 AND b.city = a.city;
Можно создавать объединения, включающие как различные таблицы, так и псевдонимы одной таблицы.
Следующий запрос объединяет таблицу Заказчики саму с собой для нахождения пар заказчиков, обслуживаемых одним продавцом. В то же время запрос объединяет заказчика с таблицей Продавцов по имени продавца:
SELECT sname, Salespeople.snum, first.cname, second.cname
FROM Customers first, Customers second, Salespeople
WHERE first.snum = second.snum AND Salespeople.snum = first.snum AND first.cnum < second.cnum;
Попробовать!!!
ВСТАВКА ОДНОГО ЗАПРОСА ВНУТРЬ ДРУГОГО
КАК РАБОТАЕТ ПОДЗАПРОС?
В языке SQL можно вкладывать запросы внутрь друга друга.
Предположим, например, что известно имя продавца: Motika, но для него не известно значение поля SNUM, а нужно получить все заказы из таблицы Заказы:
SELECT *
FROM Orders
WHERE snum =
(SELECT snum
FROM Salespeople
WHERE sname = 'Motika');
Попробовать!!!
Чтобы выполнить внешний (основной) запрос, SQL сначала должен выполнить внутренний запрос (подзапрос) предложения WHERE. Он выполняет его так же, как обычный запрос, имеющий единственную цель - отыскать в таблице Продавцы все записи, где поле SNAME равно значению Motika, и извлечь значения поля SNUM этих записей.
SQL не просто выдает это значение, а помещает его в предикат основного запроса на место подзапроса, так, чтобы предикат получил следующее значение:
WHERE snum = 1004
Затем выполняется, как обычно, основной запрос с полученным результатом.
Конечно, если известен номер продавца Motika, то можно просто ввести:
WHERE snum = 1004
и далее выполнить запрос в целом, но это не так универсально. Запрос с подзапросом будет выполняться, даже если номер Motika изменился.
DISTINCT С ПОДЗАПРОСАМИ
В некоторых случаях можно использовать DISTINCT, чтобы заставить подзапрос давать только одно значение
Предположим, что нужно найти все заказы продавцов, обслуживающих Hoffman (CNUM=2001):
SELECT *
FROM Orders
WHERE snum =
(SELECT DISTINCT snum
FROM Orders
WHERE cnum = 2001);
Попробовать с DISTINCT и без!!!
ПРЕДИКАТЫ И ПОДЗАПРОСЫ НЕЛЬЗЯ ПЕРЕСТАВЛЯТЬ МЕСТАМИ
Предикаты, включающие подзапросы, используют формат:
<скалярное выражение> <оператор> <подзапрос>
но, не
<подзапрос> <оператор> <скалярное выражение>
или
<подзапрос> <оператор> <подзапрос>.
Т.е., предыдущий запрос нельзя записать, как:
SELECT *
FROM Orders
WHERE (SELECT DISTINCT snum
FROM Orders
WHERE cnum = 2001) = snum;
ИСПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ В ПОДЗАПРОСАХ
Агрегатные функции дают одиночный результат при любом количестве обработанных записей. Поэтому любой запрос без предложения GROUP BY и использующий одиночную агрегатную функцию, будет выбирать одиночное значение, которое можно использовать в предикате.
Например, необходимо получить заказы, имеющие сумму покупки выше средней за 4-е Октября:
SELECT *
FROM Orders
WHERE amt >
(SELECT AVG(amt)
FROM Orders
WHERE odate = 10/04/1990);
Необходимо иметь в виду то, что сгруппированные агрегатные функции, определенные в предложениях GROUP BY, могут давать несколько значений. Следовательно, они не могут быть использованы в подзапросах такого типа.
ПРЕДЛОЖЕНИЕ HAVING
Предположим, что необходимо получить максимальную сумму Заказа, значение которой более $3000.00. В предложении WHERE нельзя использовать агрегатную функцию, т.к. предикаты оцениваются для отдельной записи, а агрегатные функции оцениваются для группы записей. Это означает, что невозможно создать запрос, подобный следующему:
SELECT snum, odate, MAX(amt)
FROM Orders
WHERE MAX(amt) > 3000.00
GROUP BY snum, odate;
Это отклонение от строгой интерпретации ANSI. Чтобы получить максимальную стоимость заказов более $3000.00, можно использовать предложение HAVING.
Предложение HAVING определяет критерии, удаляющие определенные группы записей из вывода, точно так же, как предложение WHERE делает это для отдельных записей.
Следующий запрос будет правильным:
SELECT snum, odate, MAX(amt)
FROM Orders
GROUP BY snum, odate
HAVING MAX(amt) > 3000.00;
Аргументы предложения HAVING следуют тем же правилам, что и предложения SELECT с использованием GROUP BY. Они должны иметь одно значение на группу.
Следующая команда запрещена:
SELECT snum, MAX(amt)
FROM Orders
GROUP BY snum
HAVING odate = 10/03/1988;
Предложение HAVING не может обработать поле ОDATE, т.к. оно может иметь (и действительно имеет) более, чем одно значение в группе. Чтобы избежать этого, предложение HAVING должно использовать агрегатные функции и поля, определенные GROUP BY.
Исправленный запрос выглядит следующим образом:
SELECT snum, MAX(amt)
FROM Orders
WHERE odate = 10/03/1990
GROUP BY snum;
Например, необходимо получить наибольшие заказы для Serres и Rifkin: SELECT snum, MAX(amt)
FROM Orders
GROUP BY snum
HAVING snum IN (1002,1007);
Воз вращаемся к подзапросам
Например, следующий запрос, находящий среднее значение комиссионных продавца в Лондоне:
SELECT AVG(comm)
FROM Salespeople
GROUP BY city
HAVING city = 'London';
не может быть использован в подзапросе!
Лучше сделать это другим способом:
SELECT AVG(comm)
FROM Salespeople
WHERE city = 'London';
ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ В ОПЕРАТОРЕ IN
В специальном операторе IN можно использовать подзапросы, производящие любое число записей (операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами). Оператор IN определяет набор значений, одно из которых должно совпадать с другим операндом предиката для его истинности.
Получить все данные из таблицы Заказы для продавца в Лондоне:
SELECT *
FROM Orders
WHERE snum IN
(SELECT snum
FROM Salespeople
WHERE city = 'London');
Выполняется намного проще, чем объединение:
SELECT onum, amt, odate, cnum, Orders.snum
FROM Orders, Salespeople
WHERE Orders.snum = Salespeople.snum AND Salespeople.city = 'London';
Например, рассмотрим запрос, похожий на предыдущий:
SELECT onum, amt, odate
FROM Orders
WHERE snum =
(SELECT snum
FROM Orders
WHERE cnum = 2001);
Используя IN вместо оператора равенства (=) можно исключить потребность в DISTINCT:
SELECT onum, amt, odate
FROM Orders
WHERE snum IN
(SELECT snum
FROM Orders
WHERE cnum = 2001);
Что случится, если допущена ошибка, и один из заказов был назначен двум продавцам? Версия запроса, использующая IN, будет выдавать заказы для обоих. Т.к. простого способа поиска такой ошибки нет, то полученные отчеты или решения, сделанные на основе его результатов, будут содержать ошибку. Выполнение запроса с оператором равенства (=) закончится неудачей, которая укажет на наличие ошибки. Потом можно найти ошибку, выполнив подзапрос отдельно и проанализировав значения, которые он дает.
Если известно, что подзапрос должен дать только одно значение, то должен использоваться оператор равенства (=).
Оператор IN подходит, если запрос может давать одно и более значений, независимо от того ожидаются они или нет.
Предположим, что нужно получить комиссионные продавцов, обслуживающих заказчиков Лондона:
SELECT comm
FROM Salespeople
WHERE snum IN
(SELECT snum
FROM Customers
WHERE city = 'London');
В предыдущем примере префикс таблицы для поля CITY необязателен, несмотря на возможную неоднозначность между полями CITY таблицы Заказчики и таблицы Продавцы. SQL всегда ищет первое поле в таблице, имеющейся в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, то проверяются внешние запросы. Использование CITY в предложении WHERE означает, что это - Customers.city (поле CITY таблицы Заказчики).
Т.к. таблица Заказчики указана в предложении FROM текущего запроса, то SQL предполагает, что это правильно. Это предположение может быть отменено полным именем таблицы или префиксом псевдонима.
ПОДЗАПРОСЫ ВЫБИРАЮТ ОДИНОЧНЫЕ СТОЛБЦЫ
Все рассмотренные подзапросывыбирают одиночный столбец. Это является обязательным требованием, т.к. полученный выход сравнивается с одиночным значением. Следовательно, в подзапросе не может использоваться SELECT *. Исключением являются подзапросы, использующиеся с оператором EXISTS
ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ В ПОДЗАПРОСАХ
В предложении SELECT подзапроса можно использовать не только сам столбец, но и выражение, включающее имя столбца. Это может быть выполнено с помощью или реляционных операторов, или оператора IN.
Например, следующий запрос использует реляционный оператор равенства:
SELECT *
FROM Customers
WHERE cnum =
(SELECT snum+1000
FROM Salespeople
WHERE sname = 'Serres');
Находятся заказчики, чьи значения поля CNUM на 1000 больше значения поля SNUM заказчика Serres. Предполагается, что столбец SNAME не имеет дублирующихся значений
ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING
Подзапросы можно использовать внутри предложения HAVING. Эти подзапросы могут использовать как агрегатные функции, так и GROUP BY или HAVING, если они не производят многочисленных значений.
Пример:
SELECT rating, COUNT(DISTINCT cnum)
FROM Customers
GROUP BY rating
HAVING rating >
(SELECT AVG(rating)
FROM Customers
WHERE city = 'San Jose');
Запрос подсчитывает заказчиков в San Jose с рейтингом выше среднего. Т.к. имеются рейтинги, отличные от 300, то они должны быть выведены с количеством заказчиков, имеющим этот рейтинг.
ИСПОЛЬЗОВАНИЕ ОПЕРАТОРА EXISTS
В этой главе рассказывается о первом специальном операторе SQL, который использует подзапрос как аргумент. Остальные будут описан в следующей главе.
Оператор EXISTS используется для указания предикату, производит ли подзапрос вывод или нет.
КАК РАБОТАЕТ EXISTS ?
Оператор EXISTS может принимать значения истина или ложь, т.е. это логический оператор.
Это означает, что он может присутствовать в предикате автономно или в комбинации с другими логическими выражениями, использующими операторы AND, OR или NOT. Он использует подзапрос в качестве аргумента и принимает значение истина, если подзапрос дает какой-либо вывод или ложь, если тот не дает вывода. Этим он отличается от других операторов предиката. В подзапросе он должен всегда иметь какое-либо значение.
Например, можно решить, извлекать ли некоторые данные из таблицы Заказчики если, и только если один или более заказчиков этой таблице находятся в San Jose:
SELECT cnum, cname, city
FROM Customers
WHERE EXISTS
(SELECT *
FROM Customers
WHERE city = 'San Jose');
Внутренний запрос выбирает все данные всех заказчиков в San Jose.
Оператор EXISTS во внешнем предикате отмечает, что подзапросом был произведен некоторый вывод, и поскольку выражение EXISTS - единственный член предиката, то присваивает предикату значение истина.
ВЫБОР СТОЛБЦОВ С ПОМОЩЬЮ EXISTS
В приведенном выше примере EXISTS должен быть построен так, чтобы выбирать один столбец вместо всех (SELECT *). В этом состоит его отличие от подзапроса, который может выбрать только один столбец.
КОМБИНАЦИЯ ИЗ EXISTS И ОБЪЕДИНЕНИЯ
Однако, может оказаться, что полезнее вывести большее количество информации об этих продавцах, а не только их номера.
Это можно сделать, объединив, таблицы Заказчики и Продавцы:
SELECT DISTINCT first.snum, sname, first.city
FROM Salespeople first, Customers second
WHERE EXISTS
(SELECT *
FROM Customers third
WHERE second.snum = third.snum AND second.cnum <> third.cnum) AND first.snum = second.snum;
Здесь внутренний запрос, как и в предыдущем варианте, фактически сообщает, что псевдоним был изменен. Внешний запрос - это объединение таблицы Продавцы с таблицей Заказчики.
Новое предложение основного предиката (AND first.snum=second.snum) проверяется на том же самом уровне, что и предложение EXISTS.
ИСПОЛЬЗОВАНИЕ NOT EXISTS
Предыдущий пример показал, что EXISTS может работать в комбинации с логическими операторами. Оператор NOT является самым простым и наиболее часто используемым с EXISTS.
Вот один из способов нахождения всех продавцов, имеющих одного заказчика. Он состоит в том, что предыдущий пример инвертируется:
SELECT DISTINCT snum
FROM Customers outer
WHERE NOT EXISTS
(SELECT *
FROM Customers inner
WHERE inner.snum = outer.snum AND inner.cnum <> outer.cnum);
EXISTS И АГРЕГАТНЫЕ ФУНКЦИИ
Единственно, что не может сделать EXISTS - это использовать агрегатную функцию в подзапросе.
Подзапросы могут быть многократно вложенными.
ИСПОЛЬЗОВАНИЕ ОПЕРАТОРОВ ANY, ALL И SOME
После изучения оператора EXISTS перейдем к трем специальным операторам, также использующим подзапросы.
ANY, ALL и SOME напоминают EXISTS, использующий подзапросы в качестве аргументов, однако они отличаются от него тем, что используются совместно с реляционными операторами. В этом они напоминают оператор IN, используемый с подзапросами. Они получают все значения, выведенные подзапросом, и обрабатывают их как единый блок данных. Однако, в отличие от IN, они могут использоваться только с подзапросами.
СПЕЦИАЛЬНЫЕ ОПЕРАТОРЫ ANY ИЛИ SOME
Операторы SOME и ANY - взаимозаменяемы, т.е. там, где может использоваться ANY, будет точно так же работать и SOME. Различные термины введены для того, чтобы дать возможность использовать тот термин, который понимается наиболее однозначно. Это может привести к возникновению проблем, т.к. интуиция иногда вводит в заблуждение.
Вот новый способ нахождения продавцов и заказчиков, размещенных в городах продавцов:
SELECT *
FROM Salespeople
WHERE city = ANY
(SELECT city
FROM Customers);
Оператор ANY получает все значения, выведенные подзапросом (для этого случая - это все значения CITY таблицы Заказчики), и использует любое (ANY) из них для сравнения на равенство со значением города текущей записи внешнего запроса. Результат сравнения служит основанием для определения истинности предиката. Это означает, что подзапрос должен выводить значения того же типа, как и те, которые используются для сравнения в основном предикате. Этим ANY отличается от EXISTS, который определяет, выводит ли подзапрос результаты или нет, но фактически их не использует.
НЕОДНОЗНАЧНОСТЬ ANY
значение меньшее ANY - это значение меньшее, чем наибольшее выбранное значение;
значение большее ANY - это значение большее, чем наименьшее выбранное значение.
См. материал для студентов
СПЕЦИАЛЬНЫЙ ОПЕРАТОР ALL
При использовании оператора ALL предикат принимает истинное значение, если каждое значение, выданное подзапросом, удовлетворяет условию в предикате внешнего запроса.
Если необходимо вывести только тех заказчиков, чьи рейтинги выше, чем у каждого заказчика в Риме, то можно использовать следующее:
SELECT *
FROM Customers
WHERE rating > ALL
(SELECT rating
FROM Customers
WHERE city = 'Rome');
Можно использовать EXISTS для построения альтернативной формулировки такого же запроса:
SELECT *
FROM Customers outer
WHERE NOT EXISTS
(SELECT *
FROM Customers inner
WHERE outer.rating <= inner.rating AND inner.city = 'Rome');
ИСПОЛЬЗОВАНИЕ ПРЕДЛОЖЕНИЯ UNION
В предыдущих главах обсуждались различные способы, которыми одни запросы могут помещаться внутрь других. Существует другой способ объединения нескольких запросов - формирование из них объединения.
В этой главе показано использование предложения UNION, который отличается от подзапросов тем, что в нем ни один из двух (и более) запросов не управляет другим запросом. Все запросы выполняются независимо друг от друга, а уже их вывод объединяется.
ОБЪЕДИНЕНИЕ НЕСКОЛЬКИХ ЗАПРОСОВ В ОДИН
Например, для получения всех продавцов и заказчиков, размещенных в Лондоне и вывода их одним списком, можно построить:
SELECT snum, sname
FROM Salespeople
WHERE city = 'London' UNION
SELECT cnum, cname FROM Customers
WHERE city = 'London';
Попробовать!!!
Видно, что столбцы двух запросов, выведены так, как если бы запрос был один. Заголовки исключены потому, что ни один из столбцов, выведенных объединением, не был извлечен из одной и только одной таблицы.
Кроме того, только последний запрос заканчивается точкой с запятой. Отсутствие точки с запятой дает SQL информацию о том, что имеется еще один или более запрос.
КОГДА МОЖНО ОБЪЕДИНЯТЬ ЗАПРОСЫ?
Если несколько запросов подвергаются объединению, то их столбцы вывода должны быть совместимы для объединения. Это означает, что все запросы должны выводить одинаковое число столбцов, и каждый столбец одного запроса должен иметь тип, совместимый с типом соответствующего столбца другого запроса.
Правила совместимости типов меняется. Стандарт ANSI следит за этим очень строго, поэтому числовые поля должны иметь одинаковый тип и размер, хотя некоторые имена, используемые в стандарте ANSI для этих типов, являются синонимами. Символьные поля должны иметь одинаковую длину (равное значению объявленной, но не обязательно используемой, длины).
Другое ограничение совместимости - это запрет NULL-значений в столбце объединения, которые запрещены для всех столбцов во всех запросах объединения. NULL-значения запрещаются условием NOT NULL. Кроме того, нельзя использовать UNION в подзапросах и агрегатные функции в предложениях SELECT запросов объединения. Этими ограничениями пренебрегают большинство СУБД.
UNION И УСТРАНЕНИЕ ДУБЛИКАТОВ
UNION автоматически исключает дубликаты записей из вывода.
Это нечто несвойственное для SQL, т.к. одиночные запросы для устранения дубликатов обычно используют DISTINCT. Например:
SELECT snum, city
FROM Customers;
Он имеет дубликат записи (SNUM=1001, CITY='London'), т.к. нет указаний SQL, требующих устранения дубликатов.
Однако если использовать UNION для объединения этого запроса с аналогичным запросом к таблице Продавцы, то дубликат записи будет устранен.
SELECT snum, city
FROM Customers UNION
SELECT snum, city FROM Salespeople;
ИСПОЛЬЗОВАНИЕ UNION C ORDER BY
До сих пор не оговаривалось, что данные нескольких запросов будут выводиться в каком-то особом порядке. Просто был показан вывод, сначала одного запроса, а затем другого.
Конечно, нельзя полагаться на вывод, происходящий в произвольном порядке. Рассмотрим наиболее простой способ сортировки вывода. Точно так же, как это делается в индивидуальных запросах, для сортировки вывода объединения можно использовать предложение ORDER BY.
ВНЕШНЕЕ ОБЪЕДИНЕНИЕ
Операция, которая часто бывает полезна - это объединение двух запросов, в котором второй выбирает записи, исключенные первым. Наиболее часто это делается для того, чтобы включить записи, которые не удовлетворили предикату при объединении таблиц. Это называется внешним объединением.
Предположим, что некоторые заказчики еще не назначены продавцам. Нужно увидеть имена и города заказчиков, имена их продавцов, но, не учитывая тех, кто еще не был назначен. Этого можно достичь, формируя объединение двух запросов, один из которых выполняет объединение, а другой выбирает заказчиков с NULL-значением в поле SNUM. Последний запрос должен вставлять пробелы в поля, соответствующие полю SNAME в первом запросе.
Использование этой методики внешнего объединения, дает возможность использовать предикаты для классификации, а не для исключения.
Пример нахождения продавцов с заказчиками, размещенными в их городах, использовался и раньше. Однако, вместо простого выбора только этих записей, вывод должен перечислять всех продавцов и указать тех, кто имел заказчиков в своих городах, а кто не имел. Следующий запрос выполнит это:
SELECT Salespeople.snum, sname, cname, comm
FROM Salespeople, Customers
WHERE Salespeople.city = Customers.city UNION
SELECT snum, sname, 'NO MATCH ', comm FROM Salespeople
WHERE NOT city = ANY
(SELECT city
FROM Customers) ORDER BY 2 DESC;
Попробовать!!!
Совместимость UNION не требует добавления дополнительного поля в первом запросе, но не во втором. Вот запрос, который добавляет строки-константы к выбранным полям и указывает, совпадает ли данный продавец с его заказчиком в его городе:
SELECT a.snum, sname, a.city, 'MATCHED '
FROM Salespeople a, Customers b
WHERE a.city = b.city UNION
SELECT snum, sname, city, 'NO MATCH' FROM Salespeople
WHERE NOT city = ANY
(SELECT city
FROM Customers ) ORDER BY 2 DESC;
Внешнее объединение с полем комментария
Дата добавления: 2020-11-29; просмотров: 72; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!