sql中的join的使用 sql中join的几种常见用法总结
SQL\u8bed\u53e5\u5982\u4f55\u4f7f\u7528join?join\u5206\u4e3aleft join, inner join, right join\uff0c \u9ed8\u8ba4\u662finner join
left join \u8868\u793a\u7ed3\u679c\u5df2\u5de6\u4fa7\u4e3a\u51c6\uff0c\u5f53\u53f3\u4fa7\u6ca1\u6709\u5de6\u4fa7\u7684\u8bb0\u5f55\u662f\uff0c\u5b57\u6bb5\u503c\u5df2null\u8868\u793a
right join \u8868\u793a\u7ed3\u679c\u5df2\u53f3\u4fa7\u4e3a\u51c6\uff0c\u5f53\u5de6\u4fa7\u6ca1\u6709\u53f3\u4fa7\u7684\u8bb0\u5f55\u662f\uff0c\u5b57\u6bb5\u503c\u5df2null\u8868\u793a
inner join \u8868\u793a\u83b7\u53d6\u4e24\u4e2a\u8868\u7684\u5171\u6709\u8bb0\u5f55\uff0c\u548c\u8fde\u8868\u67e5\u8be2\u76f8\u540c
select id,name,title,type1 from table1
join
select type2 from table2
on table1.id=table2.id
JOIN\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\uff0c\u5305\u62ec\u4e09\u79cd\uff1a INNER JOIN\u8fd0\u7b97\u5f0f\uff1a\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\u3002 LEFT JOIN\u8fd0\u7b97\u5f0f\uff1a\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\uff0c\u5e76\u5c06\u5305\u542b\u4e86LEFT JOIN\u5de6\u8fb9\u8868\u4e2d\u7684\u5168\u90e8\u8bb0\u5f55\u3002 RIGHT JOIN\u8fd0\u7b97\u5f0f\uff1a\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\uff0c\u5e76\u5c06\u5305\u542b\u4e86RIGHT JOIN\u53f3\u8fb9\u8868\u4e2d\u7684\u5168\u90e8\u8bb0\u5f55\u3002 INNER JOIN\u8bbe\u5b9a\u4e24\u4e2a\u8868\u76f8\u5173\u8fde\u7684\u8fd0\u7b97\u5f0f\uff0c\u4ee5\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\u3002 INNER JOIN\u8bed\u6cd5\u5982\u4e0b\uff1a FROM \u88681 INNER JOIN \u88682 ON \u88681.\u5b57\u6bb51 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb52 \u4e24\u4e2a\u8868\u8fde\u63a5\u7684\u5b57\u6bb5\uff0c\u8b6c\u5982 [\u88681.\u5b57\u6bb51=\u88682.\u5b57\u6bb52]\uff0c\u5fc5\u987b\u5177\u6709\u76f8\u540c\u7684\u5b57\u6bb5\u7c7b\u578b\uff0c\u4f46\u662f\u5b57\u6bb5\u540d\u79f0\u4e0d\u9700\u8981\u76f8\u540c\u3002 \u4f8b\u5982\uff0c\u81ea\u52a8\u7f16\u53f7\u5b57\u6bb5\u7c7b\u578b\u53ef\u4ee5\u8fde\u63a5Long \u7684\u5b57\u6bb5\u7c7b\u578b\uff0c\u4f46\u662f\u5355\u7cbe\u6574\u6570\u5b57\u6bb5\u7c7b\u578b\u4e0d\u80fd\u8fde\u63a5\u53cc\u7cbe\u6574\u6570\u7684\u5b57\u6bb5\u7c7b\u578b\u3002 \u6bd4\u8f83\u8fd0\u7b97\u5b50\u53ef\u4e3a=\u3001\u3001=\u3001\u6216\u3002 JOIN\u8fde\u63a5\u7684\u5b57\u6bb5\uff0c\u4e0d\u53ef\u4ee5\u5305\u542bMEMO\u5b57\u6bb5\u7c7b\u578b\u6216OLE\u5bf9\u8c61\u7c7b\u578b\uff0c\u5426\u5219\u4f1a\u53d1\u751f\u9519\u8bef\u3002 \u5728\u4e00\u4e2aJOIN\u8868\u8fbe\u5f0f\u4e2d\uff0c\u53ef\u4ee5\u8fde\u7ed3\u591a\u4e2aON\u5b50\u53e5\uff1a SELECT fields FROM \u88681 INNER JOIN \u88682 ON \u88681.\u5b57\u6bb51 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb51 AND ON \u88681.\u5b57\u6bb52 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb52) OR ON \u88681.\u5b57\u6bb53 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb53) JOIN\u8868\u8fbe\u5f0f\u4e2d\uff0c\u53ef\u4ee5\u4e3a\u5de2\u72b6\u5f0f\uff1a SELECT fields FROM \u88681 INNER JOIN (\u88682 INNER JOIN [( ]\u88683 [INNER JOIN [( ] \u8868x [INNER JOIN ...)] ON \u88683.\u5b57\u6bb53 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u8868x.\u5b57\u6bb5x)] ON \u88682.\u5b57\u6bb52 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88683.\u5b57\u6bb53) ON \u88681.\u5b57\u6bb51 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb52 \u5728\u4e00\u4e2aINNER JOIN\u4e2d\uff0c\u53ef\u4ee5\u5305\u62ec\u5de2\u72b6\u5f0f\u7684LEFT JOIN\u6216RIGHT JOIN\uff0c\u4f46\u662f\u5728\u4e00\u4e2aLEFT JOIN\u6216RIGHT JOIN\u4e2d\u4e0d\u80fd\u5305\u62ec\u5de2\u72b6\u5f0f\u7684INNER JOIN\u3002
以下部分是我抄的给个通俗的解释吧.
例表a
aid adate
1 a1
2 a2
3 a3
表b
bid bdate
1 b1
2 b2
4 b4
两个表a,b相连接,要取出id相同的字段
select * from a ,b where a.aid = b.bid这是仅取出匹配的数据.
此时的取出的是:
1 a1 b1
2 a2 b2
那么left join 指:
select * from a left join b on a.aid = b.bid
首先取出a表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
3 a3 空字符
同样的也有right join
指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
4 空字符 b4
以下是我的补充
完整外联接就是包含左向外联接和右向外联接的连接，我想聪明的你一定可以举一反三，我也就不多费唇舌了。如果还不理解，给我消息，我会很清楚的给你解释你不懂的地方
declare @employees table(Employee_ID int , Name nvarchar(20))
insert into @employees select 01, 'Hansen Ola' union all
select 02, 'Svendson Tove' union all
select 03, 'Svendson Stephen' union all
select 04, 'Pettersen Kari'
declare @Orders table(Prod_ID int, Product nvarchar(10),Employee_ID int)
insert into @Orders select 234,'Printer',01 union all
select 657,'Table',03 union all
select 865,'Chair',03
--full outer join
select a.Employee_ID,Name,Product from @Employees as a full outer join @Orders as b on a.Employee_ID=b.Employee_ID
/*结果
Employee_ID Name Product
----------- -------------------- ----------
1 Hansen Ola Printer
2 Svendson Tove NULL
3 Svendson Stephen Table
3 Svendson Stephen Chair
4 Pettersen Kari NULL
*/
--inner join
select a.Employee_ID,Name,Product from @Employees as a inner join @Orders as b on a.Employee_ID=b.Employee_ID
/*结果
Employee_ID Name Product
----------- -------------------- ----------
1 Hansen Ola Printer
3 Svendson Stephen Table
3 Svendson Stephen Chair
*/
--left join
select a.Employee_ID,Name,Product from @Employees as a left join @Orders as b on a.Employee_ID=b.Employee_ID
/*
Employee_ID Name Product
----------- -------------------- ----------
1 Hansen Ola Printer
2 Svendson Tove NULL
3 Svendson Stephen Table
3 Svendson Stephen Chair
4 Pettersen Kari NULL
*/
--right join
select a.Employee_ID,Name,Product from @Employees as a right join @Orders as b on a.Employee_ID=b.Employee_ID
/*
Employee_ID Name Product
----------- -------------------- ----------
1 Hansen Ola Printer
3 Svendson Stephen Table
3 Svendson Stephen Chair
*/
连接两个表时的条件放到on后
1=1的话就是说任何条件都成立
比如select
*
from
table1
t1
join
table2
t2
on
t1.code=t2.code
绛旓細鍦SQL涓栫晫涓紝JOIN鎿嶄綔濡傚悓杩炴帴涔愯氨锛屽皢鏁版嵁浠庡涓〃涓紪缁囨垚涓骞呭畬鏁寸殑淇℃伅鐢诲嵎銆傝鎴戜滑娣卞叆鎺㈣JOIN鐨榄旀硶锛屼粠鍐呰繛缁(INNER JOIN)寮濮嬶紝瀹冩槸杩炴帴鐨勫熀纭锛屼緥濡傛煡璇笢浜晢鍝佸晢搴楃殑璇︾粏淇℃伅銆傚唴杩炵粨锛氭晥鐜囦笌绠娲佽鐐逛竴: 鍐呰繛缁撶殑鍏抽敭鍦ㄤ簬FROM瀛愬彞锛屽畠濡傝皟鑹叉澘锛屽悓鏃惰瀺鍚堝寮犺〃锛屽 FROM ShopProduct AS SP...
绛旓細LEFT JOIN: 鍗充娇鍙宠〃涓病鏈夊尮閰嶏紝涔熶粠宸﹁〃杩斿洖鎵鏈夌殑琛 RIGHT JOIN: 鍗充娇宸﹁〃涓病鏈夊尮閰嶏紝涔熶粠鍙宠〃杩斿洖鎵鏈夌殑琛 FULL JOIN: 鍙鍏朵腑涓涓〃涓瓨鍦ㄥ尮閰嶏紝灏辫繑鍥炶 SQL INNER JOIN 鍏抽敭瀛 鍦ㄨ〃涓瓨鍦ㄨ嚦灏戜竴涓尮閰嶆椂锛孖NNER JOIN 鍏抽敭瀛楄繑鍥炶銆侷NNER JOIN 鍏抽敭瀛楄娉 SELECT column_name(s)FROM ...
绛旓細JOIN杩炴帴缁勫悎涓や釜琛ㄤ腑鐨勫瓧娈佃褰曪紝鍖呮嫭涓夌锛 INNER JOIN杩愮畻寮忥細杩炴帴缁勫悎涓や釜琛ㄤ腑鐨勫瓧娈佃褰銆 LEFT JOIN杩愮畻寮忥細杩炴帴缁勫悎涓や釜琛ㄤ腑鐨勫瓧娈佃褰曪紝骞跺皢鍖呭惈浜哃EFT JOIN宸﹁竟琛ㄤ腑鐨勫叏閮ㄨ褰曘 RIGHT JOIN杩愮畻寮忥細杩炴帴缁勫悎涓や釜琛ㄤ腑鐨勫瓧娈佃褰曪紝骞跺皢鍖呭惈浜哛IGHT JOIN鍙宠竟琛ㄤ腑鐨勫叏閮ㄨ褰曘 INNER JOIN...
绛旓細JOIN 鍦ㄥ唴杩炴帴鏃讹紝鍙互涓嶄娇鐢紝鍏跺畠绫诲瀷杩炴帴蹇呴』浣跨敤銆傚SELECT * FROM TABLEA INNER JOIN TABLEB ON A.ID=B.ID 鍙互杩欐牱鍐欙細SELECT * FROM TABLEA,TABLEB WHERE A.ID=B.ID JOIN 鏈変互涓嬪嚑绉嶇被鍨嬶細INNER(鍐呰繛鎺ワ級鎸囧畾杩斿洖姣忓鍖归厤鐨勮銆傚簾寮冧袱涓〃涓笉鍖归厤鐨勮銆傚鏋滄湭鎸囧畾鑱旀帴绫诲瀷锛屽垯杩欐槸...
绛旓細鍦 SQL 涓锛屽彲浠浣跨敤杩炴帴锛JOIN锛夋搷浣滄潵瀹炵幇鏍规嵁琛 1 涓殑瀛楁鈥滅墿鏂欑紪鐮佲濊幏鍙栧叾鍦ㄨ〃 2 涓搴旂殑淇℃伅銆備互涓嬫槸涓绉嶅父瑙佺殑瀹炵幇鏂瑰紡锛氬亣璁捐〃 1 鐨勫悕绉颁负 table1 锛岃〃 2 鐨勫悕绉颁负 table2 锛屽苟涓旇〃 1 涓殑鐗╂枡缂栫爜瀛楁鍚嶄负 material_code 锛岃〃 2 涓殑...
绛旓細1銆佹潵鑷袱涓叧绯荤殑鍏冪粍瀵瑰湪鍏卞悓灞炴т笂鐨勫肩浉鍚屻傦紙涓嶉檺浜庝竴涓叡鍚屽睘鎬э紝涔熷彲浠ユ槸澶氫釜鍏卞悓灞炴э級 2銆佸幓鎺夐噸澶嶇殑灞炴э紙鍒楋級銆 3銆佸垪鍑哄睘鎬х殑椤哄簭锛氬厛鏄叡鍚屽睘鎬э紝鐒跺悗鏄涓涓叧绯荤殑灞炴э紝鏈鍚庢槸绗簩涓叧绯荤殑灞炴с備负浜嗛槻姝袱涓〃鏈夊涓叡鍚屽睘鎬ф椂natural join 浼氬拷鐣ラ儴鍒嗛渶瑕佺殑鍏冪粍瀵癸紝搴浣跨敤join鈥...
绛旓細SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons, OrdersWHERE Persons.Id_P = Orders.Id_P缁撴灉闆嗭細LastNameFirstNameOrderNoAdamsJohn22456AdamsJohn24562CarterThomas77895CarterThomas44678SQL JOIN - 浣跨敤 Join闄や簡涓婇潰鐨勬柟娉曪紝鎴戜滑涔熷彲浠ヤ娇鐢ㄥ叧閿瘝 JOIN 鏉ヤ粠涓や釜琛ㄤ腑鑾峰彇鏁版嵁銆
绛旓細閭ｄ箞left join 鎸:select * from a left join b on a.aid = b.bid 棣栧厛鍙栧嚭a琛ㄤ腑鎵鏈夋暟鎹,鐒跺悗鍐嶅姞涓婁笌a,b鍖归厤鐨勭殑鏁版嵁 姝ゆ椂鐨勫彇鍑虹殑鏄:1 a1 b1 2 a2 b2 3 a3 绌哄瓧绗 鍚屾牱鐨勪篃鏈塺ight join 鎸囩殑鏄鍏堝彇鍑篵琛ㄤ腑鎵鏈夋暟鎹,鐒跺悗鍐嶅姞涓婁笌a,b鍖归厤鐨勭殑鏁版嵁 姝ゆ椂鐨勫彇鍑虹殑鏄:1 a1 b1 2 ...
绛旓細鍙宠繛鎺ュ氨鏄粠绗涘崱灏旂Н涓寫鍑篛N瀛愬彞鏉′欢鎴愮珛鐨勮褰曪紝鐒跺悗鍔犱笂鍙宠〃涓墿浣欑殑璁板綍 宸﹀閾炬帴锛坙eft outter join)锛氬乏澶栭摼鎺eft outter join灏辨槸姹傚乏琛ㄥ幓闄や袱涓〃鐨勪氦闆嗛儴鍒嗗墿涓嬫暟鎹傚彸澶栭摼鎺ワ紙right outter join)锛氬彸澶栭摼鎺ight outter join灏辨槸姹傚彸琛ㄥ幓闄や袱涓〃鐨勪氦闆嗛儴鍒嗗墿涓嬫暟鎹
