sqlÖеÄin¿ÉÒÔÓÃʲôÌæ»»
sql ÖÐÌæ»» in µÄ¼Æ»®ÓУº1. exists ×ÓÅÌÎʼì²éÖµÔÚÆäËû±íÖеı£´æÐÔ£»2. ×ÓÅÌÎÊʹÓýÏÁ¿ÔËËã·û±È¶Ô×ÓÅÌÎÊÖµ£»3. join ʹÓà join Ìõ¼þ½ÏÁ¿Öµ£»4. union ʹÓà union ºÍ distinct ºÏ²¢Ð§¹û£¬È¥ÖغóÀàËÆ in¡£Ñ¡Ôñ¼Æ»®Ê±Ë¼Á¿Êý¾ÝÁ¿¡¢ÖØ´óÐÔ¡¢¿É¶ÁÐÔ¡£
SQL ÖÐÌæ»» IN µÄÌæ»»¼Æ»®
ÔÚ SQL ÖУ¬IN ²Ù×÷·ûÓÃÓÚ¼ì²éÖµÊÇ·ñ°üÀ¨ÔÚÖ¸¶¨ÁбíÖС£ËäÈ» IN ÊÇÒ»ÖÖÀû±ãµÄÒªÁ죬µ«ËüÔÚijЩÇéÐÎÏ¿ÉÄÜ»áÊܵ½ÐÔÄÜÏÞÖÆ¡£ÒÔÏÂÊÇһЩ¿ÉÒÔÓÃÀ´Ìæ»» IN µÄÌæ»»¼Æ»®£º
1. EXISTS
EXISTS ×ÓÅÌÎÊ¿ÉÓÃÓÚ¼ì²éÖµÊÇ·ñ±£´æÓÚÁíÒ»¸ö±í»òÅÌÎÊÖС£Óë IN Ïà±È£¬EXISTS ¸üÓÐÀûÓÚÖ´ÐÐÍýÏëµÄÓÅ»¯¡£
ʾÀý£º
SELECT * FROM customers WHERE EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.customer_id)
µÇ¼ºó¸´ÖÆ
2. ×ÓÅÌÎÊ
×ÓÅÌÎÊ¿ÉÒÔÓë½ÏÁ¿ÔËËã·ûÒ»ÆðʹÓã¬ÀýÈç = »ò !=£¬ÒÔ¼ì²éÖµÊÇ·ñÓë×ÓÅÌÎÊ·µ»ØµÄֵƥÅä¡£×ÓÅÌÎÊÔÊÐíÎÞаµØÖ¸¶¨ÖØ´óɸѡÌõ¼þ¡£
ʾÀý£º
SELECT * FROM customers WHERE customer_id = (SELECT customer_id FROM orders WHERE product_id = 1)
µÇ¼ºó¸´ÖÆ
3. JOIN
JOIN ²Ù×÷·û¿ÉÒÔÓÃÀ´½«Á½ÕűíÅþÁ¬ÆðÀ´¡£Í¨¹ýÔÚ JOIN Ìõ¼þÖÐʹÓýÏÁ¿ÔËËã·û£¬¿ÉÒÔ¼ì²éÖµÊÇ·ñÆ¥Åä¡£
ʾÀý£º
SELECT * FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.product_id = 1
µÇ¼ºó¸´ÖÆ
4. UNION
UNION ²Ù×÷·û¿ÉÒÔ½«¶à¸ö±í»òÅÌÎʵÄЧ¹ûºÏ²¢µ½Ò»¸ö±íÖС£Í¨¹ýʹÓà UNION ºÍ DISTINCT£¬¿ÉÒÔʵÏÖÀàËÆÓÚ IN µÄ¹¦Ð§¡£
ʾÀý£º
SELECT DISTINCT customer_id FROM (SELECT customer_id FROM orders UNION SELECT customer_id FROM customers)
µÇ¼ºó¸´ÖÆ
Ñ¡ÔñÌæ»»¼Æ»®µÄ×¢ÖØÊÂÏî
Ñ¡Ôñ×îºÏÊʵÄÌæ»»¼Æ»®Ê±£¬ÐèҪ˼Á¿ÒÔÏÂÒòËØ£º
Êý¾ÝÁ¿£º Êý¾ÝÁ¿½Ï´óʱ£¬×ÓÅÌÎÊºÍ JOIN ¿ÉÄÜ»á±È EXISTS ЧÂʸü¸ß¡£
ÖØ´óÐÔ£º ×ÓÅÌÎÊºÍ JOIN ÔÊÐíÖ¸¶¨¸üÖØ´óµÄɸѡÌõ¼þ¡£
¿É¶ÁÐÔ£º IN ²Ù×÷·ûͨ³£±ÈÆäËûÌæ»»¼Æ»®¸üÒ×ÓÚÔĶÁºÍÃ÷È·¡£
ÒÔÉϾÍÊÇsqlÖеÄin¿ÉÒÔÓÃʲôÌæ»»µÄÏêϸÄÚÈÝ£¬¸ü¶àÇë¹Ø×¢±¾ÍøÄÚÆäËüÏà¹ØÎÄÕ£¡