发表于 2009-1-5 19:48
| 
oralce sql join on鐨勭敤娉
鍙戝竷: 2008-12-08 18:38 | 浣滆: webmaster | 鏉ユ簮: 鏈珯鍘熷垱 | 鏌ョ湅: 27娆
right outer join鐩稿弽
full outer join 宸﹀彸琛ㄦ湭杩炴帴鐨勫唴瀹瑰叏閮ㄦ樉绀
澶栬仈鎺ャ傚鑱旀帴鍙互鏄乏鍚戝鑱旀帴銆佸彸鍚戝鑱旀帴鎴栧畬鏁村閮ㄨ仈鎺ャ
鍦 FROM 瀛愬彞涓寚瀹氬鑱旀帴鏃讹紝鍙互鐢变笅鍒楀嚑缁勫叧閿瓧涓殑涓缁勬寚瀹氾細
LEFT JOIN 鎴 LEFT OUTER JOIN銆
宸﹀悜澶栬仈鎺ョ殑缁撴灉闆嗗寘鎷 LEFT OUTER 瀛愬彞涓寚瀹氱殑宸﹁〃鐨勬墍鏈夎锛岃屼笉浠呬粎鏄仈鎺ュ垪鎵鍖归厤鐨勮銆傚鏋滃乏琛ㄧ殑鏌愯鍦ㄥ彸琛ㄤ腑娌℃湁鍖归厤琛岋紝鍒欏湪鐩稿叧鑱旂殑缁撴灉闆嗚涓彸琛ㄧ殑鎵鏈夐夋嫨鍒楄〃鍒楀潎涓虹┖鍊笺
RIGHT JOIN 鎴 RIGHT OUTER JOIN銆
鍙冲悜澶栬仈鎺ユ槸宸﹀悜澶栬仈鎺ョ殑鍙嶅悜鑱旀帴銆傚皢杩斿洖鍙宠〃鐨勬墍鏈夎銆傚鏋滃彸琛ㄧ殑鏌愯鍦ㄥ乏琛ㄤ腑娌℃湁鍖归厤琛岋紝鍒欏皢涓哄乏琛ㄨ繑鍥炵┖鍊笺
FULL JOIN 鎴 FULL OUTER JOIN銆
瀹屾暣澶栭儴鑱旀帴杩斿洖宸﹁〃鍜屽彸琛ㄤ腑鐨勬墍鏈夎銆傚綋鏌愯鍦ㄥ彟涓涓〃涓病鏈夊尮閰嶈鏃讹紝鍒欏彟涓涓〃鐨勯夋嫨鍒楄〃鍒楀寘鍚┖鍊笺傚鏋滆〃涔嬮棿鏈夊尮閰嶈锛屽垯鏁翠釜缁撴灉闆嗚鍖呭惈鍩鸿〃鐨勬暟鎹笺
浠呭綋鑷冲皯鏈変竴涓悓灞炰簬涓よ〃鐨勮绗﹀悎鑱旀帴鏉′欢鏃讹紝鍐呰仈鎺ユ墠杩斿洖琛屻傚唴鑱旀帴娑堥櫎涓庡彟涓涓〃涓殑浠讳綍琛屼笉鍖归厤鐨勮銆傝屽鑱旀帴浼氳繑鍥 FROM 瀛愬彞涓彁鍒扮殑鑷冲皯涓涓〃鎴栬鍥剧殑鎵鏈夎锛屽彧瑕佽繖浜涜绗﹀悎浠讳綍 WHERE 鎴 HAVING 鎼滅储鏉′欢銆傚皢妫绱㈤氳繃宸﹀悜澶栬仈鎺ュ紩鐢ㄧ殑宸﹁〃鐨勬墍鏈夎锛屼互鍙婇氳繃鍙冲悜澶栬仈鎺ュ紩鐢ㄧ殑鍙宠〃鐨勬墍鏈夎銆傚畬鏁村閮ㄨ仈鎺ヤ腑涓や釜琛ㄧ殑鎵鏈夎閮藉皢杩斿洖銆
LEFT OUTER JOIN 鎴 LEFT JOIN
RIGHT OUTER JOIN 鎴 RIGHT JOIN
FULL OUTER JOIN 鎴 FULL JOIN
鐢ㄦ硶涓句緥锛
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
ON s.title_id = t.title_id
AND s.qty > 50
ORDER BY s.stor_id ASC
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
