05. 多表查询

2023-11-06 21:33:52

一、笛卡尔积（或交叉连接）

  笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。SQL 92中，笛卡尔积也称为交叉连接，英文是 CROSS JOIN。在 SQL 99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

笛卡尔积的错误会在下面条件下产生

  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接

二、关联查询

  • 前提条件：
    • 这些一起查询的表之间是有联系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。
  • 等值连接 VS 非等值连接
    • 等值连接：
      • SELECT 字段名 FROM 表名1,表名2 WHERE 表名1.字段名1 = 表名2.字段名1;
    • 非等值连接：
      • SELECT 字段名 FROM 表名1,表名2 WHERE 非等值连接条件;
  • 自连接 VS 非自连接
    • 自连接：
      • SELECT 字段名 FROM 表名1 别名1,表名1 别名2 WHERE 连接条件;
    • 非自连接：
      • SELECT 字段名 FROM 表名1,表名2 WHERE 连接条件;
  • 内连接 VS 外连接
    • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
      • SELECT 字段名 FROM 表名1,表名2 WHERE 连接条件;                  -- SQL92
      • SELECT 字段名 FROM 表名1 [INNER] JOIN 表名2 ON 连接条件;           -- SQL99
    • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（右）表中不满足条件的行，这种方式称为左（右）外连接，没有匹配的行时，结果表中相应的列为空（NULL）
      • 左外连接：连接条件中左边的表称为主表，右边的表称为从表
        • SELECT 字段名 FROM 表名1,表名2 WHERE 表名1.字段名1 = 表名2.字段名1(+);  -- SQL92，MySQL不支持
        • SELECT 字段名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 连接条件;       -- SQL99
      • 右外连接：连接条件中右边的表称为主表，左边的表称为从表
        • SELECT 字段名 FROM 表名1,表名2 WHERE 表名1.字段名1(+) = 表名2.字段名1;  -- SQL92，MySQL不支持
        • SELECT 字段名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 连接条件;       -- SQL99
      • 满外连接：结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
        • SELECT 字段名 FROM 表名1 FULL [OUTER] JOIN 表名2 ON 连接条件;       -- SQL99,MySQL不支持FULL
  • 在表中有相同列时，在列名之前加上表名前缀。
  • 如果给表起了别名后，一旦在SELECT或WHERE中使用别名的话，则必须使用表的别名，而不能使用表的原名
  • 如果有n个表实现多表的查询，则至少需要n-1个连接条件
-- 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
-- 从sql优化的角度，建议多表查询是，每个字段前都指明其所在的表
SELECT employees.employee_id,departments.department_name,employees.department_id 
FROM employees,departments
WHERE employees.department_id = departments.department_id;

-- 如果给表起了别名，一但在SELECT和WHERE中使用表名的话，则必须使用表的别名，不能使用表的原名
SELECT emp.employee_id,dept.department_name,emp.department_id 
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;

-- 如果有n个表实现多表查询，则至少需要n-1个连接条件
SELECT e.employee_id,e.last_name,d.department_name,e.department_id,l.city,l.location_id
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

-- 等值连接
SELECT emp.employee_id,dept.department_name,emp.department_id 
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;

-- 非等值连接
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

-- 自连接
SELECT emp.employee_id AS emp_id,emp.last_name AS emp_name,mgr.employee_id AS mgr_id,mgr.last_name AS mgr_name
FROM employees emp,employees mgr
WHERE emp.manager_id = mgr.employee_id;

-- 非自连接
SELECT emp.employee_id,dept.department_name,emp.department_id 
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;

-- 内连接
-- SQL92语法
SELECT emp.employee_id,dept.department_name,emp.department_id 
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;
-- SQL92语法实现内连接：JOIN...ON
SELECT last_name,department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;

-- 外连接
-- SQL92语法：使用`+`创建左外连接，MySQL不支持SQL92语法中的外连接的写法
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+);
-- SQL92语法：使用`+`创建右外连接，MySQL不支持SQL92语法中的外连接的写法
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id(+) = d.department_id;
-- SQL99语法使用JOIN...ON的方式是是实现左外连接的方式
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
-- SQL99语法使用JOIN...ON的方式是是实现右外连接的方式
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;
-- SQL99语法使用JOIN...ON的方式是是实现右外连接的方式，MySQL不支持SQL99的满外连接
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

三、UNINO的使用

  UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相会对应。各个SELECT语句之间使用UNION或UNION ALL关键字分分隔。UNION操作符返回两个查询的结果集的并集，去除重复记录。UNION ALL操作符返回两个查询的结果集的并集，对于两个结果集的重复部分，不去重。

语法格式：

SELECT 字段名1,字段名2,...  
FROM 表名1 
UNION
SELECT 字段名1,字段名2,... 
FROM 表名2;

SELECT 字段名1,字段名2,...  
FROM 表名1 
UNION ALL
SELECT 字段名1,字段名2,... 
FROM 表名2;

SELECT * FROM employees WHERE email LIKE '%a%'
UNION 
SELECT * FROM employees WHERE department_id > 90;

