Mysql连接查询

一、内连接查询

注意：内连接强调的的是所连接表之间的某些行存在匹配关系，若是一个表的一些行在另外的表中找不带匹配关系，那么就会被直接过滤掉，它不像后面的外连接那样多个表之间存在主，副表之分。即使主表的某些行在副表中找不到匹配行，副表也会补足NULL进行填充。

1、等值连接

连接条件存在等值匹配的表连接查询：

查看每个员工的部门名称：

mysql> select e.ename , e.deptno ,d.dname  from emp e join dept d  on e.deptno = d.deptno;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+
14 rows in set (0.00 sec)

推荐使用Join on方法进行表连接，一方面增强了SQL语句的可读性，同时也把WHERE子句从表连接条件置零解放出来，上面的where子句充当了连接条件的声明，若是后面再有过滤条件的话，就必须使用and联结where条件。而使用ON声明联结条件的时候，后面可以直接跟where条件

mysql> select e.ename , e.deptno , d.dname 
    -> from emp e
    -> join dept d
    -> on e.deptno = d.deptno;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+
14 rows in set (0.00 sec)

2、自连接查询

上面的是多个表之间存在行匹配关系，要是一个表本身的某些列之间存在匹配关系，那么我们也可以把一张表看作两张表来进行看待，这里就看到了之前所说的表别名的用处：同一个表可以通过表别名的方式在一个select语句中使用多次；

如下表中员工的上级编号（MGR）和员工的编号（EMPNO）是相匹配的，除了员工KING的上级员工编号是NULL；

+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 a.ename '员工' , b.ename '上司' 
       from emp a  
       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  |
+--------+--------+
13 rows in set (0.00 sec)

注意：

这里的KING的上司并没有被查询出来，因为他的上司是NULL在emp表中没有找到匹配行，所以被过滤掉了，这里仔细看一下上面一开始就说的内外联结查询的区别。

二、外连接查询

上面为了查询每个员工的上司，我们使用了内连接查询，但是也发现了一个问题，就是KING的上司并没有被显示出来，因为在EMP表中找不到员工编号为NULL的员工。所以内连接查询并没有满足我们的需求，这里就提出了外连接查询。

外连接查询是有主副表之分的，要求主表的所有行都被检索和显示出来，即使副表中没有与之匹配的行，也要用NULL值填充。

同样是查看每个员工的上司：

mysql> select a.ename '员工' , 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  |
+--------+--------+
14 rows in set (0.00 sec)

分析：

这里在JOIN关键字的前面我们加上了一个LEFT表明左边的那个表是主表，右边的表为副表，所以这里是左连接查询

当然也可以改为右链接查询：

mysql> select a.ename '员工' , b.ename '上司'
    -> from emp b 
    -> right join emp a
    -> on a.mgr = b.empno;

这里我们看到KING的上司也被显示出来了

三、内连接和外连接的区别

语法区别：

• 两者最大的区别就是JOIN关键是前面是否加上了区分主副表关系的关键字LEFT/RIGHT，当然了，有的地方为了增强SQL的可读性，会进一步添加INNER（内连接）和OUTER（外连接）进行区分

功能区别：

• 外连接的查询结果包含了关联表之间没有被关联的行，缺失值用NULL填充。