浅谈sql连接查询的区别 inner,left,right,full
--table1表
IDNAMEQQPHONE
1秦云1010280013500000
2在路上1037813600000
3LEO1000013900000
4秦云024145854564512
--table2表
IDNAMEsjsjgly
1秦云2004-01-0100:00:00.000李大伟
2秦云2005-01-0100:00:00.000马化腾
3在路上2005-01-0100:00:00.000马化腾
4秦云2005-01-0100:00:00.000李大伟
5在路上2005-01-0100:00:00.000李大伟
--innerjoin 只要是符合要求的就显示
select*fromtable1t1 innerjointable2t2ont1.name=t2.name<br><br>--得到的结果
IDNAMEQQPHONEIDNAMEsjsjgly
1秦云10102800135000001秦云2004-01-0100:00:00.000李大伟
4秦云0241458545645121秦云2004-01-0100:00:00.000李大伟
1秦云10102800135000002秦云2005-01-0100:00:00.000马化腾
4秦云0241458545645122秦云2005-01-0100:00:00.000马化腾
2在路上10378136000003在路上2005-01-0100:00:00.000马化腾
1秦云10102800135000004秦云2005-01-0100:00:00.000李大伟
4秦云0241458545645124秦云2005-01-0100:00:00.000李大伟
2在路上10378136000005在路上2005-01-0100:00:00.000李大伟
--leftjoin以左表为主，会看到若右表有关联字段重复，则左边关联的字段信息翻倍。
select*fromtable1t1（1） leftjointable2t2ont1.name=t2.name 与 select*fromtable2t2（2） rightjointable1t1ont1.name=t2.name 结果相等，不过两表的顺序换一下而已。 （1）结果 IDNAMEQQPHONEIDNAMEsjsjgly 1秦云10102800135000001秦云2004-01-0100:00:00.000李大伟 1秦云10102800135000002秦云2005-01-0100:00:00.000马化腾 1秦云10102800135000004秦云2005-01-0100:00:00.000李大伟 2在路上10378136000003在路上2005-01-0100:00:00.000马化腾 2在路上10378136000005在路上2005-01-0100:00:00.000李大伟 3LEO1000013900000NULLNULLNULLNULL 4秦云0241458545645121秦云2004-01-0100:00:00.000李大伟 4秦云0241458545645122秦云2005-01-0100:00:00.000马化腾 4秦云0241458545645124秦云2005-01-0100:00:00.000李大伟 （2）结果 IDNAMEsjsjglyIDNAMEQQPHONE 1秦云2004-01-0100:00:00.000李大伟1秦云1010280013500000 2秦云2005-01-0100:00:00.000马化腾1秦云1010280013500000 4秦云2005-01-0100:00:00.000李大伟1秦云1010280013500000 3在路上2005-01-0100:00:00.000马化腾2在路上1037813600000 5在路上2005-01-0100:00:00.000李大伟2在路上1037813600000 NULLNULLNULLNULL3LEO1000013900000 1秦云2004-01-0100:00:00.000李大伟4秦云024145854564512 2秦云2005-01-0100:00:00.000马化腾4秦云024145854564512 4秦云2005-01-0100:00:00.000李大伟4秦云024145854564512
--leftjoin已右表为主，与left同理
select*fromtable1t1 rightjointable2t2ont1.name=t2.name IDNAMEQQPHONEIDNAMEsjsjgly 1秦云10102800135000001秦云2004-01-0100:00:00.000李大伟 4秦云0241458545645121秦云2004-01-0100:00:00.000李大伟 1秦云10102800135000002秦云2005-01-0100:00:00.000马化腾 4秦云0241458545645122秦云2005-01-0100:00:00.000马化腾 2在路上10378136000003在路上2005-01-0100:00:00.000马化腾 1秦云10102800135000004秦云2005-01-0100:00:00.000李大伟 4秦云0241458545645124秦云2005-01-0100:00:00.000李大伟 2在路上10378136000005在路上2005-01-0100:00:00.000李大伟
--fulljoin 两表数据都取出来,不管是否符合
select*fromtable1t1 fulljointable2t2ont1.name=t2.name 结果 IDNAMEQQPHONEIDNAMEsjsjgly 1秦云10102800135000001秦云2004-01-0100:00:00.000李大伟 1秦云10102800135000002秦云2005-01-0100:00:00.000马化腾 1秦云10102800135000004秦云2005-01-0100:00:00.000李大伟 2在路上10378136000003在路上2005-01-0100:00:00.000马化腾 2在路上10378136000005在路上2005-01-0100:00:00.000李大伟 3LEO1000013900000NULLNULLNULLNULL 4秦云0241458545645121秦云2004-01-0100:00:00.000李大伟 4秦云0241458545645122秦云2005-01-0100:00:00.000马化腾 4秦云0241458545645124秦云2005-01-0100:00:00.000李大伟
以上就是小编为大家带来的浅谈sql连接查询的区别inner,left,right,full全部内容了，希望大家多多支持毛票票~