æ¹æ³/æ¥éª¤
1
é¦å
ï¼å¨ä¸ä¸ªæ¥è¯¢ä¸çæ¥è¯¢ç»æä½ä¸ºå¤å±æ¥è¯¢çæ¡ä»¶ï¼å¯ä»¥ç¨INå
³é®åï¼ä»£ç å¦ä¸ï¼
SELECT * FROM city WHERE CountryCode IN (SELECT Code FROM country);
å¦ä¸å¾æ示ï¼
2
å
¶æ¬¡ï¼å¤å±æ¥è¯¢çæ¡ä»¶ä¸å¨å
å±æ¥è¯¢ç»æï¼å¯ä»¥ç¨NOT INå
³é®åï¼ä»£ç å¦ä¸ï¼
SELECT * FROM city WHERE CountryCode NOT IN (SELECT Code FROM country);
å¦ä¸å¾æ示ï¼
3
使ç¨EXISTSå
³é®åæ¥è¯¢æ¶ï¼å
å±æ¥è¯¢è¯å¥ä¸è¿åæ¥è¯¢çè®°å½ï¼èæ¯è¿åä¸ä¸ªBooleanå¼ï¼å½å
å±æ¥è¯¢è¿åçå¼ä¸ºtrueæ¶ï¼å¤å±æ¥è¯¢è¯å¥å°è¿è¡æ¥è¯¢ï¼å¦æè¿åfalseæ¶ï¼å°ä¸è¿è¡æ¥è¯¢ææ¥è¯¢ç»æ为空ï¼ä»£ç å¦ä¸ï¼
SELECT * FROM city WHERE EXISTS (SELECT Name FROM country);
å¦ä¸å¾æ示ï¼
4
ç±ç¬¬ä¸æ¥å¯ç¥ï¼ä¸EXISTSç¸å¯¹çæ¯NOT EXISTSï¼å½å
å±æ¥è¯¢è¿åçå¼ä¸ºfalseæ¶ï¼å¤å±æ¥è¯¢è¯å¥å°è¿è¡æ¥è¯¢ï¼å¦æè¿åtrueæ¶ï¼å°ä¸è¿è¡æ¥è¯¢ææ¥è¯¢ç»æ为空ï¼ä»£ç å¦ä¸ï¼
SELECT * FROM city WHERE NOT EXISTS (SELECT Name FROM country);
å¦ä¸å¾æ示ï¼
5
满足å
¶ä¸çä»»ä¸æ¡ä»¶ï¼å°±å¯ä»¥éè¿è¯¥æ¡ä»¶æ¥æ§è¡å¤å±æ¥è¯¢è¯å¥ï¼ç¨å
³é®åANYï¼ä»£ç å¦ä¸ï¼
SELECT * FROM city WHERE Population >= ANY (SELECT Population FROM country);
å¦ä¸å¾æ示ï¼
6
åæ¥è¯¢è¿å
å«æ¯è¾è¿ç®ç¬¦ï¼æâ>="ãâ<="ã"!="çå
³é®åï¼ä»£ç å¦ä¸ï¼
SELECT * FROM city WHERE Population >= (SELECT Population FROM country WHERE Name = 'Afghanistan');
SELECT * FROM city WHERE Population <= (SELECT Population FROM country WHERE Name = 'Afghanistan');
SELECT * FROM city WHERE Population != (SELECT Population FROM country WHERE Name = 'Afghanistan');
å¦ä¸å¾æ示ï¼
7
满足æææ¡ä»¶ï¼åªæ满足å
å±æ¥è¯¢è¯å¥è¿åææç»æï¼ä»£ç å¦ä¸ï¼
SELECT * FROM city WHERE Population >= ALL (SELECT Population FROM country WHERE Name = 'Afghanistan');
å¦ä¸å¾æ示ï¼
http://jingyan.baidu.com/article/73c3ce280d68f2e50243d960.html