MySQL学习笔记之连接查询(SQL99标准) inner 、left outer、right outer、full outer、cross等

前言：sql99语法中的连接查询

• 语法：

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

• 连接类型：

内连接：inner
外连接：
左外：left [outer]
右外：right [outer]
全外：full [outer]
交叉连接：
cross

一、内连接

• 语法：

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

• 细分：

​ 等值连接 非等值连接 自连接

• 特点：

① 可以添加排序、分组、筛选
② inner可以忽略
③ 筛选条件在where 之后 连接条件在on之后，提高分离性，便于阅读
④ inner join连接和sql 92语法中的等值连接效果是一样的，都是查询多表的交集

1.等值连接

案例1.查询员工名、部门名

SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

调换一下顺序后效果与案例1等价

SELECT last_name, department_name
FROM departments d
INNER JOIN employees e
ON e.`department_id` = d.`department_id`;

案例2.查询名字中包含有e的员工名和工种名(添加筛选)

SELECT last_name, job_title
FROM employees e
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
WHERE e.`last_name` LIKE '%e%';

案例3.查询部门个数大于3的城市名和部门个数(添加筛选和分组)

SELECT city, COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

案例4.查询员工的个数大于3的部门的名字和员工个数，并按照个数排序

SELECT department_name, COUNT(*) AS 员工人数
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_name`
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

案例5.查询员工名、部门名、工种名，并按照部门名降序

SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
ORDER BY d.department_name DESC;

2.非等值连接

案例6.查询员工的工资级别

SELECT COUNT(*),grade_level
FROM employees e
INNER JOIN job_grades j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY grade_level
HAVING	COUNT(*) > 20
ORDER BY grade_level DESC;

3.自连接

案例7.查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.last_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

二、外连接

1.外连接查询结果为主表的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和他匹配的，则显示null
外连接查询结果=内连接查询结果+主表有而从表中没有的记录

2.左外连接：left join左边的是主表
右外连接：right join右边的是主表

3.左外和右外交换两个表的顺序，可以实现同样的效果

左外连接和右外连接

案例8. 查询男友不在男生表的女生名

SELECT NAME
FROM beauty g
LEFT OUTER JOIN boys bo
ON g.`boyfriend_id` = bo.`id`   #匹配过程中，如果匹配不到，那么就设置为null
WHERE bo.`id` IS NULL;			#筛选条件最好用主键，因为主键在原始从表中必然不为空
								#如果有null那一定是匹配不到产生的结果

• 使用右外连接实现上述案例8的要求

SELECT NAME
FROM boys bo
RIGHT OUTER JOIN beauty g
ON bo.`id` = g.`boyfriend_id`
WHERE bo.`id` IS  NULL;

案例9 查询哪个部门没有员工(左外连接)

SELECT d.department_id,department_name,employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

• 右外连接实现上述案例9的要求

SELECT d.department_id, department_name, employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

三、全外连接

• 特点：

假设有表1 和 表2

全外连接匹配结果由3部分组成：
① 表1表2可以相互匹配的部分（都有的交集）
② 表1作为主表，去匹配表2，但表2没有的部分（表1有但表2没有）
③ 表2作为主表，去匹配表1，但表1没有的部分（表2有但表1没有）
因为上述的这种对称性，所以表1和表2的前后顺序可以随意更改

四、交叉连接

• 特点：

本质就是实现了笛卡尔乘积
如表1 有m行，表2 有n行，则结果为m*n行

回忆：

​ 在sql92中是因为在没有合适的连接条件时，就会产生笛卡尔乘积现象
​ 在sql99中是用交叉连接语法实现sql中的笛卡尔乘积现象

SELECT * FROM beauty; 	#12行
SELECT * FROM boys;		#4行

SELECT b.*,g.*
FROM beauty g
CROSS JOIN boys b;		#结果为48行