SELECT * FROM employees WHERE email LIKE '%a%'
UNION ALL
SELECT * FROM employees WHERE department_id > 90;

注意：执行UNION ALL语句时所需要的资源比UNINO语句。如果明确知道合并数据后的结果不存在重复数据，或者不需要去除重复的数据，则尽量使用UNINO ALL语句，以提高数据查询的效率。

四、7种SQL JOIN的实现

05. 多表查询

-- 中图：内连接
SELECT 字段名 
FROM 表名1 [INNER] JOIN 表名2 
ON 表名1.字段名1 = 表名2.字段名1;

-- 左上图：左外连接
SELECT 字段名 
FROM 表名1 LEFT [OUTER] JOIN 表名2 
ON 表名1.字段名1 = 表名2.字段名1;

-- 右上图：右外连接
SELECT 字段名 
FROM 表名1 RIGHT [OUTER] JOIN 表名2 
ON 表名1.字段名1 = 表名2.字段名1;

-- 左中图
SELECT 字段名 
FROM 表名1 LEFT [OUTER] JOIN 表名2 
ON 表名1.字段名1 = 表名2.字段名1 
WHERE 表名2.字段名1 IS NULL;

-- 右中图
SELECT 字段名 
FROM 表名1 RIGHT [OUTER] JOIN 表名2 
ON 表名1.字段名1 = 表名2.字段名1 
WHERE 表名1.字段名1 IS NULL;

-- 左下图：满外连接
-- 方式一：左上图 UNION ALL 右中图
SELECT 字段名1,字段名2,... 
FROM  表名1 LEFT [OUTER] JOIN 表名2 
ON 表名1.字段名3 = 表名2.字段名3
UNION ALL
SELECT 字段名1,字段名2,... 
FROM 表名1 RIGHT [OUTER] JOIN 表名2 
ON 表名1.字段名3 = 表名2.字段名3 
WHERE 表名1.字段名3 IS NULL;
-- 方式二：左中图 UNION ALL右上图
SELECT 字段名1,字段名2,... 
FROM 表名1 LEFT [OUTER] JOIN 表名2 
ON 表名1.字段名3 = 表名2.字段名3
WHERE 表名2.字段名3 IS NULL
UNION ALL
SELECT 字段名1,字段名2,... 
FROM 表名1 RIGHT [OUTER] JOIN 表名2 
ON 表名1.字段名3 = 表名2.字段名3 

-- 右下图：左中图 UNION ALL 右中图
SELECT 字段名1,字段名2,... 
FROM 表名1 LEFT [OUTER] JOIN 表名2 
ON 表名1.字段名3 = 表名2.字段名3
WHERE 表名2.字段名3 IS NULL
UNION ALL
SELECT 字段名1,字段名2,... 
FROM 表名1 RIGHT [OUTER] JOIN 表名2 
ON 表名1.字段名3 = 表名2.字段名3 
WHERE 表名1.字段名3 IS NULL;

-- 中图
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

-- 左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

-- 右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

-- 左中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

-- 右中图
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

-- 左下图：满外连接
-- 方式1：左上图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
-- 方式2：左中图 UNION ALL 右上图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

-- 右下图：左中图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

五、SQL99语法的新特性

5.1、自然连接

  NATURAL JOIN 用来表示自然连接，它会自动帮你查询两张连接表中的 所有相同的字段 ，然会进行 等值连接。

语法：

SELECT 字段名
FROM 表名1 NATURAL JOIN 表名2;

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments;

5.2、USING连接

  SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接，但是只能配合JOIN一起使用。USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。

语法：

SELECT 字段名 
FROM 表名1 JOIN 表名2
USING (共同的字段名);

SELECT employee_id,last_name,department_name
FROM employees JOIN departments
USING (department_id);

六、练习题

-- 1.显示所有员工的姓名，部门号和部门名称。 
SELECT last_name,d.department_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

-- 2.查询90号部门员工的job_id和90号部门的location_id 
SELECT job_id,location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id = 90;

-- 3.选择所有有奖金的员工的 last_name , department_name , location_id , city 
SELECT last_name,department_name,l.location_id,city
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE commission_pct IS NOT NULL;

-- 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 
SELECT last_name,job_id,d.department_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE city = 'Toronto';

-- 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’ 
SELECT department_name,street_address,last_name,e.job_id,salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id 
JOIN locations l
ON d.location_id = l.location_id 
WHERE department_name = 'Executive';

-- 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式 
-- employees	Emp#	manager		Mgr#  
-- kochhar 	101 	king 		100 
SELECT emp.last_name AS employess,emp.employee_id AS "Emp#",mgr.last_name AS manager,mgr.employee_id AS "Mgr#"
FROM employees emp LEFT JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

-- 7.查询哪些部门没有员工 
SELECT department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

-- 8. 查询哪个城市没有部门 
SELECT city
FROM locations l LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.location_id IS NULL;

-- 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT employee_id,last_name,salary,e.department_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE department_name IN ('Sales','IT');
上一篇：MySQL基础篇——多表查询


下一篇：DMSQL语法学习记录