EXISTS , , , . EXISTS , , . , , , EXISTS . , , ( 12.2):
SELECT DISTINCT snum
FROM Customers outer
WHERE EXISTS (SELECT *
FROM Customers inner
WHERE inner.snum = outer.snum AND
inner.cnum <> outer.cnum);
============= SQL Execution Log ==============
| SELECT DISTINCT cnum |
| FROM Customers outer |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE inner.snum = outer.snum |
| AND inner.cnum <> outer.cnum); |
|===============================================|
| cnum |
| ----- |
| 1001 |
| 1002 |
===============================================
12.2. EXISTS .
- ( , ), , snum ( ), cnum ( ). , , , (.. - ). EXISTS , (snum) , , . DISTINCT , , .
EXISTS
, . , ( 12.3):
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;
============= SQL Execution Log ==============
| 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; |
|===============================================|
| cnum cname city |
| ----- -------- ---- |
| 1001 Peel London |
| 1002 Serres San Jose |
===============================================
12.3. EXISTS .
|
|
, , , . , , . (AND first.snum = second.snum) , EXISTS. , snum, . - AND, . , , , . , .
NOT EXISTS
, EXISTS . , , EXISTS NOT. , , , . ( 12.4.)
SELECT DISTINCT snum
FROM Customers outer
WHERE NOT EXISTS (SELECT *
FROM Customers inner
WHERE inner.snum = outer.snum AND
inner.cnum <> outer.cnum);
============== SQL Execution Log =============
| SELECT DISTINCT snum |
| FROM Salespeople outer |
| WHERE NOT EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE inner.snum = outer.snum |
| AND inner.cnum <> outer.cnum); |
|===============================================|
| cnum |
| ----- |
| 1003 |
| 1004 |
| 1007 |
===============================================
12.4. NOT EXISTS.
EXISTS
, EXISTS . . , EXISTS , , ; , EXISTS .
EXISTS , , , .
, EXISTS . ( ). , , , . .
, , , . , EXISTS (SELECT COUNT (DISTINCT sname) FROM Salespeople) EXISTS (SELECT sname FROM Salespeople) .