MySQL简单使用二、join使用

--------join-----------
inner join：内连接 等值连接
left join:左链接
right join:右链接

create table testa(aid int,aname varchar(100));
create table testb(bid int,bname varchar(100),age int);

一、单个on条件

testa表

aid  aname
1    zhangsan
2    lisi
3    wangwu
4    hanmeimei

testb表

bid  bname      age
1    zhangsan   18
2    lisi       20
3    wangwu     25
5    lilei      23

1. left join

   select 
   a.aid,a.aname,b.bid,b.bname 
   from testa a
   left join 
   testb b 
   on
   a.aid = b.bid
   1    zhangsan    1    zhangsan
   2    lisi        2    lisi
   3    wangwu      3    wangwu
   4    hanmeimei   null null
   

   left join:右表去匹配左表，以左表数据为准(on后边的条件)，且最全，匹配不到则为null值

2. right join

   select 
   a.aid,a.aname,b.bid,b.bname 
   from testa a
   right join 
   testb b 
   on
   a.aid = b.bid
   1    zhangsan   1    zhangsan 
   2    lisi       2    lisi     
   3    wangwu     3    wangwu   
   null null       4    hanmeimei
   

   right join:左表去匹配右表，以右表数据为准(on后边的条件)，且最全，匹配不到则为null值

3. inner join

   select 
   a.aid,a.aname,b.bid,b.bname 
   from testa a
   inner join 
   testb b 
   on
   a.aid = b.bid
   1    zhangsan   1    zhangsan 
   2    lisi       2    lisi     
   3    wangwu     3    wangwu 
   

   inner join:等值匹配，两边表都要存在

二、多个on条件

testa表

aid  aname
1    zhangsan1
2    lisi1
3    wangwu
4    hanmeimei

testb表

bid  bname      age
1    zhangsan2   18
2    lisi2       20
3    wangwu      25
5    lilei       23

1. left join

   select 
   a.aid,a.aname,b.bid,b.bname 
   from testa a
   left join 
   testb b 
   on
   a.aid = b.bid and a.aname = b.bname;
   
   1    zhangsan1   null  null
   2    lisi1       null  null
   3    wangwu      3     wangwu
   4    hanmeimei   null  null
   

三、数值相加以及大小、区间

select 
a.ename,a.deptno,(a.sal+IFNULL(a.comm, 0)) as salcomm ,
s.grade,
b.dname,b.loc
from emp a
left join dept b on a.deptno=b.deptno
left join salgrade s 
on (a.sal+IFNULL(a.comm, 0)) between s.losal and s.hisal;