sql語句中左右連接的使用方法

1、select * from a inner join b on a.bid = b.bid;SQl中的左連接，右連接，內(nèi)連接，全連接，自連接的理解按照自己的理解做個(gè)筆記，以下在Sql Server2005和Oracle92中都運(yùn)行通過：create table a(aid varchar(2),aname varchar(10),bid varchar(2);create table b(bid varchar(2),bname varchar(10);insert into a values(s1,sname1,c1);insert into a values(s2,sname2,c2);i

2、nsert into a values(s3,sname3,c3);insert into a values(s4,sname4,c4);insert into a values(s5,sname5,c5);insert into b values(c1,cname1);insert into b values(c2,cname2);insert into b values(c3,cname3);insert into b values(c6,cname6);insert into b values(c7,cname7);-Iner Join和Join和，(如select * from a,b

3、 where a.id=b.id),這三個(gè)效果一樣.select * from a , b where a.bid = b.bid;select * from a join b on a.bid = b.bid;J緒果緒果j消息消息aid anamebidbidbnameJ_jsismamelclcl cnomel252sname2c2c2 cnanne23s3$name3c3c3 cnanne3aid anamebidbidbnameJ_i sienamelclcl enamel 1,_1s3sname2c2c2 cname2$iname3c3c3 cnanne3aid a什占mebrdbi

4、dbnameJ_:s1snamelclcl enamel252&name2c2c2cname23s3snameSc3c3 cname3承查詢巨成功訪承查詢巨成功訪 -Left Join和Left Outer Join左連接、左外連接，效果一樣。select * from a left join b on a.bid = b.bid;select * from a left outer join b on a.bid = b.bid;功結(jié)果功結(jié)果| |. .消息消息iaid1anamebidbidbn 所IE1 Nenamelc1c1dcramd2蛇sname?sname?c2c2cna

5、nne?_3_s3snarme3snarme3c3 c3cnarre3_4_s4sname404 NULLNULL_5_s5snameSc5 NULLNULL疵anamebidbidbriameJ_enamelenamelc1c1denamel2_芍sname2c2 c2cnarre2J_srtameSc3 c3cnam634$4snarme4snarme4c4 NULLNULL5x5snameS05 NULLNULL曜查曜查誨已成功執(zhí)東誨已成功執(zhí)東-Right Join和Right Outer Join右連接、右外連接，效果一樣。select * from a right join b on

6、 a.bid = b.bid;select * from a right outer join b on a.bid = b.bid;El結(jié)果結(jié)果心消息心消息| |aidanamebdbidbnamej_Is1snamel卜. ._ _clclenamel2_蛭$name2c2c2cname2s3$name3c3c3cname34NULLNULLNULLc6cnameB5NULLNULLNULLc?cname7aidanamebid |bidbname1廠二js1snamelclclenamel2s2sname?c2c2cname?3.snarneSc3c3cname3_4_NULLNULLN

7、ULLc6cnameG5NULLNULLNULLc7cnam67白白查詢已成功執(zhí)行查詢已成功執(zhí)行. .-Full Join和Full Outer Join全連接、全外連接，效果一樣。select * from a full join b on a.bid = b.bid;select * from a full outer join b on a.bid = b.bid;13結(jié)果消息結(jié)果消息| |朝anamebiidbidbnarriBi dsnamelc1dcnanrtel! 2s2sname?c2c2cnama2“;3sname3c3c3cnarreSs4snarmelsnarmelc4NULLNULL_5_5_s5一sname5c5NULLNULLJ J_ _NULLNULLNULLcGcnameG2_2_NULLNULLNULLc7cname7aidanamebidbid bname：i i j_j_jTI. di sisnamelc1c1cnanoel2 2s2sname2c2c2cnameS3 3s3sn