SQL 多表连接查询

SQL 多表连接查询学习笔记

多表连接查询是 SQL 中用于从多个表中提取相关数据的强大工具。通过连接查询，可以将多个表中的数据组合在一起，生成更复杂的查询结果。

1. 连接查询的类型

SQL 支持多种连接查询类型，主要包括：

（1）笛卡尔积（Cross Join）

• 定义：两张表的每行数据都与另一张表的所有行数据匹配，结果行数为 m * n。
• 示例：

  -- 笛卡尔积
  SELECT d1.*, d2.*, t.*
  FROM tmp_emp t, dept d1, dept d2;
  
  -- SQL 1999 标准（交叉连接）
  SELECT * FROM tmp_emp CROSS JOIN dept;

（2）自然连接（Natural Join）

• 定义：自动匹配两张表中列名和类型相同的列，并去掉重复列。
• 示例：

  -- 自然连接
  SELECT * FROM tmp_emp t NATURAL JOIN dept d;

（3）内连接（Inner Join）

• 定义：根据指定的条件匹配两张表中的数据，只返回匹配成功的行。
• 示例：

  -- 内连接（使用 ON 子句）
  SELECT * FROM tmp_emp t INNER JOIN dept d ON t.deptno = d.deptno;
  
  -- 内连接（使用 USING 子句）
  SELECT * FROM tmp_emp t INNER JOIN dept d USING(deptno);

（4）外连接（Outer Join）

• 左外连接（Left Join）：返回左表的所有行，右表没有匹配的行显示为 NULL。

  -- 左外连接
  SELECT * FROM tmp_emp t LEFT JOIN dept d ON t.deptno = d.deptno;

• 右外连接（Right Join）：返回右表的所有行，左表没有匹配的行显示为 NULL。

  -- 右外连接
  SELECT * FROM tmp_emp t RIGHT JOIN dept d ON t.deptno = d.deptno;

• 全外连接（Full Join）：返回左右表的所有行，没有匹配的行显示为 NULL。

  -- 全外连接
  SELECT * FROM tmp_emp t FULL JOIN dept d ON t.deptno = d.deptno;

（5）自连接（Self Join）

• 定义：同一张表自己连接自己，通常用于查询层级关系（如员工和领导）。
• 示例：

  -- 自连接
  SELECT t1.empno, t1.ename, t1.mgr, t2.empno, t2.ename
  FROM tmp_emp t1, tmp_emp t2
  WHERE t1.mgr = t2.empno;
  
  -- SQL 1999 标准写法
  SELECT t1.empno, t1.ename, t1.mgr, t2.empno, t2.ename
  FROM tmp_emp t1 JOIN tmp_emp t2 ON t1.mgr = t2.empno;

2. 综合示例

以下是一些综合示例，展示了多表连接查询的实际应用。

（1）查询员工的编号、姓名、部门编号、部门名称

SELECT empno, ename, tmp_emp.deptno, dname
FROM tmp_emp, dept
WHERE tmp_emp.deptno = dept.deptno;

（2）查询每个雇员的姓名、工资、部门名称、工资等级及其领导的姓名及其工资等级

SELECT t1.ename, t1.sal, d1.dname, s1.grade, t2.ename, s2.grade
FROM tmp_emp t1
JOIN dept d1 ON t1.deptno = d1.deptno
JOIN salgrade s1 ON t1.sal BETWEEN s1.losal AND s1.hisal
LEFT JOIN tmp_emp t2 ON t1.mgr = t2.empno
LEFT JOIN salgrade s2 ON t2.sal BETWEEN s2.losal AND s2.hisal;

3. 连接查询的注意事项

1. 避免笛卡尔积：

   • 笛卡尔积会导致结果集过大，通常需要明确指定连接条件。

2. 选择合适的连接类型：

   • 根据业务需求选择内连接、左外连接、右外连接或全外连接。

3. 性能优化：

   • 多表连接查询可能会影响性能，建议在连接列上创建索引。

4. 别名使用：

   • 使用表别名可以简化 SQL 语句，提高可读性。

4. 总结

1. 连接查询的类型：笛卡尔积、自然连接、内连接、外连接和自连接。
2. 连接查询的语法：ON 子句、USING 子句和 NATURAL JOIN 的使用方法。