引言
实际业务开发中,往往会涉及到多张表之间的数据交互,这时候单表查询已经不能满足复杂的业务需求了,所以就需要用到多表的连接查询;
连接查询主要分为以下三种:
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全连接
其中常用的就是内连接和外连接,全连接用处很少,几乎不用,所以这里就不介绍了;
数据库表传送门
下面示例将会用到三张表:
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 条件三......
实际业务会很复杂,所以要拆分开后再找表之间的关系,一步一步分析才行;
具体什么时候使用内连接什么时候使用外连接呢?只需要由外键区分即可:
- 如果外键不能为空,优先使用内连接;
- 如果外键可以为空:
- 假如只需要查询那些在另一张表中有相对应的记录,使用内连接;
- 假如需要查询左(右)侧表中所有符合条件的记录,使用左(右)外连接;
注意:外键能不能为空是由数据库表设计时决定的,因为外键只是数据库表的一个字段,所以需要根据实际情况分析;