几种连接查询原理回顾
多表连接的结果通过三个属性决定:
- 方向性:在外连接中写在前边的表为左表、写在后边的表为右表。
- 主附关系:主表要出所有的数据范围,附表与主表无匹配项时标记为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 |