06 多表查询

1 多表查询分类讲解

1.1 等值连接 vs 非等值连接

1.1.1 等值连接

(1) 区分重复的列名

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

(2) 表的别名

SELECT e.last_name, d.department_name, e.department_id
FROM employees e, departments d
WHERE e.employee_id = d.department_id;

(3) 连接多个表

● 连接n个表，至少需要n-1个连接条件。

【案例】查询出公司员工的 last_name,department_name, city

SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

1.1.2 非等值连接

【案例】EPLOYEES表中的列工资在JOB_GRADES表中的最低与最高工资之间。

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;

1.2 自连接 vs 非自连接

自连接

● 一张表，用别名的方式，进行连接

【案例】查询eployees表，返回“ A works for B ”

SELECT CONCAT(e1.last_name,'works for', e2.last_name)
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id;

1.3 内连接 vs 外连接

 

3 SQL99语法实现多表查询

3.1 JOIN ... ON

基本语法：

SELECT table1.column, table2.column,table3.column
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件

说明：

●可使用ON子句指定额外连接条件

●这个连接条件可与其它条件分开

●关键字JOIN、INNER JOIN、CROSS JOIN都表示内连接。

3.2 内连接（INNER JOIN）

【案例１】

SELECT e.employee_id, e.last_name, e.department_id, 
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

【案例２】

SELECT employee_id, city, department_name
FROM   employees e 
JOIN   departments d
ON     d.department_id = e.department_id 
JOIN   locations l
ON     d.location_id = l.location_id;

3.3 外连接（OUTER JOIN）

(1) 左外连接（LEFT OUTER JOIN）

【案例】

# 查询结果是 A
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

(2) 右外连接（RIGHT OUTER JOIN）

【案例】

# 查询结果是 B
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

(3) 满外连接（FULL OUTER JOIN）

 

4 UNION的使用

4.1 UNION操作符 — 并集，去重

4.2 UNION操作符 — 并集，不去重

【案例】查询部门编号>90或邮箱包含a的员工信息

# 方式一
SELECT * 
FROM employees 
WHERE email LIKE '%a%' OR department_id>90;

# 方式二
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;

【案例】查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

注意：

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