MySQL sql99连接查询——INNER JOIN内连接、LEFT JOIN外连接、CROSS JOIN交叉连接、join、inner join、cross join 的关系、USING_inner join using

参照mysql官方文档：

https://dev.mysql.com/doc/refman/8.0/en/join.html
1

sql99语法

• 语法:
  select查询列表
  from表1 别名[连接类型]
  join表2 别名
  on连接条件
  [where筛选条件]
  [group by分组]
  [having筛选条件]
  [order by排序列表]

• 分类:

• 内连接 : inner

• 外连接
  左外:left [outer]
  右外: right [outer]
  全外: full [outer]

• 交叉连接: cross

join、inner join、cross join 的关系

一、内连接

inner join(等值连接) 只返回两个表中联结字段相等的行

不添加ON条件 相当于全连接 JOIN、CROSS JOIN

• 语法:

select查询列表
from 表1 别名
inner join表2 别名
on连接条件;
1
2
3
4

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

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

1、等值连接

#查询员工名、对应的部门名，员工名名字有e

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
WHERE e.last_name LIKE '%e%'
1
2
3
4
5

#查询部门个数>3的城市名，部门个数（分组+筛选+排序）

SELECT city,COUNT(*) 部门个数
FROM locations l
INNER JOIN departments d
ON l.location_id = d.location_id
GROUP BY city
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
1
2
3
4
5
6
7

#查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

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 j.`job_id` = e.`job_id`
ORDER BY department_name DESC;
1
2
3
4
5

2、非等值连接

#查询员工的工资级别

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.lowest_sal AND g.highest_sal;
1
2
3
4

3、自连接

#查询员工名字、上级名字

SELECT e.`last_name`,m.`last_name`
FROM employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id`;
1
2
3
4

二、外连接

应用场景：用于查询一个表中有，另一个表中没有的记录

left join(左) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右) 返回包括右表中的所有记录和左表中联结字段相等的记录

LEFT JOIN是LEFT OUTER JOIN的简写版

特点:

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

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

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

4. 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
   包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行;

#外连接：查询哪个部门没有员工
#左外

SELECT d.*,e.`employee_id`
FROM departments d
LEFT OUTER JOIN employees e
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NOT NULL;
1
2
3
4
5

#右外

SELECT d.*,e.`employee_id`
FROM employees e
RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NOT NULL;
1
2
3
4
5

#全外
注意：FULL OUTER JOIN 在oracle中支持。

USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend id = bo.id;
1
2
3
4
5

三、交叉连接（sql99语法中代表笛卡尔乘积）

生成笛卡尔积
它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配(开发中避免使用);

SELECT j.*,d.*
FROM jobs j
CROSS JOIN departments d
1
2
3

四、USING

五、NATURAL JOIN