join练习

1、select a.operatorid,a.operatorname,b.orgname from sys_employee a  natural join sys_organization b

普通自然连接

 

2、select a.operatorid,a.operatorname,b.orgname from sys_employee a  join sys_organization b using (orgid)

如果出现多个相同对应的字段使用using

3、select a.operatorid,a.operatorname,b.orgname from sys_employee a  join sys_organization b
on a.orgid = b.orgid and a.operatorname = 'xx'

如果相对应的字段名不一致使用on

 

4、select a.operatorid,a.operatorname,b.orgname from sys_employee a left join sys_organization b
on a.orgid = b.orgid and a.operatorname = 'xx'

左连接

5、select a.operatorid,a.operatorname,b.orgname from sys_employee a right join sys_organization b
on a.orgid = b.orgid and a.operatorname = 'xx'

右连接

6、select a.operatorid,a.operatorname,b.orgname from sys_employee a full join sys_organization b
on a.orgid = b.orgid and a.operatorname = 'xx'

全连接