sql查询之左连接，右连接，内连接以及全外连接的使用（测试常见面试题欧）

**表数据：**
/*
SQLyog v10.2 
MySQL - 5.1.62-community : Database - sience
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*Table structure for table `dept` */

CREATE TABLE `dept` (
  `id` varchar(36) NOT NULL,
  `deptno` varchar(36) NOT NULL COMMENT '部门编号',
  `dname` varchar(36) NOT NULL COMMENT '部门名称',
  `loc` varchar(36) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `dept` */

insert  into `dept`(`id`,`deptno`,`dname`,`loc`) values ('1','01','测试部','测试'),('2','02','开发部','开发'),('3','04','售前部','售前'),('4','05','运维部','运维');

/*Table structure for table `emp` */

CREATE TABLE `emp` (
  `empno` varchar(36) NOT NULL COMMENT '员工编号',
  `ename` varchar(36) NOT NULL COMMENT '员工姓名',
  `job` varchar(20) NOT NULL COMMENT '职位',
  `sal` varchar(20) NOT NULL COMMENT '工资',
  `deptno` varchar(36) NOT NULL COMMENT '部门编号',
  `mgr` varchar(20) NOT NULL COMMENT '上级领导编号',
  `comm` varchar(20) DEFAULT NULL COMMENT '奖金',
  `hiredate` datetime NOT NULL COMMENT '入职时间',
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `emp` */

insert  into `emp`(`empno`,`ename`,`job`,`sal`,`deptno`,`mgr`,`comm`,`hiredate`) values ('002','李四','开发','6000','02','002','20','2016-03-01 14:21:28'),('1','张三','测试','3000','01','001','20','2016-03-01 14:21:28'),('3','琳子','测试','4500','01','003','20','2017-03-01 14:35:07'),('4','糖衣','售前','5000','04','004','50','2016-07-11 14:36:03'),('5','古道','开发','7000','05','005','100','2015-03-01 14:36:50');

/*Table structure for table `salgrade` */

CREATE TABLE `salgrade` (
  `id` varchar(36) NOT NULL,
  `losal` varchar(36) DEFAULT NULL COMMENT '最低工资',
  `hisal` varchar(36) DEFAULT NULL COMMENT '最高工资',
  `grade` varchar(10) NOT NULL COMMENT '工资等级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `salgrade` */

insert  into `salgrade`(`id`,`losal`,`hisal`,`grade`) values ('',NULL,NULL,''),('1','1000','3000','5'),('2','3100','6000','4'),('3','6100','8000','3'),('4','8100','9000','2');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

/**
关联查询
*/

查询emp表中员工的编号，姓名以及所属部门的编号，名称

SELECT
emp.empno ‘员工编号’,
emp.ename ‘员工名称’,
dept.deptno ‘部门编号’,
dept.dname ‘部门名称’
FROM
dept,emp
WHERE emp.deptno = dept.deptno

查询dept表中部门的编号，名称，地址，以及该部门员工的编号，姓名，职位，工资

SELECT
d.deptno,
d.dname,
d.loc,
e.empno,
e.ename,
e.job,
e.sal
FROM
dept AS d,
emp AS e
WHERE d.deptno = e.deptno

查询emp表中工资在1000-3000之间的每一个员工的编号，姓名，工资以及所属部门的编号，名称，最后根据部门编号进行升序排列，如果部门编号一直，根据工资进行降序排序

SELECT
e.empno,
e.ename,
e.sal,
d.deptno,
d.dname
FROM
emp e,
dept d
WHERE e.deptno = d.deptno
AND e.sal BETWEEN 1000
AND 3000
ORDER BY deptno ASC,
e.sal DESC ;

/**
非等值连接查询
*/

#查询emp表中员工的编号，姓名，工资以及该工资等级（注意，此时要注意工资要在最低工资与最高工资之间）
SELECT
e.empno,
e.ename,
e.sal,
s.grade
FROM
emp e,
salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

#查询emp表中员工的姓名，职位，工资以及该工资的等级，该等级下的最高工资
SELECT
e.ename,
e.job,
e.sal,
s.grade,
s.hisal
FROM
emp e,
salgrade s
WHERE e.sal BETWEEN s.losal
AND s.hisal

#查询salgrade表中，工资的等级，最低工资，最高工资，以及该等级下，员工的所有的信息，最后根据工资的等级进行升序排列，如果工资的等级一直，根基该员工的工资进行升序排列
SELECT
s.*
FROM
salgrade s,
emp e
WHERE e.sal BETWEEN s.losal
AND s.hisal
ORDER BY s.grade ASC,
e.sal DESC ;

/**
*自连接====所需要查询的数据都来源于同一张表，而且二个列之间有关联关系，吧一张表当成二张表，通过关联条件连接起来进行查询插座。
领导也属于员工
*/

查询emp表中员工的编号，姓名，以及上级领导的编号，姓名【将emp看成二张表，员工表和领导表，员工表中领导的编号等于领导表中员工的编号】

SELECT
e.empno,
e.ename,
m.empno ‘上级领导编号’,
m.ename ‘上级领导姓名’
FROM
emp e,
emp m
WHERE e.mgr = m.empno

/**
内连接====inner join ..on
*/

查询emp表中员工的编号，姓名，职位，以及所属部门的编号，地址

SELECT
e.empno,
e.ename,
e.job,
d.deptno,
d.loc
FROM
emp e
INNER JOIN dept d
ON e.deptno = d.deptno

查询dept表中部门的编号，名称，以及该部门下的员工的所有信息

SELECT
d.deptno,
d.dname,
e.*
FROM
dept d
INNER JOIN emp e
ON d.deptno = e.deptno

查询emp表中员工的姓名，职位，工资，以及该工资的等级，最低工资，并且根据等级进行升序排列，如果等级一直，根据员工的工资进行升序排列

SELECT
e.ename,
e.job,
e.sal,
s.grade,
s.losal,
s.hisal
FROM
emp e
INNER JOIN salgrade s ON e.sal BETWEEN s.losal
AND s.hisal
ORDER BY s.hisal ASC,
e.sal ASC

/**
*左外连接查询=========以左边的表为主，查询左表的所有数据以及与之关联的右表中的数据,不管右边是否为空

格式：select …from 表1名称 left join 表二名称 on 条件（一般都是二表中共同的字段，也成关联字段）
*/

#查询emp 表中所有的信息以及员工所属的部门的编号

SELECT
e.*,
d.deptno
FROM
emp e
LEFT JOIN dept d
ON e.deptno = d.deptno

#查询emp表中所有员工的编号，姓名，职位，工资以及上级领导的编号，姓名，工资
SELECT
e.empno,
e.ename,
e.job,
m.empno,
m.ename,
m.sal
FROM
emp e
LEFT JOIN emp m
ON e.mgr = m.empno

/**
右外连接===以右边的表为主，不管左边是否为空
*/
#查询dept表中所有的部门的信息，以及对应部门下的员工的编号，姓名，职位
SELECT d.*,
e.empno,
e.ename,
e.job FROM dept d
RIGHT JOIN emp e
ON d.deptno = e.deptno

查询工资在1000到3000之间emp表中所有员工的编号，姓名，工资，入职时间，以及该工资的等级，最后根据等级进行升序排列，如果等级一直，根据工资进行将序排列

SELECT
e.*,
s.grade
FROM
emp e
RIGHT JOIN salgrade s
ON e.sal = s.grade
ORDER BY s.grade ASC,
e.sal ASC

#查询emp表中名字没有“古”的所有员工的编号，姓名，职位，工资以及上级领导的编号，姓名，职位，工资，最后根据员工的工资进行升序排列，如果工资一直，根基编号进行降序排列（右外）
SELECT
e.*,
m.empno,
m.ename,
m.job,
m.sal
FROM
emp m
RIGHT JOIN emp e
ON e.empno = m.mgr
WHERE e.ename NOT LIKE ‘%古%’
ORDER BY e.sal ASC,
m.empno

/**
全外连接
*/
#查询emp表中素有员工的编号，姓名，职位，以及dept表中所有部门的所有信息
SELECT
e.ename,
e.empno,
e.job,
d.*
FROM
emp e FULL
OUTER JOIN dept d
ON e.deptno = d.deptno