На практике часто встречается ситуация, когда необходимо объединять таблицу саму с собой. Запрос, выполняющий такое объединение, называется рекурсивным. Например, если мы хотим вывести все пары студентов с одинаковым рейтингом, можно записать следующий запрос:
SELECT
first.cname,
second.cname,
first.rating
FROM
Students first,
Students second
WHERE
first.rating = second.rating;
Здесь в списке вывода SELECT указываются поля cname и rating таблицы first, и поле cname таблицы second. В разделе FROM указывается, что first и second – просто псевдонимы для таблицы Students. Для выполнения запроса сервер создаст две копии таблицы Students, одну с именем first, другую с именем second, выполнит запрос так, как будто это две разные таблицы, и уничтожит копии. Естествнно, сервер физически не создает копий таблиц, но с псевдонимами в запросе он работает так, будто это две разные таблицы.
Можно заметить, что вывод запроса будет повторять каждую пару дважды - сначала «Иванов - Петров», затем «Петров - Иванов». Кроме того, вывод содержит строки «Иванов - Иванов», «Петров - Петров». Это происходит потому, что сервер берет первую строку из первого псевдонима и сравнивает ее со всеми строками из второго псевдонима. Будут выбраны строки «Иванов - Иванов» и «Иванов - Петров». Затем он переходит к следующей строке и снова сравнивает ее со всеми строками из второго псевдонима, и так далее. Будут выбраны строки «Петров - Иванов» и «Петров - Петров».
Чтобы избежать дубликатов, надо наложить еще одно условие, налагающие отношение порядка на сравниваемые строки. Например, сравнивать имена.
SELECT
first.cname,
second.cname,
first.rating
FROM
Students first,
Students second
WHERE
first.rating = second.rating AND
first.cname < second.cname;
Вложенные подзапросы
Вложенные подзапросы так же служат для получения информации из нескольких таблиц. С их помощью можно выполнять рекурсивные запросы. Для чего существует два способа сделать одно и то же действие? Дело в том, что на практике встречаются ситуации, когда запрос выражается очень сложно через соединения, и легко – через вложенный подзапрос, и наоборот. На конкретном сервере БД запрос с использованием JOIN может выполняться очень долго, а с использованием подзапроса – быстро.
Пример запроса с вложенным подзапросом:
SELECT *
FROM Orders
WHERE snum =
(SELECT snum
FROM Salespeople
WHERE sname = 'Motika');
Так как подзапрос стоит после знака равенства, он должен возвращать только одно значение. В случае, если подзапрос вернет более чем одно значение, произойдет ошибка.
Обратите внимание, что при записи подзапроса допустима следующая форма:
<имя/константа> <оператор> <подзапрос>, а не <подзапрос> <оператор> <имя/константа> или < подзапрос > < оператор > < подзапрос >.
Во вложенных подзапросах можно использовать агрегатные функции:
SELECT *
FROM Orders
WHERE amt >
(SELECT AVG (amt)
FROM Orders
WHERE odate = 10/04/1990);
Ограничение на вложенный подзапрос то же самое – он должен возвращать единственное значение. В случае, если подзапрос возвращает несколько записей, вместо операций сравнения нужно использовать 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 подзапроса:
SELECT *
FROM Customers
WHERE cnum =
(SELECT snum + 1000
FROM Salespeople
WHERE sname = Serres);
Также допустимы подзапросы в выражении HAVING:
SELECT rating, COUNT (DISTINCT cnum)
FROM Customers
GROUP BY rating
HAVING rating >(SELECT AVG (rating)
FROM Customers
WHERE city = " San Jose');
