mysql的join 二_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

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 joinselect

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;