--------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
-
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值
-
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值
-
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
- 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;