SQL Server: Inner Join¼°Outer Join, Natrual Jion
Ò»¡¢NATURAL JOIN£¨×ÔÈ»Á¬½Ó£©
Á½Õűíͨ¹ýNATURAL JOINÁ¬½ÓµÄʱºò£¬Ï൱ÓÚÓиöÒþº¬µÄWHERE×Ӿ䣬¶ÔÁ½ÕűíÖÐͬÃûµÄ¶ÔÓ¦ÁÐÏà±È½Ï¿´ÊÇ·ñÏàµÈ¡£
¶þ¡¢CROSS JOIN£¨´´½¨µÑ¿¨¶û»ý£©
¶ÔÁ½Õűíͨ¹ý½»²æÁªºÏ²úÉúµÚÈýÕÅ·µ»Ø½á¹û¼¯µÄ±í¡£Ï൱ÓÚÆÕͨµÄÁ¬½Ó¡£
Èý¡¢INNER JOIN£¨ÄÚÁ¬½Ó£©
ÄÚÁ¬½Ó¾ÍÏ൱ÓÚÆÕͨµÄCROSS JOIN£¬Ö»ÊǸñʽ²»Ò»Ñù£¬INNER JOINÔÚºóÃæÓÐÒ»¸öON×Ӿ䣨Ï൱ÓÚWHERE£©µÄËÑË÷Ìõ¼þ£¬ÓÃÓÚ¹ýÂË·µ»ØµÄÐС£
ËÄ¡¢OUTER JOIN £¨ÍâÁ¬½Ó£©
select * from ta outer join tb on (ta.c1=tb.c1)
outer join¸æËßDBMSÉú³É½á¹û±í£¬Ôڴ˱íÖв»½ö´øÓÐÏà¹Ø(ta.c1=tb.c1)Ðжԣ¬¶øÇÒ»¹ÓÐÀ´×ÔÁ½¸öÔ´±íÖÐÈÎÒ»±íµÄ²»Æ¥ÅäµÄÐС£
Îå¡¢LEFT OUTER JOIN£¨×óÁ¬½Ó£© RIGHT OUTER JOIN£¨ÓÒÁ¬½Ó£©
select * from ta left outer join tb on (ta.c1=tb.c1)
select * from ta right outer join tb on (ta.c1=tb.c1)
left outer join(left join) ¸æËßDBMSÉú³É°üÀ¨ÁªºÏÐкÍÈκβ»Æ¥ÅäµÄÐеĽá¹û±í£¬µ«ÊDz»Æ¥ÅäµÄÐÐϵÀ´×Ô²éѯµÄfrom×Ó¾äÖÐLEFT OUTER JOIN¹Ø¼ü´Ê×ó±ßµÄ±íÖС£
right outer join(right join)Óëleft outer join(left join)¸ÕºÃÏà·´¡£
Áù¡¢FULL OUTER JOIN£¨È«Á¬½Ó£©
FULL OUTER JOIN ·µ»Ø×éºÏÁËLEFT OUTER JOIN ºÍRIGHT OUTER JOIN½á¹ûµÄ±í¡£
ÒÔÏÂÊÇInner Join¼°Outer JoinµÄ¾ßÌå½éÉÜ£º
ÔÚÒ»¸öÕý¹æ»¯µÄÊý¾Ý¿â»·¾³ÖÐ, ÎÒÃdz£»áÅöµ½Õâ¿îÇéÐÎ: ËùÐèµÄ×ÊÁϲ¢²»ÊÇ·ÅÔÚͬһ¸ö×ÊÁϱíÖÐ, ÔÚÕâ¸öʱºò, Äã¾ÍÒªÓõ½ Join¡£
¡¡¡¡µ±È» Join ÈçºÎ½«²»Í¬µÄÊý¾Ý¿âµÄ×ÊÁϽáºÏ, »¹Òª¿´ÄãÈçºÎʹÓÃËü, Ò»¹²ÓÐËÄÖÖ²»Í¬µÄ Join µÄ·½Ê½, ÔÚÕâƪÎÄÕÂÖÐÎÒÃǽ«ÎªÄã½éÉÜ Inner Join ¼° Outer Join ÒÔ¼°ÆäÓ¦Óá£
¡¡¡¡Inner Join
¡¡¡¡Inner Join Ó¦¸ÃÊÇ×î³£ÓÃµÄ Join ·½Ê½, ËüÖ»»á´«»Ø·ûºÏ Join ¹æÔòµÄ¼Í¼, »¹ÊÇÏÈÀ´¿´¿´Óï·¨£º
Select <ҪѡÔñµÄ×Ö¶Î> from <Ö÷Òª×ÊÁϱí> <Join ·½Ê½> <´ÎÒª×ÊÁϱí> [On <Join ¹æÔò>]
¡¡¡¡ÏÖÔÚÎÒÃÇÀûÓà MS SQL ÄÚ½¨µÄ±±·çÊý¾Ý¿âÀ´Êµ¼ÊÁ·Ï°Ò»ÏÂ! Ïë¶àÁ˽â MS SQL µÄÄÚ½¨Êý¾Ý¿â, Äã¿ÉÒÔ¿´¿´ SQL Server µÄÄÚ½¨Êý¾Ý¿âÕâƪÎÄÕ¡£
¡¡¡¡Çë´ò¿ª
Ïà¹ØÎĵµ£º
ÒòΪ²»Ö§³Ö´æ´¢¹ý³Ì£¬ËùÒÔ²éѯÓï¾äÈçÏ£º
string SLQuery =
"INSERT INTO ParkLocation(ParkName,Latitude, Longtitude,Charge,TotalSpace,EmptySpace,ParkCaption,UpdataTime)"+
&nb ......
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Á¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£ unionÖ»Êǽ«Á½¸ö½á¹ ......
ÎåÖÖÌá¸ß SQL ÐÔÄܵķ½·¨
ÓÐʱ£¬ ΪÁËÈÃÓ¦ÓóÌÐòÔËÐеøü¿ì£¬Ëù×öµÄÈ«²¿¹¤×÷¾ÍÊÇÔÚÕâÀï»òÄÇÀï×öһЩºÜСµ÷Õû¡£°¡£¬µ«¹Ø¼üÔÚÓÚÈ·¶¨ÈçºÎ½øÐе÷Õû£¡³ÙÔçÄú»áÓöµ½ÕâÖÖÇé¿ö£ºÓ¦ÓóÌÐòÖÐµÄ SQL ²éѯ²»ÄÜ°´ÕÕÄúÏëÒªµÄ·½Ê½½øÐÐÏìÓ¦¡£ËüҪô²»·µ»ØÊý¾Ý£¬ÒªÃ´ºÄ·ÑµÄʱ¼ä³¤µÃ³öÆæ¡£Èç¹ûËü½µµÍÁ˱¨¸æ»òÄúµÄÆóÒµÓ¦ÓóÌÐòµÄËٶȣ¬Óû§±ØÐ ......