oracleÖÐnot inÓ÷¨
not in ÔËËã·ûÓÃÓÚ¼ì²éÒ»¸öÖµÊÇ·ñ²»ÔÚÁíÒ»¸ö×é»ò×ÓÅÌÎʵÄЧ¹û¼¯ÖС£ËüÒÔÒÔÏ·½·¨Ê¹ÓãºÖ¸¶¨Òª¼ì²éµÄÖµ¡£½«¸ÃÖµÓë×ÓÅÌÎÊ»òÖµÁбí¾ÙÐнÏÁ¿¡£·µ»Ø¸ÃÖµ²»ÔÚ½ÏÁ¿×éÖеÄËùÓÐÐС£
Oracle ÖÐ NOT IN Ó÷¨
NOT IN ÔËËã·ûÔÚ Oracle ÖÐÓÃÓÚ¼ì²éÒ»¸öÖµÊÇ·ñ²»ÔÚÁíÒ»¸ö×é»ò×ÓÅÌÎÊ·µ»ØµÄЧ¹û¼¯ÖС£
Óï·¨£º
SELECT column_name FROM table_name WHERE column_name NOT IN (subquery | value_list);
µÇ¼ºó¸´ÖÆ
ÆäÖУº
column_name ÊÇÒª¼ì²éµÄÖµ¡£
subquery ÊÇ·µ»ØÒ»¸öÖµµÄǶÌ×ÅÌÎÊ¡£
value_list ÊÇÒ»¸ö°üÀ¨¶à¸öÖµµÄ¶ººÅÍÑÀëÁÐ±í¡£
ʾÀý£º
¼ì²éÒ»¸öÖµÊÇ·ñ²»ÔÚÒ»¸ö×ÓÅÌÎÊ·µ»ØµÄЧ¹û¼¯ÖУº
SELECT customer_name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);
µÇ¼ºó¸´ÖÆ
Õ⽫´Ó customers ±íÖÐÑ¡ÔñËùÓв»ÔÚ orders ±íÖÐ customer_id ÁÐÖÐÖµ£¨¼´Ã»Óж©¹ºµÄ¿Í»§£©µÄ¿Í»§ÐÕÃû¡£
¼ì²éÒ»¸öÖµÊÇ·ñ²»ÔÚÒ»¸öÖµÁбíÖУº
SELECT product_id FROM products WHERE product_id NOT IN (101, 102, 103);
µÇ¼ºó¸´ÖÆ
Õ⽫´Ó products ±íÖÐÑ¡ÔñËùÓв»ÔÚÖµÁбí (101, 102, 103) ÖеIJúÆ· ID¡£
×¢ÖØ£º
NOT IN ÔËËã·ûÇø·Ö¾Þϸд¡£
ÈôÊÇ subquery »ò value_list Ϊ¿Õ£¬NOT IN ½«·µ»ØËùÓÐÐС£
Oracle »¹ÌṩÁËÒ»¸ö NOT IN ANY ÔËËã·û£¬ÓÃÓÚ¼ì²éÒ»¸öÖµÊÇ·ñ²»ÔÚ¶à¸ö×ÓÅÌÎÊ·µ»ØµÄЧ¹û¼¯µÄÍŽáÖС£
ÒÔÉϾÍÊÇoracleÖÐnot inÓ÷¨µÄÏêϸÄÚÈÝ£¬¸ü¶àÇë¹Ø×¢±¾ÍøÄÚÆäËüÏà¹ØÎÄÕ£¡