sqlÖÐ in ¡¢not in ¡¢exists¡¢not exists Ó÷¨ºÍ²î±ð
exists £¨sql ·µ»Ø½á¹û¼¯ÎªÕ棩
not exists (sql ²»·µ»Ø½á¹û¼¯ÎªÕ棩
ÈçÏ£º
±íA
ID NAME
1 A1
2 A2
3 A3
±íB
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
±íAºÍ±íBÊÇ£±¶Ô¶àµÄ¹ØÏµ A.ID => B.AID
SELECT ID,NAME from A WHERE EXIST (SELECT * from B WHERE A.ID=B.AID)
Ö´Ðнá¹ûΪ
1 A1
2 A2
ÔÒò¿ÉÒÔ°´ÕÕÈçÏ·ÖÎö
SELECT ID,NAME from A WHERE EXISTS (SELECT * from B WHERE B.AID=£±)
--->SELECT * from B WHERE B.AID=£±ÓÐÖµ·µ»ØÕæËùÒÔÓÐÊý¾Ý
SELECT ID,NAME from A WHERE EXISTS (SELECT * from B WHERE B.AID=2)
--->SELECT * from B WHERE B.AID=£²ÓÐÖµ·µ»ØÕæËùÒÔÓÐÊý¾Ý
SELECT ID,NAME from A WHERE EXISTS (SELECT * from B WHERE B.AID=3)
--->SELECT * from B WHERE B.AID=£³ÎÞÖµ·µ»ØÕæËùÒÔûÓÐÊý¾Ý
NOT EXISTS ¾ÍÊÇ·´¹ýÀ´
SELECT ID,NAME from A WHERE¡¡NOT EXIST (SELECT * from B WHERE A.ID=B.AID)
Ö´Ðнá¹ûΪ
3 A3
===========================================================================
EXISTS = IN,Òâ˼Ïàͬ²»¹ýÓï·¨ÉÏÓеãµãÇø±ð£¬ºÃÏñʹÓÃINЧÂÊÒª²îµã£¬Ó¦¸ÃÊDz»»áÖ´ÐÐË÷ÒýµÄÔÒò
SELECT ID,NAME from A¡¡ WHERE¡¡ID IN (SELECT AID from B)
NOT EXISTS = NOT IN ,Òâ˼Ïàͬ²»¹ýÓï·¨ÉÏÓеãµãÇø±ð
SELECT ID,NAME from A WHERE¡¡ID¡¡NOT IN (SELECT AID from B)
ÏÂÃæÊÇÆÕͨµÄÓ÷¨£º
SQLÖÐIN,NOT IN,EXISTS,NOT EXISTSµÄÓ÷¨ºÍ²î±ð:&nbs
Ïà¹ØÎĵµ£º
±¾ÎĹؼü´Ê£ºSQL 2008 SQL SQL Server SQL Server 2008
¶ÔÓÚ¼´½«ÔÚ2008ÄêÂ¡ÖØ·¢²¼µÄSQL Server 2008£¬Î¢ÈíÆÚÍûÊг¡²»½ö½öÊǰÑËü×÷Ϊ¹ØÏµÊý¾Ý¿âϵͳÀ´¿´´ý£¬Î¢Èí½«Æä¶¨Î»ÎªÒ»¸öÆóÒµÊý¾Ýƽ̨£¬ËäÈ»¹ØÏµÊý¾Ý¿âÒýÇæÈÔÈ»ÊÇSQL Server 2008µÄºËÐÄ£¬²»¹ýSQL Server 2008ËùÄÜÌṩµÄ·þÎñ¹ã¶È½«Ô¶³¬¹ý¼òµ¥µÄ¹ØÏµÊý¾ ......
SQL Server2008ÐÐÊý¾ÝºÍÒ³Êý¾ÝѹËõ½âÃÜ
Êý¾ÝѹËõÒâζ׿õСÊý¾ÝµÄÓдÅÅÌÕ¼ÓÃÁ¿£¬ËùÒÔÊý¾ÝѹËõ¿ÉÒÔÓÃÔÚ±í£¬¾Û¼¯Ë÷Òý£¬·Ç¾Û¼¯Ë÷Òý£¬ÊÓͼË÷Òý»òÊÇ·ÖÇø±í£¬·ÖÇøË÷ÒýÉÏ¡£2.ǰ±êѹËõ£ºÃ¿Ò»Ò³ÖеÄËùÓÐÁУ¬ÔÚÐбêÍ·ÏÂÃæ£¬Ã¿Ðж¼´æ´¢×ÅÒ»¸öÐж¨ÒåÖµ£¬Ñ¹Ëõºó£¬ËùÓÐÐе͍ÒåÖµ¶¼±»Ìæ»»³ÉÐÐÍ·ÖµµÄÒýÓá£
¡¡¡¡±¾ÎĽ«Îª´ó¼Ò½éÉÜ ......
¹«¸æ£º±¾²©¿ÍΪ΢ÈíÔÆ¼ÆËãÖÐÎIJ©¿ÍµÄ¾µÏñ²©¿Í£¬²¢²»±£Ö¤Á´½Ó¿ÉÓÃÐÔ£¬ÎªÁ˱£Ö¤ÔĶÁÌåÑ飬Çë·ÃÎÊhttp://blogs.msdn.com/azchina¡£
SQL AzureÊÇÒ»¸ö²¿ÊðÔÚÔÆ¶ËµÄ¹ØÏµÐÍÊý¾Ý¿âÒýÇæ£¬ËüÖ§³ÖSQL ServerÖоø´ó¶àÊýºÍ¿ª·¢ÓйصŦÄÜ¡£×÷Ϊһ¸ö²¿ÊðÔÚÔÆ¶ËµÄÊý¾Ý¿âÒýÇæ£¬¾ø´ó¶àÊýµÄ¹ÜÀí¹¤×÷¶¼ÓÉ΢ÈíΪÄãÍê³É£¬Òò´Ë ......
select *from customers
select *from orders
select customers.cust_id, orders.order_num from customers inner join orders on customers.cust_id=orders.cust_id
select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id=orde ......