sql涔媗eft join銆乺ight join銆乮nner join鐨勫尯鍒?
left join(宸﹁仈鎺? 杩斿洖鍖呮嫭宸﹁〃涓?殑鎵?鏈夎?褰曞拰鍙宠〃涓?仈缁撳瓧娈电浉绛夌殑璁板綍
right join(鍙宠仈鎺? 杩斿洖鍖呮嫭鍙宠〃涓?殑鎵?鏈夎?褰曞拰宸﹁〃涓?仈缁撳瓧娈电浉绛夌殑璁板綍
inner join(绛夊?艰繛鎺? 鍙?繑鍥炰袱涓?〃涓?仈缁撳瓧娈电浉绛夌殑琛?
涓句緥濡備笅锛?
--------------------------------------------
琛ˋ璁板綍濡備笅锛?
aID銆?銆?銆?銆?銆?aNum
1銆?銆?銆?銆?銆?a20050111
2銆?銆?銆?銆?銆?a20050112
3銆?銆?銆?銆?銆?a20050113
4銆?銆?銆?銆?銆?a20050114
5銆?銆?銆?銆?銆?a20050115
琛˙璁板綍濡備笅:
bID銆?銆?銆?銆?銆?bName
1銆?銆?銆?銆?銆?2006032401
2銆?銆?銆?銆?銆?2006032402
3銆?銆?銆?銆?銆?2006032403
4銆?銆?銆?銆?銆?2006032404
8銆?銆?銆?銆?銆?2006032408
--------------------------------------------
1.left join
sql璇?彞濡備笅:
select * from A
left join B
on A.aID = B.bID
缁撴灉濡備笅:
aID銆?銆?銆?銆?銆?aNum銆?銆?銆?銆?銆?bID銆?銆?銆?銆?銆?bName
1銆?銆?銆?銆?銆?a20050111銆?銆?銆?銆?1銆?銆?銆?銆?銆?2006032401
2銆?銆?銆?銆?銆?a20050112銆?銆?銆?銆?2銆?銆?銆?銆?銆?2006032402
3銆?銆?銆?銆?銆?a20050113銆?銆?銆?銆?3銆?銆?銆?銆?銆?2006032403
4銆?銆?銆?銆?銆?a20050114銆?銆?銆?銆?4銆?銆?銆?銆?銆?2006032404
5銆?銆?銆?銆?銆?a20050115銆?銆?銆?銆?NULL銆?銆?銆?銆?銆?NULL
锛堟墍褰卞搷鐨勮?鏁颁负 5 琛岋級
缁撴灉璇存槑:
left join鏄?互A琛ㄧ殑璁板綍涓哄熀纭?鐨?A鍙?互鐪嬫垚宸﹁〃,B鍙?互鐪嬫垚鍙宠〃,left join鏄?互宸﹁〃涓哄噯鐨?
鎹㈠彞璇濊?,宸﹁〃(A)鐨勮?褰曞皢浼氬叏閮ㄨ〃绀哄嚭鏉?鑰屽彸琛?B)鍙?細鏄剧ず绗﹀悎鎼滅储鏉′欢鐨勮?褰?渚嬪瓙涓?负: A.aID = B.bID).
B琛ㄨ?褰曚笉瓒崇殑鍦版柟鍧囦负NULL.
--------------------------------------------
2.right join
sql璇?彞濡備笅:
select * from A
right join B
on A.aID = B.bID
缁撴灉濡備笅:
aID銆?銆?銆?銆?銆?aNum銆?銆?銆?銆?銆?bID銆?銆?銆?銆?銆?bName
1銆?銆?銆?銆?銆?a20050111銆?銆?銆?銆?1銆?銆?銆?銆?銆?2006032401
2銆?銆?銆?銆?銆?a20050112銆?銆?銆?銆?2銆?銆?銆?銆?銆?2006032402
3銆?銆?銆?銆?銆?a20050113銆?銆?銆?銆?3銆?銆?銆?銆?銆?2006032403
4銆?銆?銆?銆?銆?a20050114銆?銆?銆?銆?4銆?銆?銆?銆?銆?2006032404
NULL銆?銆?銆?銆?銆?NULL銆?銆?銆?銆?銆?8銆?銆?銆?銆?銆?2006032408
锛堟墍褰卞搷鐨勮?鏁颁负 5 琛岋級
缁撴灉璇存槑:
浠旂粏瑙傚療涓?涓?灏变細鍙戠幇,鍜宭eft join鐨勭粨鏋滃垰濂界浉鍙?杩欐?鏄?互鍙宠〃(B)涓哄熀纭?鐨?A琛ㄤ笉瓒崇殑鍦版柟鐢∟ULL濉?厖.
--------------------------------------------
3.inner join
sql璇?彞濡備笅:
select * from A
innerjoin B
on A.aID = B.bID
缁撴灉濡備笅:
aID銆?銆?銆?銆?銆?aNum銆?銆?銆?銆?銆?bID銆?銆?銆?銆?銆?bName
1銆?銆?銆?銆?銆?a20050111銆?銆?銆?銆?1銆?銆?銆?銆?銆?2006032401
2銆?銆?銆?銆?銆?a20050112銆?銆?銆?銆?2銆?銆?銆?銆?銆?2006032402
3銆?銆?銆?銆?銆?a20050113銆?銆?銆?銆?3銆?銆?銆?銆?銆?2006032403
4銆?銆?銆?銆?銆?a20050114銆?銆?銆?銆?4銆?銆?銆?銆?銆?2006032404
缁撴灉璇存槑:
寰堟槑鏄?杩欓噷鍙?樉绀哄嚭浜?A.aID = B.bID鐨勮?褰?杩欒?鏄巌nner join骞朵笉浠ヨ皝涓哄熀纭?,瀹冨彧鏄剧ず绗﹀悎鏉′欢鐨勮?褰?
--------------------------------------------
娉?
LEFT JOIN鎿嶄綔鐢ㄤ簬鍦ㄤ换浣曠殑 FROM 瀛愬彞涓?紝缁勫悎鏉ユ簮琛ㄧ殑璁板綍銆備娇鐢?LEFT JOIN 杩愮畻鏉ュ垱寤轰竴涓?乏杈瑰?閮ㄨ仈鎺ャ?傚乏杈瑰?閮ㄨ仈鎺ュ皢鍖呭惈浜嗕粠绗?竴涓?紙宸﹁竟锛夊紑濮嬬殑涓や釜琛ㄤ腑鐨勫叏閮ㄨ?褰曪紝鍗充娇鍦ㄧ?浜屼釜锛堝彸杈癸級琛ㄤ腑骞舵病鏈夌浉绗﹀?肩殑璁板綍銆?
璇?硶锛欶ROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2
璇存槑锛歵able1, table2鍙傛暟鐢ㄤ簬鎸囧畾瑕佸皢璁板綍缁勫悎鐨勮〃鐨勫悕绉般??
field1, field2鍙傛暟鎸囧畾琚?仈鎺ョ殑瀛楁?鐨勫悕绉般?備笖杩欎簺瀛楁?蹇呴』鏈夌浉鍚岀殑鏁版嵁绫诲瀷鍙婂寘鍚?浉鍚岀被鍨嬬殑鏁版嵁锛屼絾瀹冧滑涓嶉渶瑕佹湁鐩稿悓鐨勫悕绉般??
compopr鍙傛暟鎸囧畾鍏崇郴姣旇緝杩愮畻绗︼細"="锛?"<"锛?">"锛?"<="锛?">=" 鎴?"<>"銆?
濡傛灉鍦↖NNER JOIN鎿嶄綔涓??鑱旀帴鍖呭惈Memo 鏁版嵁绫诲瀷鎴?OLE Object 鏁版嵁绫诲瀷鏁版嵁鐨勫瓧娈碉紝灏嗕細鍙戠敓閿欒?.