sql的on有什么用? sql里的on关键字是干吗的？
sql server\u6570\u636e\u5e93\u6709\u4ec0\u4e48\u7528\u6570\u636e\u662f\u5b58\u50a8\u6570\u636e\u7684\uff0c\u5047\u5982\u4f60\u73b0\u5728\u5728\u6dd8\u5b9d\u4e0a\u9762\u6ce8\u518c\u4e86\u4e00\u4e2a\u8d26\u53f7\uff0c\u8fd9\u4e2a\u8d26\u53f7\u4fe1\u606f\u5c31\u4f1a\u5b58\u5230\u6dd8\u5b9d\u7684\u6570\u636e\u5e93\u91cc\u9762\uff0c\u4e0b\u6b21\u4f60\u767b\u9646\u7684\u65f6\u5019\u624d\u80fd\u77e5\u9053\u4f60\u5df2\u7ecf\u6ce8\u518c\u8fc7
\u4e0d\u662fon\uff0c\u662fjion on
\u8868\u793a\u4e24\u4e2a\u8868\u4f9d\u9760c.id\u548cd.id\u8fde\u63a5
\u6253\u4e2a\u6bd4\u65b9
select c.*,d.* from c
join d on a.c.id=d.id
\u8fd9\u4e2a\u7b49\u540c\u4e8e
select c.*,d.* from c,d where c.id=d.id
作用类似WHERE.
只有用join 的时候 才用.
有:
inner join ...on
left outer join ...on
right outer join ...on
几种情况.
如：
SELECT *
FROM TABALE1 A
LEFT OUTER JOIN TABLE2 B ON A.COL1=B.COL2.
SELECT COL FROM TABLE1 [LEFT RIGHT INNER NATURAL FULL]JOIN TABLE2
ON 条件，比如
SELECT * FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
绛旓細on and 鏄寚杩欏墠鍚庝袱涓〃杩炴帴鐨勬潯浠 涓嶆涓涓潯浠讹紝涓や釜琛ㄤ細鎸夋墍鏈夋潯浠惰繘琛屽墠鍚庤繛鎺ワ紝杩檕n鍜宎nd宸﹀彸鍑虹幇鐨勬槸鍒椾笌鍒楃殑鍏崇郴銆備粬鏄疉鍜孊鍏ㄩ儴鏁版嵁鎸夎姹傜殑杩炴帴銆俹n where 鏄寚杩欏墠鍚庝袱涓〃鐨勮繛鎺ユ潯浠跺彧鏈塷n鍚庨潰鐨勪竴涓紝鐒跺悗瀵硅繛鎺ュソ鐨勭粨鏋滐紝鍘绘墽琛寃here鏉′欢鏌ヨ锛寃here鍚庨潰鐨勫垪鍙互涓嶆槸杩炴帴鐩稿叧鐨勫垪锛...
绛旓細闂鍑哄湪鍝噷鍛紵杩欓噷鐗垫壇鍒扮殑鏄sql璇彞涓璷n鍚庣殑鏉′欢鍜寃here鍚庣殑鏉′欢鐨勬墽琛岄『搴忛棶棰樸傝繖閲岋紝鏈夊繀瑕佸啀娆℃槑纭竴涓媗eft join...on... 璇硶鐨勫噯纭惈涔夈傛湁浜嗕笂闈㈢殑鍩虹鐭ヨ瘑锛屽氨鍙互杩涗竴姝ュ幓鐞嗚В闃愯堪浜嗐傛湁鏃跺欙紝寰堢畝鍗曠殑闂锛屽嵈骞朵笉绠鍗曘傚洖澶寸湅锛岃繕鏄鍩虹姒傚康鎶婃彙鐨勪笉澶熸槑鏅帮紝鏈夋ā绯婄殑鍦版柟銆傝繖閲岀殑on...
绛旓細鎭╋紒鏄殑锛on鏄寚瀹氫袱涓〃涔嬮棿鐨勬潯浠讹紒鎴戜釜浜鸿涓猴紝鍐呰仈鎺ュ彲浠ョ敤where鏉ヤ唬鏇匡紝涓嶈繃鎴戣繕鏄枩娆㈢敤inner join 濡傛灉鏄鑱旀帴锛岄偅涔坙eft join on ,right join on ,full join on 鍏卞悓瀛︿範锛
绛旓細鎵惧嚭鏉ョ殑璁板綍鏉℃暟灏辨槸宸﹁竟琛ㄧ殑璁板綍鏁 鍏蜂綋鐢ㄦ硶濡備笅锛歋ELECT column_name(s)FROM table_name1LEFT JOIN table_name2ON table_name1.column_name=table_name2.column_name 鎷撳睍鏉愭枡锛氫娇鐢"left join...on"鐨勪綔鐢ㄥ湪浜庝粠t1琛ㄤ腑杩斿洖鎵鏈夎锛屽鏋渢2琛ㄤ腑娌℃湁鐩稿簲鐨勮褰曞垯琛モ渘ull鈥濄傝瑙佷笅鍥 ...
绛旓細鍦ㄥ琛ㄨ仈鎺ユ煡璇㈡椂锛on姣攚here鏇存棭璧蜂綔鐢ㄣ傜郴缁熼鍏堟牴鎹悇涓〃涔嬮棿鐨勮仈鎺ユ潯浠讹紝鎶婂涓〃鍚堟垚涓涓复鏃惰〃鍚庯紝鍐嶇敱where杩涜杩囨护锛岀劧鍚庡啀璁＄畻锛岃绠楀畬鍚庡啀鐢 having杩涜杩囨护銆傜敱姝ゅ彲瑙侊紝瑕佹兂杩囨护鏉′欢璧峰埌姝ｇ‘鐨勪綔鐢紝棣栧厛瑕佹槑鐧借繖涓潯 浠跺簲璇ュ湪浠涔鏃跺欒捣浣滅敤锛岀劧鍚庡啀鍐冲畾鏀惧湪閭ｉ噷銆傦紙4锛夌敤EXISTS鏇夸唬IN銆佺敤NOT...
绛旓細浣犵浜岀寰岄潰鐨剆.vreceiptcode='SO1201070109' and s.dr=0 and b.dr=0 宸茬粡涓嶆槸鏉′欢璇彞浜嗭紝鍙槸涓ゅ紶琛ㄧ殑鍏宠仈 锛屼綔涓烘潯浠朵粬鍙兘鎺ュ湪 where 寰岄潰鎵嶈捣鍒扮瓫閫夌殑浣滅敤
绛旓細娌℃湁鍖哄埆锛孖NNER JOIN绛変环浜 JOIN锛屼綘鍙互鐞嗚В涓 JOIN鏄 INNER JOIN 鐨勭缉鍐欍1. inner join鏄唴杩炴帴,鏄剧ず绗﹀悎杩炴帴鏉′欢鐨勮褰曡娉曞涓:select select_list from table1 inner join tabl2 on table1.column1=table2.column1 2. natural join鏄涓ゅ紶琛ㄤ腑瀛楁鍚嶅拰鏁版嵁绫诲瀷閮界浉鍚岀殑瀛楁杩涜绛夊艰繛鎺ワ紝骞...
绛旓細杩欎釜鏃跺欓渶瑕佸姞涓婅〃鍚嶆墠鑳藉尯鍒嗭紝鑰岃〃鍚嶅お闀匡紝閫氬父鍙互浣跨敤鍒悕銆傚唴杩炴帴鍙互娌℃湁杩炴帴鏉′欢: 娌℃湁on涔嬪悗鐨勫唴瀹,杩欎釜鏃跺欑郴缁熶細淇濈暀鎵鏈夌粨鏋(绗涘崱灏旂Н)銆傜瓑鍊艰繛鎺ワ細鍐呰繛鎺ヨ繕鍙互浣跨敤where浠ｆ浛on鍏抽敭瀛楋紝姝ゆ柟娉曞張鍙瓑鍊艰繛鎺ャ備粠闆嗗悎璁鸿搴︾湅锛岀瓑鍊艰繛鎺ユ槸鍐呰繛鎺ョ殑瀛愰泦銆備絾姝ゆ柟娉曟墽琛屾晥鐜囨瘮杈冧綆锛屽敖閲忎笉鐢ㄣ
绛旓細inner join 鏄sql鐨鍩烘湰鍏宠仈璇硶 渚嬪琛╝,琛╞锛2涓〃鏈夊叧鑱斿瓧娈 涓鑸槸杩欐牱鍐 select from a inner join b on a.id=b.id 2涓〃鍏宠仈鏌ヨ鐩稿叧鏁版嵁鐨勬剰鎬 array鏄暟缁勭殑鎰忔濓紝鍦╯ql閲岄潰鏄病鏈夋暟缁勭殑锛屼及璁′綘璇寸殑鏄斁鍦╢rom 鍚庨潰鐨勪竴涓〃鍚 ...
绛旓細T-SQL鍦ㄦ煡璇㈠悇涓樁绾у垎鍒共浜浠涔锛氾紙1锛塅ROM 闃舵 FROM闃舵鏍囪瘑鍑烘煡璇㈢殑鏉ユ簮琛紝骞跺鐞嗚〃杩愮畻绗︺傚湪娑夊強鍒拌仈鎺ヨ繍绠楃殑鏌ヨ涓紙鍚勭join锛夛紝涓昏鏈変互涓嬪嚑涓楠わ細a.姹傜瑳鍗″皵绉備笉璁烘槸浠涔堢被鍨嬬殑鑱旀帴杩愮畻锛岄鍏堥兘鏄墽琛屼氦鍙夎繛鎺ワ紙cross join锛夛紝姹傜瑳鍗″効绉紝鐢熸垚铏氭嫙琛╒T1-J1銆俠.ON绛涢夊櫒銆傝繖涓樁娈靛...
