mysql内外连接_mysql的内连接外连接查询

建表：

create table emp(

empno int primary key,

ename char(6) not null,

job char(9) not null,

mgr int,

hiredate date not null,

sal int not null,

comm int,

deptno int not null

)

create table dept(

deptno int primary key,

dname char(10) not null,

loc char(8) not null

)

create table salgrade(

grade int primary key,

losal int not null,

hisal int not null

)

准备数据：

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7566,'jones','manager',7839,'1981-04-02',2975,null,20);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7782,'clark','manager',7839,'1981-06-09',2450,null,10);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7788,'scott','analyst',7566,'1987-04-19',3000,null,20);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7839,'king','president',null,'1981-11-17',5000,null,10);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7844,'turner','salesman',7698,'1981-09-08',1500,null,30);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7876,'adams','clerk',7788,'1987-05-23',1100,null,20);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7900,'james','clerk',7698,'1981-12-03',950,null,30);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7934,'miller','clerk',7782,'1982-01-23',1300,null,10);

insert into dept(deptno,dname,loc) values(10,'accounting','new york');

insert into dept(deptno,dname,loc) values(20,'research','dallas');

insert into dept(deptno,dname,loc) values(30,'sales','chicago');

insert into dept(deptno,dname,loc) values(40,'operations','boston');

insert into salgrade(grade,losal,hisal) values(1,700,1200);

insert into salgrade(grade,losal,hisal) values(2,1201,1400);

insert into salgrade(grade,losal,hisal) values(3,1401,2000);

insert into salgrade(grade,losal,hisal) values(4,2001,3000);

insert into salgrade(grade,losal,hisal) values(5,3001,9999);

emp表：

dept表：

salgrade表：

查询：

(内连接的等值连接)查找叫allen的人所在的部门名称：

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno where e.ename='allen';

(内连接的非等值连接)查找所有人的姓名、薪水、薪水等级和对应薪水等级的最低、最高薪水：

select e.ename,e.sal,s.grade,s.losal,s.hisal from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

(内连接的自连接)列出员工的名字和他们的直接领导的名字：

select worker.ename,manager.ename from emp worker inner join emp manager on worker.mgr=manager.empno;

内连接总结：任何一张表里的记录一定要在另一张表中找到匹配的记录，否则不能出现在结果集中。

(外连接的左外连接)列出员工的名字和他们的直接领导的名字：

select worker.ename,manager.ename from emp worker left outer join emp manager on worker.mgr=manager.empno;

(外连接的右外连接)列出员工的名字和他们的直接领导的名字：

select worker.ename,manager.ename from emp manager right outer join emp worker on worker.mgr=manager.empno;

左右外连接总结：左外连接或右外连接的查询结果=内连接的结果+驱动表在匹配表中匹配不上的记录和一条null记录的组合。

(外连接的全外连接)：用的很少，暂时不介绍。