, ANY . , , , , , ( 13.5):
SELECT *
FROM Customers
WHERE rating > ANY (SELECT rating
FROM Customers
WHERE city = 'Rome');
, " ( city = 'Rome')", , , , city = 'Rome'. , ANY, SQL. ANY , , .
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE rating > ANY |
| (SELECT rating |
| FROM Customers |
| WHERE city = 'Rome'); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ------ |
| 2002 Giovanni 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.
=============== 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- .
(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 ==============
| 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');
, . San Jose . , :
SELECT *
FROM Customers
WHERE rating = (SELECT DISTINCT rating
FROM Customers
WHERE city = 'San Jose');
, , , ALL . , , , . , , .
, ALL , " <> ". , SQL , , , a .
, , , . 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 |
| (SELECT rating |
| FROM Customers |
| WHERE city = 'San Jose'); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ------ |
| 2001 Hoffman London 100 1001 |
| 2006 Clemens London 100 1001 |
| 2007 Pereira Rome 100 1004 |
===============================================
13.11. ALL <>.
San Jose. : 200 ( Liu) 300 ( Cisneros). , , , , 100. , NOT IN:
SELECT*
FROM Customers
WHERE rating NOT IN (SELECT rating
FROM Customers
WHERE city = 'San Jose');
|
|
|
ANY:
SELECT *
FROM Customers
WHERE NOT rating = ANY (SELECT rating
FROM Customers
WHERE city = 'San Jose');
.
