MySql连接查询——内连接与外连接

最新推荐文章于 2024-02-21 23:58:04 发布

YXXYX

最新推荐文章于 2024-02-21 23:58:04 发布

阅读量3.2k

点赞数 17

分类专栏：数据库文章标签： java 数据库 sql mysql database

本文链接：https://blog.csdn.net/YXXXYX/article/details/123123425

版权

数据库专栏收录该内容

4 篇文章 2 订阅

订阅专栏

引言

实际业务开发中，往往会涉及到多张表之间的数据交互，这时候单表查询已经不能满足复杂的业务需求了，所以就需要用到多表的连接查询；

连接查询主要分为以下三种：

内连接
- 等值连接
- 非等值连接
- 自连接
外连接
- 左外连接
- 右外连接
全连接

其中常用的就是内连接和外连接，全连接用处很少，几乎不用，所以这里就不介绍了；

数据库表传送门

下面示例将会用到三张表：

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

dept部门表：

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

salgrade工资等级表；

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

笛卡尔积现象

相信很多人都听过这个概念，这是一个数学概念，大致意思是：

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，
表示为 X * Y ，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

在数据库中集合其实就是表，所以数据库中笛卡尔积现象可以这样解释：

笛卡尔积，又叫cross join，是SQL中两表连接的一种方式。 
假如A表中的数据为m行，B表中的数据有n行，那么A和B做笛卡尔积，结果为 m * n 行

现在想要查询到每一个员工(ENAME)对应的部门名称(DNAME)，那么就涉及到emp和dept两张表之间的查询，假如写了如下sql：

SELECT 
	e.ENAME, d.DNAME 
FROM 
	emp e, dept d;

查询结果为：

+--------+------------+
| ENAME  | DNAME      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
...............等等一共56行
56 rows in set (0.00 sec)

可以发现查询结果并不符合要求，实际结果是emp表中每一行的数据都和dept表中所有数据进行了匹配，所以最后总行数为：

emp表行数（14）* dept表行数（4）

这就是笛卡尔积现象，出现这个现象的原因很简单，因为两个表的连接没有设置连接条件，所以没有连接条件的表关系返回的结果为笛卡尔积；

为了避免这个现象，可以使用WHERE子句建立连接关系：

SELECT 
	e.ENAME, d.DNAME 
FROM 
	emp e, dept d 
WHERE 
	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 |
+--------+------------+
14 rows in set (0.00 sec)

这个结果也就是我们需要的结果了；

但是记住一点：虽然使用WHERE子句可以避免笛卡尔积现象，但是在查询过程中并不会减少匹配次数，只不过是把有效数据显示了出来；

（意思是：使用了WHERE子句后，查询结果虽然只有14行，但是在查询过程中实际上是匹配了56次）

内连接

等值连接

上面使用的连接方法就是内连接中的等值连接，所以内连接是基于两个表之间的相等的测试，通俗来说就是两个表间的条件是等量关系；

但是上面的那种语法已经过时了，那是92语法，现在一般使用的是99语法，一般需要使用INNER JOIN......ON连接两个表，所以上面的sql可以写为：

SELECT 
	e.ENAME, d.DNAME 
FROM 
	emp e 
INNER JOIN 
	dept d 
ON 
	e.DEPTNO=d.DEPTNO;

结果是一样的；这里的INNER可以不写，默认就是内连接，但是写上可读性更好；

条件判断这里使用ON来判断，传递给ON的条件和传递给WHERE的条件是相同的；而且ON的后面还可以加WHERE判断语句：a JOIN b ON 条件1 WHERE 条件2

非等值连接

非等值连接主要特点就是：连接条件中的关系是非等量关系；

举个例子：找出每个员工的工资等级，要求显示员工名、工资、工资等级

这需要用到emp表和salgrade表，非常简单，只需要使用内连接连接这两个表，查询出在salgrade表中工资大于最低工资小于最高工资的等级即可；

SELECT
	e.ENAME,e.SAL,s.GRADE
FROM
	emp e
INNER 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 |
+--------+---------+-------+
14 rows in set (0.00 sec)

自连接

自连接有点特殊，它是基于一张表的查询，但是却把这一张表当成两张表来用；顾名思义就是自己连接自己；

下面用一个例子说明：找出每个员工的上级领导，要求显示员工名和对应的领导名

在员工表emp中每一个员工都有对应的上级（除了KING员工），所以可以把这一张表按不同的意思看成两张表：

员工表和上级表，看成哪个表那么其他部分就忽略不要在管了；

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 |
+--------+-------+
13 rows in set (0.01 sec)

外连接

内连接在将一个表中的一行与另一个表中的行相关联时，会略过为空的行，就比如上面找员工领导的例子，KING是最大的领导，所以他没有领导，查询出来也没有它的数据，那么如何能带上它的数据呢？这就需要用到外连接；

外连接分为左外连接和右外连接，但是这两种连接方式并没有太大的区别，下面就一块来示范一下：

还是上面找员工领导的例子，这次额外要求把没有领导的员工也输出：

SELECT
	a.ENAME AS '员工',b.ENAME AS '领导'
FROM
	emp a
LEFT JOIN
	emp b
ON
	a.MGR = b.EMPNO;

可以看到这里不再使用INNER JOIN内连接，而是换成了LEFT JOIN外连接，（等价于LEFT OUTER JOIN，OUTER可以忽略），意思是把LEFT JOIN左边的表作为主表，右边的表作为副表，这样的结果就是主表的所有内容都会查出来，而副表的内容依靠于主表内容；

查询结果：

+--------+-------+
| 员工   | 领导  |
+--------+-------+
| 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)

可以看到副表没有的内容（KING）自动生成了NULL；

这就是左外连接，同样 右外连接(RIGHT JOIN) 就是右边的是主表，左边的是副表，那么这个查询也可以这样写：

SELECT
	a.ENAME AS '员工',b.ENAME AS '领导'
FROM
	emp b
RIGHT JOIN
	emp a
ON
	a.MGR = b.EMPNO;

简单总结一下：

主表在查询时占主导地位，主表的内容一定可以查出来；
副表在查询时依附主表，若主表内容副表对应没有，则生成NULL；

总结

外连接有主表和副表之分，而内连接没有主副之分

在示例中只设计两张表的连接，而在连接多张表查询时也一样，比如 表一 JOIN 表二 ON 条件一 JOIN 表三 ON 条件二 JOIN 表四 ON 条件三......

实际业务会很复杂，所以要拆分开后再找表之间的关系，一步一步分析才行；

具体什么时候使用内连接什么时候使用外连接呢？只需要由外键区分即可：

如果外键不能为空，优先使用内连接；
如果外键可以为空：
- 假如只需要查询那些在另一张表中有相对应的记录，使用内连接；
- 假如需要查询左（右）侧表中所有符合条件的记录，使用左（右）外连接；

注意：外键能不能为空是由数据库表设计时决定的，因为外键只是数据库表的一个字段，所以需要根据实际情况分析；

YXXYX

关注

17
点赞
踩
29

收藏

觉得还不错? 一键收藏
打赏
13
评论
MySql连接查询——内连接与外连接

引言实际业务开发中，往往会涉及到多张表之间的数据交互，这时候单表查询已经不能满足复杂的业务需求了，所以就需要用到多表的连接查询；连接查询主要分为以下三种：内连接等值连接非等值连接自连接外连接左外连接右外连接全连接其中常用的就是内连接和外连接，全连接用处很少，几乎不用，所以这里就不介绍了；数据库表传送门下面示例将会用到三张表：emp员工表+-------+--------+-----------+------+------------+---------+----
复制链接

扫一扫