MySQL查询语句学习之多表查询

前言

所有表均来自mytestdb数据库
可通过此链接免费下载或直接点击mytestdb下载

https://download.csdn.net/download/Onlylong1/85747137?spm=1001.2014.3001.5503

---

1. 等值连接与不等值连接

①等值连接

查询员工的id与名字及其对应的部门id与部门名

SELECT emp.employee_id, emp.last_name, dep.department_id, dep.department_name
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id
ORDER BY dep.department_id DESC;

查询结果:

②不等值连接

查询员工名字与薪资及其对应的工作等级

SELECT e.last_name,e.salary,j.grade_level
FROM employees e, job_grades j
# WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
ORDER BY salary ASC, LENGTH(e.last_name);

查询结果：

2. 自连接与非自连接

①自连接

查询员工id与名字及其管理者id与名字

SELECT emp.employee_id, emp.last_name, mgr.employee_id,mgr.last_name
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id;

查询结果：

②非自连接

除自连接外的查询都叫非自连接,即前面所演示的等值连接与不等值连接均为非自连接

3. 内连接与外连接

①内连接

合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行

查询员工的名字及其部门名及其所在部门所在城市名

SELECT last_name, department_name, city
#INNER 可省略
FROM employees emp INNER JOIN departments dep 
ON emp.department_id = dep.department_id
JOIN locations loc
ON dep.location_id = loc.location_id;

查询结果:

②外连接

合并具有同一列的两个以上的表的行,结果集中除了包含一个表与另一个表匹配的行之外,还查询到了左表 或 右表 中不匹配的行

外连接分为左外连接、右外连接、满外连接

左外连接：

查询所有的员工的last_name,department_name信息

SELECT last_name, department_name
# OUTER 可以省略
FROM employees emp LEFT OUTER JOIN departments dep
ON emp.department_id = dep.department_id;

查询结果:

右外连接：

查询所有的员工的last_name,department_name信息

SELECT last_name, department_name
# OUTER 可以省略
FROM employees emp RIGHT OUTER JOIN departments dep
ON emp.department_id = dep.department_id;

查询结果：

满外连接(全外连接)：

查询所有的员工的last_name,department_name信息

# MySQL 不支持 FULL OUTTER JOIN
SELECT last_name, department_name
# OUTER 可以省略
FROM employees emp FULL OUTER JOIN departments dep
ON emp.department_id = dep.department_id;

MySQL要实现满外连接(全外连接)要引入UNION或UNION ALL

其作用是操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中

区别:

UNION：会去重

UNION ALL: 不会去重，效率比UNION高(能使用UNION ALL就不要使用UNION)

MySQL实现满外连接

1.使用左连接(查出表employees与表departments公共部分及表employees特有部分)：

SELECT emp.last_name, dep.department_name
FROM employees emp LEFT OUTER JOIN departments dep
ON emp.department_id = dep.department_id;

2.再使用右外连接(查询表departments的特有部分)

SELECT emp.last_name, dep.department_name
FROM employees emp RIGHT OUTER JOIN departments dep
ON emp.department_id = dep.department_id
WHERE emp.department_id IS NULL;

3.最后使用UNION ALL连接两部分

其实只需要获取到表employees与表department的公共部分及两表特有部分，再将其使用UNION或UNION ALL即可

SELECT emp.last_name, dep.department_name
FROM employees emp LEFT OUTER JOIN departments dep
ON emp.department_id = dep.department_id

UNION ALL

SELECT emp.last_name, dep.department_name
FROM employees emp RIGHT OUTER JOIN departments dep
ON emp.department_id = dep.department_id
WHERE emp.department_id IS NULL;

查询结果：