多表连接SQL知识点回顾

几种连接查询原理回顾

多表连接的结果通过三个属性决定：

• 方向性：在外连接中写在前边的表为左表、写在后边的表为右表。
• 主附关系：主表要出所有的数据范围，附表与主表无匹配项时标记为null，内连接时无主附表之
  分
• 对应关系：关键字段中有重复值的表为多表，没有重复值的表为一表。

对应关系：一对一、一对多或多对一、多对多
连接方式：
内连接：

select ename,job,hiredate,sal,dname
from emp
inner join dept
on emp.deptno=dept.deptno;
+--------+-----------+------------+------+------------+
| ename | job    | hiredate  | sal | dname   |
+--------+-----------+------------+------+------------+
| clark | manager  | 1981-06-09 | 2450 | accounting |
| king  | persident | 1981-11-17 | 5000 | accounting |
| miller | clerk   | 1982-01-23 | 1300 | accounting |
| smith | clerk   | 1980-12-17 |  800 | research  |
| jones | manager  | 1981-04-02 | 2975 | research  |
| scott | analyst  | 1987-04-19 | 3000 | research  |
| adams | clerk   | 1987-05-23 | 1100 | research  |
| ford  | analyst  | 1981-12-03 | 3000 | research  |
| allen | salesman | 1981-02-20 | 1600 | sales   |
| ward  | salesman | 1981-02-22 | 1250 | sales   |
| martin | salesman | 1981-09-28 | 1250 | sales   |
| blake | manager  | 1981-05-01 | 2850 | sales   |
| turner | salesman | 1981-09-08 | 1500 | sales   |
| james | clerk   | 1981-12-03 |  950 | sales   |
+--------+-----------+------------+------+------------+

左连接

select dept.deptno,dname,count(empid)
from dept
left join emp
on dept.deptno=emp.deptno
group by dept.deptno;
+--------+------------+--------------+
| deptno | dname   | count(empid) |
+--------+------------+--------------+
|   10 | accounting |       3 |
|   20 | research  |       5 |
|   30 | sales   |       6 |
|   40 | operations |       0 |
+--------+------------+--------------+

右连接按照连接条件，返回两张表中满足条件的记录，以及右表中的所有记录，左表匹配不到显
示为null。

笛卡尔积连接

两张表中的每一条记录进行笛卡尔积组合，然后根据where条件过滤虚拟结果集中
的记录。

select ename,job,hiredate,sal,dname
from emp,dept
where emp.deptno=dept.deptno;
+--------+-----------+------------+------+------------+
| ename | job    | hiredate  | sal | dname   |
+--------+-----------+------------+------+------------+
| clark | manager  | 1981-06-09 | 2450 | accounting |
| king  | persident | 1981-11-17 | 5000 | accounting |
| miller | clerk   | 1982-01-23 | 1300 | accounting |
| smith | clerk   | 1980-12-17 |  800 | research  |
| jones | manager  | 1981-04-02 | 2975 | research  |
| scott | analyst  | 1987-04-19 | 3000 | research  |
| adams | clerk   | 1987-05-23 | 1100 | research  |
| ford  | analyst  | 1981-12-03 | 3000 | research  |
| allen | salesman | 1981-02-20 | 1600 | sales   |
| ward  | salesman | 1981-02-22 | 1250 | sales   |
| martin | salesman | 1981-09-28 | 1250 | sales   |
| blake | manager  | 1981-05-01 | 2850 | sales   |
| turner | salesman | 1981-09-08 | 1500 | sales   |
| james | clerk   | 1981-12-03 |  950 | sales   |
+--------+-----------+------------+------+------------+

自连接：通过设置表别名，将一张表虚拟成多张表。

等值连接与非等值连接

上述都是等值连接
那么非等值连接则：
不等值连接：连接条件是两张表中的关键字段取值满足非等值比较运算

select ename,job,hiredate,sal,grade
from emp
inner join salgrade
on sal between losal and hisal;
+--------+-----------+------------+------+-------+
| ename | job    | hiredate  | sal | grade |
+--------+-----------+------------+------+-------+
| smith | clerk   | 1980-12-17 |  800 |   1 |
| allen | salesman | 1981-02-20 | 1600 |   3 |
| ward  | salesman | 1981-02-22 | 1250 |   2 |
| jones | manager  | 1981-04-02 | 2975 |   4 |
| martin | salesman | 1981-09-28 | 1250 |   2 |
| blake | manager  | 1981-05-01 | 2850 |   4 |
| clark | manager  | 1981-06-09 | 2450 |   4 |
| scott | analyst  | 1987-04-19 | 3000 |   4 |
| king  | persident | 1981-11-17 | 5000 |   5 |
| turner | salesman | 1981-09-08 | 1500 |   3 |
| adams | clerk   | 1987-05-23 | 1100 |   1 |
| james | clerk   | 1981-12-03 |  950 |   1 |
| ford  | analyst  | 1981-12-03 | 3000 |   4 |
| miller | clerk   | 1982-01-23 | 1300 |   2 |
+--------+-----------+------------+------+-------+

连接总结

一表作为主表可以保证维度的完整性，多表作为主表可以保证度量的准确性。
在没有明确表示需要保证维度完整性的情况下，优先保证度量的准确性，所以将度量值所在的表作
为主表。
度量字段通常存在于多表中，因此通常情况下可以将多表作为主表进行外连接。

• 确定查询的信息在哪几张表
• 确定表和表之间的对应关系和主附关系
• 确定表和表之间的连接条件