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;