SQL连接详解与应用

连接查询

1. 什么是连接查询
关系型数据库中，表与表之间存在关联关系。为了获取多张表中的数据，通常需要使用连接查询。这种多表联合获取数据的方式称为连接查询。
在实际开发中，通过单表获取数据的情况较少见。

2. 连接查询的分类
- 按照连接方式分类
* 内连接（INNER JOIN）
内连接查询两张表中完全匹配的记录。常见的内连接类型包括等值连接、非等值连接和自连接。
* 外连接（OUTER JOIN）
外连接在内连接的基础上，显示一张表中的所有记录，即使另一张表中没有匹配的记录，也会用空值填充。外连接分为左外连接（LEFT OUTER JOIN）和右外连接（RIGHT OUTER JOIN）。
- 按照语法出现的年代分类
* SQL92标准
* SQL99标准

3. 如果不加连接条件会出现什么情况？
如果不加连接条件，查询结果将是笛卡尔积，即每张表中的每一行都会与其他表中的每一行组合。

4. 如何避免笛卡尔积？
在连接时加上适当的查询条件可以避免笛卡尔积。虽然这不会减少匹配的次数，但可以显著减少结果集的大小。

5. 案例：查询员工所在的部门，要求显示员工编号、员工姓名以及对应的部门编号和部门名称
涉及表：emp e（员工表），dept d（部门表）
- SQL92标准的等值连接
SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
- SQL99标准的等值连接（INNER可以省略）
SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
SQL92和SQL99的比较：
SQL92：连接条件和查询条件混合在一起，可读性较差。
SQL99：连接条件和查询条件分开，语句更清晰。

6. 案例：查询员工的工资对应的等级，要求显示员工的编号、员工姓名以及对应的工资等级
涉及表：emp e（员工表），salgrade s（工资等级表）
- SQL92标准的非等值连接
SELECT e.empno, e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
或
SELECT e.empno, e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal >= s.losal AND e.sal <= s.hisal;
- SQL99标准的非等值连接
SELECT e.empno, e.ename, e.sal, s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

7. 案例：查询员工对应的上级领导，要求显示员工的编号、员工姓名以及对应的上级领导的姓名
涉及表：emp e（员工表），emp m（领导表）
- SQL92标准的自连接
SELECT e.empno, e.ename, m.ename FROM emp e, emp m WHERE e.mgr = m.empno;
- SQL99标准的自连接
SELECT e.empno, e.ename, m.ename FROM emp e JOIN emp m ON e.mgr = m.empno;

8. 案例：查询员工所在的部门，要求显示员工编号、员工姓名以及对应的部门编号和部门名称，显示所有的部门
涉及表：emp e（员工表），dept d（部门表）
任何左外连接都对应一个右外连接。
- SQL99标准的左外连接
SELECT e.empno, e.ename, d.deptno, d.dname FROM dept d LEFT JOIN emp e ON e.deptno = d.deptno;
- SQL99标准的右外连接
SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;
- SQL92标准的左外连接
SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e, dept d WHERE d.deptno = e.deptno(+);
- SQL92标准的右外连接
SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e, dept d WHERE e.deptno(+) = d.deptno;

9. 案例：查询员工的上级领导，要求显示员工的编号、员工姓名以及对应的上级领导的姓名，显示所有员工
- SQL99标准的左外连接
SELECT e.empno, e.ename, NVL(m.ename, '这是Boss') FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;

10. 多张表如何进行连接查询？例如 a b c 表，a -> b 和 a -> c 的连接
以学生选课系统为例：
学生表 t_student s
id name
----------------------
1 张三
2 李四
3 王五
课程表 t_course c
id name
-----------------
100 C++
200 Java
300 Jsp
学生选课表 t_student_course_relation sc
sid cid （sid 是外键，关联学生表的主键；cid 也是外键，关联课程表的主键；sid + cid 是联合主键）
---------------------------------------------------
1 100
1 200
1 300
2 100
2 300
3 200
要求：编写查询语句，将2号学生选的课程查询出来，显示学生的姓名和课程名称
SELECT s.name, c.name FROM t_student_course_relation sc JOIN t_student s ON s.id = sc.sid JOIN t_course c ON c.id = sc.cid WHERE s.id = 2;