第八课 连接查询（SQL99）

• 语法：

SELECT 查询列表
FROM 表1 别名 【连接类型】
JOIN 表2 别名
ON 连接条件
【WHERE 筛选条件】
【GROUP BY 分组子句】
【HAVING 筛选条件】
【ORDER BY 排序】

• 连接类型
  • 内连接：INNER
  • 外连接：
    • 左外连接：LEFT OUTER
    • 右外连接：RIGHT OUTER
    • 全外连接：FULL OUTER
  • 交叉连接：CROSS

1 内连接

• 语法：

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件
...

• 分类
  • 等值连接
  • 非等值连接
  • 自连接

1.1 等值连接

• 特点
  • 可以添加排序、分组和筛选
  • INNER可以省略
  • 筛选条件放在WHERE后面，连接条件放在ON后面，提高分离性，便于阅读
  • INNER JOIN连接和SQL92语法中的等值连接效果是一样的，都是查询多表的交集

# 等值连接
# 1 查询员工名，部门名
SELECT
	emp.last_name,
	dep.department_name
FROM
	employees emp
INNER JOIN departments dep ON emp.department_id = dep.department_id;

# 2 查询名字中包含e的员工名和工种名
SELECT
	emp.last_name,
	job.job_title
FROM
	employees emp
INNER JOIN jobs job ON emp.job_id = job.job_id
WHERE
	emp.last_name LIKE '%e%';

# 3 查询部门个数>3的城市名和部门个数
SELECT
	COUNT(*),
	loc.city
FROM
	departments dep
INNER JOIN locations loc ON dep.location_id = loc.location_id
GROUP BY
	loc.city
HAVING
	COUNT(*) > 3;

# 4 查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序
SELECT
	COUNT(*),
	dep.department_name
FROM
	employees emp
INNER JOIN departments dep ON emp.department_id = dep.department_id
GROUP BY
	emp.department_id
HAVING
	COUNT(*) > 3
ORDER BY
	COUNT(*) DESC;

# 5 查询员工名、部门名、工种名、并按部门名降序
SELECT
	emp.last_name,
	dep.department_name,
	job.job_title
FROM
	employees emp
INNER JOIN departments dep ON emp.department_id = dep.department_id
INNER JOIN jobs job ON emp.job_id = job.job_id
ORDER BY
	dep.department_name DESC;

1.2 非等值连接

# 非等值连接
# 1 查询员工的工资级别
SELECT
	emp.last_name,
	emp.salary,
	gra.grade_level
FROM
	employees emp
INNER JOIN job_grades gra ON emp.salary BETWEEN gra.lowest_sal
AND gra.highest_sal;

1.3 自连接

# 自连接
# 1 查询员工的名字，上级的名字
SELECT
	emp.last_name,
	man.last_name
FROM
	employees emp
INNER JOIN employees man ON emp.manager_id = man.employee_id;

2 外连接

• 应用场景：用于查询一个表中有，另一个表中没有的记录
• 特点
  • 外连接的查询结果为主表的所有记录
  • 如果从表中有和它匹配的，则显示匹配的值
  • 如果从表中没有和它匹配的，则显示NULL
  • 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录
• 主表和从表
  • 左外连接：LEFT JOIN左边的是主表
  • 右外连接：RIGHT JOIN右边的是主表
• 左外连接和右外连接交换两个表的顺序，可以实现同样的效果
• 全外连接 = 内连接结果 + 表1中有但表2中没有的部分 + 表2中有但表1中没有的部分
• 交叉连接：两个表的笛卡尔积

# 外连接
# 1 查询哪个部门没有员工（左外连接）
SELECT dep.department_id
FROM departments dep
LEFT OUTER JOIN employees emp
ON emp.department_id = dep.department_id
WHERE emp.employee_id IS NULL;

# 2 查询哪个部门没有员工（右外连接）
SELECT dep.department_id
FROM employees emp
RIGHT OUTER JOIN departments dep
ON emp.department_id = dep.department_id
WHERE emp.employee_id IS NULL;

# 3 交叉连接
SELECT *
FROM employees emp
CROSS JOIN departments dep;

3 连接总结

• 图片填充！！！！！