sql涔媗eft join銆乺ight join銆乮nner join鐨勫尯鍒
sql涔媗eft join銆乺ight join銆乮nner join鐨勫尯鍒?
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 鏁版嵁绫诲瀷鏁版嵁鐨勫瓧娈碉紝灏嗕細鍙戠敓閿欒.
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 鏁版嵁绫诲瀷鏁版嵁鐨勫瓧娈碉紝灏嗕細鍙戠敓閿欒.