SQL——多表连接查询

若一个查询同时涉及两个或两个以上的表， 则称之为连接查询（在FROM子句中体现)。 参与连接的表可有多个，但连接操作在两个表之间进行，即两两连接。

---

连接查询包括：

• 内连接
  • 等值连接：用“=”比较被连接列的列值
  • 非等值连接：用“>、>=、<、<=、<>”号进行比较运算
  • 自连接：特殊的内连接，一张表看成两张表，自己连接自己，必须给表取别名
• 外连接
  • 左外
  • 右外
  • 前外
• 交叉连接       

1 内连接

执行连接操作的过程：

先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组；

找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。

表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2， …

重复这个过程，直到表1中的全部元组都处理完毕为止。

select ...
    from tablename [inner] join 被连接表
    on 连接条件
    ......

——举例（引用已有数据库：bjpowernode.sql）

dept：部门表	emp：员工表	salgrade ：工资等级表
deptno：部门编号	empno：员工编号	grade：等级
dname：部门名称	ename:员工名字	losal：最低薪资
loc：部门位置	job：工作岗位	hisal：最高薪资
	mgr：上级领导编号
	hiredate：入职时间
	sal：月薪
	comm：补助/津贴
	deptno：部门编号

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

1.1 等值连接

查询每个员工的部门名称，显示员工名和部门名。

select ename,dname
from emp e join dept d
on e.deptno=d.deptno;

+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

1.2 非等值连接 

查询每个员工的工资等级，显示员工名、工资、工资等级。

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

+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

1.3 自连接 

特殊的内连接——相互连接的表物理上为同一张表。

必须为两个表取别名，使之在逻辑上成两个表(一个是查询结果表，一个查询条件表）。 注：为表指定别名时，在查询语句中其它地方， 所有用到表名处均要用别名，不能再用原表名。  

<表名>    [AS]   <表别名>  (注意与列别名的区别，group by 后不能用列别名）

查询某个员工的上级领导，显示员工名和对应的领导名。

注：员工的领导编号=领导的员工编号

select a.ename as '员工名',b.ename as '领导名'
from emp a inner join emp b
on a.mgr=b.empno;

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

 2 外连接

只限制一张表中数据必须满足连接条件， 而另一张表中数据可不满足连接条件。   

外连接与普通连接的区别: 普通连接操作只输出满足连接条件的元组

外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

左外连接:列出左边关系中所有的元组  

右外连接:列出右边关系中所有的元组

# 1.ANSI方式的外连接的语法格式为：
FROM  表1  LEFT | RIGHT |FULL [OUTER]  
    JOIN  表2  ON  <连接条件> 
 
# 2.theta方式的外连接的语法格式为：
# 左外连接（输出表1中所有内容）：
   FROM  表1, 表2  WHERE [表1.]列名(+) ＝ [表2.]列名
# 右外连接（输出表2中所有内容）：
   FROM  表1, 表2  WHERE [表1.]列名＝ [表2.]列名(+)

2.1 左外连接 

查询每个员工的上级领导。

注：主要查询左边的员工表

select a.ename as '员工', b.ename '领导'
from emp a left join emp b
on a.mgr=b.empno;

+--------+-------+
| 员工   | 领导  |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

2.1 右外连接 

查询每个员工的上级领导。

注：主要查询右边的领导表

select a.ename as '员工', b.ename '领导'
from emp a right join emp b
on a.mgr=b.empno;

+--------+--------+
| 员工   | 领导   |
+--------+--------+
| NULL   | SMITH  |
| NULL   | ALLEN  |
| NULL   | WARD   |
| FORD   | JONES  |
| SCOTT  | JONES  |
| NULL   | MARTIN |
| JAMES  | BLAKE  |
| TURNER | BLAKE  |
| MARTIN | BLAKE  |
| WARD   | BLAKE  |
| ALLEN  | BLAKE  |
| MILLER | CLARK  |
| ADAMS  | SCOTT  |
| CLARK  | KING   |
| BLAKE  | KING   |
| JONES  | KING   |
| NULL   | TURNER |
| NULL   | ADAMS  |
| NULL   | JAMES  |
| SMITH  | FORD   |
| NULL   | MILLER |
+--------+--------+

 查询哪个部门没有员工。

注：主要的表是部门表，用right 使用where e.empno is null

select d.*
from emp e right join dept d
on e.deptno=d.deptno
where e.empno is null;

+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+

3 三张表连接 

查询每个员工的部门名称和工资等级。

注：使用emp表dept表先进行连接，再将emp表和salgrade表连接

select 
    e.ename,d.dname,s.grade
from 
    emp e 
join 
    dept d 
on 
    e.deptno=d.deptno
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal;

+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+

 查询每个员工的部门名称、工资等级和上级领导。

注：不能在第一个join进行左外连接

select e.ename '员工',d.dname,s.grade,e1.ename '领导'
from
    emp e
join
    dept d
on
    e.deptno=d.deptno
join
    salgrade s
on
    e.sal between s.losal and s.hisal
left join
    emp e1
on
    e.mgr = e1.empno;

+--------+------------+-------+-------+
| 员工   | dname      | grade | 领导  |
+--------+------------+-------+-------+
| SMITH  | RESEARCH   |     1 | FORD  |
| ALLEN  | SALES      |     3 | BLAKE |
| WARD   | SALES      |     2 | BLAKE |
| JONES  | RESEARCH   |     4 | KING  |
| MARTIN | SALES      |     2 | BLAKE |
| BLAKE  | SALES      |     4 | KING  |
| CLARK  | ACCOUNTING |     4 | KING  |
| SCOTT  | RESEARCH   |     4 | JONES |
| KING   | ACCOUNTING |     5 | NULL  |
| TURNER | SALES      |     3 | BLAKE |
| ADAMS  | RESEARCH   |     1 | SCOTT |
| JAMES  | SALES      |     1 | BLAKE |
| FORD   | RESEARCH   |     4 | JONES |
| MILLER | ACCOUNTING |     2 | CLARK |
+--------+------------+-------+-------+