Мартин Грубер - Понимание SQL
Rome
200
1003
2003
Liu
San Jose
200
1002
2004
Grass
Berlin
300
1002
2008
Cisneros
San Jose
300
1007
Таблица 13.5 Как оператор "больше чем" (>) интерпретируется ANY
Если мы оценим ANY способом использующим грамматику Английского Языка, то только заказчики с оценкой 300 будут превышать Giovanni, который находится в Риме и имеет оценку 200. Однако, подзапрос ANY также находит Periera в Риме с оценкой 100. Так как все заказчики с оценкой 200 были выше этой, они будут выбраны, даже если имелся другой заказчик из Рима(Giovanni) чья оценка не была выше (фактически, то что один из выбранных заказчиков также находится в Риме несущественно).
Так как подзапрос произвел по крайней мере одно значение которое сделает предикат верным в отношении этих строк, строки были выбраны. Чтобы дать другой пример, предположим что мы должны были выбирать все порядки сумм приоретений которые были больше чем по крайней мере один из порядков на 6-е Октября:
SELECT *
FROM Orders
WHERE amt > ANY
( SELECT amt
FROM Orders
WHERE odate=10/06/1990 );
Вывод для этого запроса показывается в Таблице 13.6.
Даже если самая высокая сумма приобретений в таблице (9891.88) - имелась на 6-е Октября, предыдущая строка имеет более высокое значение суммы чем другая строка на 6-е Октября, которая имела значение суммы=1309.95. Имея реляционный оператор ">=" вместо просто " > ", эта строка будет также выбирана, потому что она равна самой себе.
Конечно, вы можете использовать ANY с другой SQL техникой, например с техникой обьединения. Этот запрос будет находить все порядки со значением суммы меньшей чем значение любой суммы для заказчика в San Jose (вывод показывается в Таблице 13.7):
SELECT *
FROM Orders
WHERE amt < ANY
( SELECT amt
FROM Orders A, Customers b
WHERE a.cnum=b.cnum
AND b.city=" San Jose' );
Даже если нименьший порядок в таблице был для заказчика из San Jose, то был второй наибольший; следовательно почти все строки будут выбраны. Простой способ запомнить, что < ANY значение меньшее чем наибольшее выбранное значение, а > ANY значение большее чем наименьшее выбранное значение.
SQL Execution Log
SELECT * FROM Orders WHERE amt > ANY
(SELECT amt FROM Orders WHERE odate=10/06/1990);
onum
amt
odate
cnum
snum
3002
1900.10
10/03/1990
2007
1004
3005
5160.45
10/03/1990
2003
1002
3009
1713.23
10/04/1990
2002
1003
3008
4723.00
10/05/1990
2006
1001
3011
9891.88
10/06/1990
2006
1001
Таблица 13. 6: Выбранное значение больше чем любое(ANY) на 6-е Октября
SQL Execution Log
WHERE amt > ANY (SELECT amt FROM Orders a, Customers b
WHERE a.cnum=b.cnum AND b.city='San Jose');
onum
amt
odate
cnum
snum
3001
18.69
10/03/1990
2008
1007
3003
767.10
10/03/1990
2001
1001
3002
1900.10
10/03/1990
2007
1004
3006
1098.10
10/03/1990
2008
1007
3009
1713.23
10/04/1990
2002
1003
3007
75.10
10/04/1990
2004
1002
3008
4723.00
10/05/1990
2006
1001
3010
1309.88
10/06/1990
2004
1002
Таблица 13. 7: Использование ANY с объединением
Фактически, вышеуказанные команды весьма похожи на следующее - (вывод показан в Таблице 13.8) :
SELECT *
FROM Orders
WHERE amt <
( SELECT MAX amt
FROM Orders A, Customers b
WHERE a.cnum=b.cnum
AND b.city=" San Jose' );
SQL Execution Log
WHERE amt < (SELECT MAX (amt) FROM Orders a, Customers b
WHERE a.cnum=b.cnum AND b.city='San Jose');
onum
amt
odate
cnum
snum
3002
1900.10
10/03/1990
2007
1004
3005
5160.45
10/03/1990
2003
1002
3009
1713.23
10/04/1990
2002
1003
3008
4723.00
10/05/1990
2006
1001
3011
9891.88
10/06/1990
2006
1001
Таблица 13.8: Использование агрегатной функции вместо ANY
СПЕЦИАЛЬНЫЙ ОПЕРАТОР ALLС помощью ALL, предикат является верным, если каждое значение выбранное подзапросом удовлетворяет условию в предикате внешнего запроса.
Если мы хотим пересмотреть наш предыдущий пример чтобы вывести только тех заказчиков чьи оценки, фактически, выше чем у каждого заказчика в Париже, мы можем ввести следующее чтобы произвести вывод показанный в Таблтце 13.9:
SELECT *
FROM Customers
WHERE rating > ALL
(SELECT rating
FROM Customers
WHERE city=Rome ):
SQL Execution Log
SELECT * FROM Customers WHERE rating > ALL
(SELECT rating FROM Customers WHERE city='Rome');
cnum
cname
city
rating
snum
2004
Grass
Berlin
300
1002
2008
Cisneros
San Jose
300
1007
Таблица 13.9: Использование оператора ALL
Этот оператор проверяет значения оценки всех заказчиков в Риме. Затем он находит заказчиков с оценкой большей чем у любого из заказчиков в Риме. Самая высокая оценка в Риме - у Giovanni( 200). Следовательно, выбираются только значения выше этих 200.
Как и в случае с ANY, мы можем использовать EXISTS для производства альтернативной формулировки такого же запроса - (вывод показан в Таблице 13.10 ):
SELECT *
FROM Customers outer
WHERE NOT EXISTS
( SELECT *
FROM Customers inner
WHERE outer.rating <=inner.rating
AND inner.city=|Rome| );
SQL Execution Log
SELECT * FROM Customers outer WHERE NOT EXISTS
(SELECT * FROM Customers inner WHERE outer
rating=inner.rating AND inner.city='Rome');
cnum
cname
city
rating
snum
2004
Grass
Berlin
300
1002
2008
Cisneros
San Jose
300
1007
Таблица 13.10: Использование EXISTS в качестве альтернативы к ALL
РАВЕНСТВА И НЕРАВЕНСТВА
ALL используется в основном с неравенствами чем с равенствами, так как значение может быть "равным для всех" результатом подзапроса только если все результаты, фактически, идентичны. Посмотрите следующий запрос:
SELECT *
FROM Customers
WHERE rating=ALL
( SELECT rating
FROM Customers
WHERE city=" San Jose' );
Эта команда допустима, но, c этими данными, мы не получим никакого вывода. Только в единственом случае вывод будет выдан этим запросом - если все значения оценки в San Jose окажутся идентичными. В этом случае, можно сказать следующее :
SELECT *
FROM Customers
WHERE rating=
( SELECT DISTINCT rating
FROM Customers
WHERE city=" San Jose' );
Основное различие в том, что эта последняя команда должна потерпеть неудачу если подзапрос выведет много значений, в то время как вариант с ALL просто не даст никакого вывода. В общем, не самая удачная идея использовать запросы которые работают только в определенных ситуациях подобно этой. Так как ваша база данных будет постоянно меняться, это еудачный способ, чтобы узнать о ее содержании. Однако, ALL может более эффективно использоваться с неравенствами, то есть с оператором "< >". Но учтите что сказанное в SQL что - значение которое не равняется всем результатам подзапроса, - будет отличаться от того же но сказанного с учетом граматики Английского языка. Очевидно, если подзапрос возвращает много различных значений, как это обычно бывает, ни одно
отдельное значение не может быть равно им всем в обычном смысле. В SQL, выражение - < > ALL - в действительности соответствует " не равен любому " результату подзапроса. Другими словами, предикат верен, если данное значение не найдено среди результатов подзапроса. Следовательно, наш предыдущий пример противоположен по смыслу этому примеру (с выводом показанным в Таблице 13.11):
SELECT *
FROM Customers
WHERE rating < > ALL
( SELECT rating
FROM Customers
WHERE city=" San Jose' );
SQL Execution Log
SELECT * FROM Customers WHERE rating < > ALL
