第七课 连接查询（SQL92）

• 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
• 笛卡尔积现象：两个表中的所有数据实现无条件的两两连接，例如A表有2条数据，B表有4条数据，两表联查后的结果出现2*4=8条数据时，出现笛卡尔积错误
• 分类：
  • 按年代分类：
    • SQL92标准：仅仅支持内连接
    • SQL99标准（推荐）：支持内连接 + 外连接（左外和右外）+ 交叉连接
  • 按功能分类：
    • 内连接
      • 等值连接
      • 非等值链接
      • 自连接
    • 外连接
      • 左外连接
      • 右外连接
      • 全外连接
    • 交叉连接

1 等值连接

• 为表起别名
  • 提高语句的简洁度
  • 区分多个重名的字段
  • 注：如果为表起别名，则查询的字段就不能使用原来的表名去限定
• 等值连接
  • 多表等值连接的结果为多表的交集部分
  • N个表连接至少需要 N-1 个连接条件
  • 多表顺序没有要求
  • 一般为表起别名
  • 可以搭配之前所有的子句使用，包括排序、分组、筛选

# 等值连接
# 1 查询员工名对应的部门名
SELECT
	emp.last_name,
	dep.department_name
FROM
	employees emp,
	departments dep
WHERE
	emp.department_id = dep.department_id;

# 2 查询员工名、工种名、工种号
SELECT
	emp.last_name,
	job.job_title,
	job.job_id
FROM
	jobs job,
	employees emp
WHERE
	job.job_id = emp.job_id # 3 查询有奖金的员工名和部门名
	SELECT
		emp.last_name,
		dep.department_name
	FROM
		employees emp,
		departments dep
	WHERE
		emp.department_id = dep.department_id
	AND emp.commission_pct IS NOT NULL;

# 4 查询城市名中第二个字符是‘O’的部门
SELECT
	dep.department_name,
	loc.city
FROM
	locations loc,
	departments dep
WHERE
	loc.location_id = dep.location_id
AND loc.city LIKE '_o%';

# 5 查询每个城市的部门个数
SELECT
	COUNT(1),
	loc.city
FROM
	locations loc,
	departments dep
WHERE
	loc.location_id = dep.location_id
GROUP BY
	loc.city;

# 6 查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECT
	dep.department_name,
	dep.manager_id,
	MIN(emp.salary)
FROM
	employees emp,
	departments dep
WHERE
	emp.department_id = dep.department_id
AND emp.commission_pct IS NOT NULL
GROUP BY
	emp.department_id;

# 7 查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT
	job.job_title,
	COUNT(*)
FROM
	jobs job,
	employees emp
WHERE
	job.job_id = emp.job_id
GROUP BY
	emp.job_id
ORDER BY
	COUNT(1) DESC;

# 8 查询员工名、部门名和所在的城市
SELECT
	emp.last_name,
	dep.department_name,
	loc.city
FROM
	employees emp,
	departments dep,
	locations loc
WHERE
	emp.department_id = dep.department_id
AND dep.location_id = loc.location_id;

2 非等值连接

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

3 自连接

# 自连接
# 1 查询员工名和上级领导名称
SELECT
	emp.last_name emp,
	man.last_name man
FROM
	employees emp,
	employees man
WHERE
	emp.manager_id = man.employee_id;