一文完整MySQL连接查询，笛卡尔乘积，内连接外连接交叉连接

---

连接查询又称为多表查询，当查询的字段来自于多个表时，使用连接查询。

---

笛卡尔乘积

笛卡尔乘积现象：表1有m行，表2有n行，结果有m*n行

发生原因：没有有效的连接条件

避免：添加有效的连接条件

SELECT `name`,`boyName`
FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`=`boys`.`id`;

---

连接查询分类

按年代分：92标准（①），99标准（①②中的左右③）

按照功能分类：

①内连接：等值连接，非等值连接，自连接

②外连接：左外连接，右外连接，全外连接

③交叉连接

MySQL支持内连接，外连接（左外和右外），交叉连接

---

等值连接

1、92标准

①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
①一般需要为表起别名
⑤可以搭配前面介绍的所有子句

SELECT 表1中字段,表2中字段
FROM 表1,表2 (表顺序可以换)
WHERE 表1.依据的字段=表2.依据的字段; (连接条件)

SELECT `name`,`boyName`
FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`=`boys`.`id`;

字段有歧义时，可以用表.限制，如果表面太长，可以给表起别名，而起别名后就不能用原表名了，需要使用别名。

SELECT `name`,`boyName`
FROM `beauty` AS a,`boys` As o
WHERE a.`boyfriend_id`=o.`id`;

可以添加筛选条件。

SELECT `last_name`,`department_name`,`commission_pct`
FROM `departments` AS d,`employees` AS e
WHERE d.`department_id`=e.`department_id`
AND `commission_pct` IS NOT NULL;

组合分组查询。

#查询每个城市有多少个部门
SELECT COUNT(*) AS 个数,`city`
FROM `departments` AS d,`locations` AS l
WHERE d.`location_id`=l.`location_id`
GROUP BY `city`;

2、99标准

SELECT `last_name`,`department_name`
FROM `employees` AS e
INNER JOIN `departments` AS d           #inner为内连接类型标识
ON e.`department_id`=d.`department_id`; #连接条件

#多表
SELECT `last_name`,`department_name`,`city`
FROM `employees` AS e
INNER JOIN `departments` AS d ON e.`department_id`=d.`department_id`
INNER JOIN `locations` AS l ON d.`location_id`=l.`location_id`
ORDER BY `department_name` DESC;

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

---

非等值连接

区别于等值连接在于连接条件不是=。

1、92标准

SELECT salary,grade_level
FROM employees AS e,job_grades AS g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

也可以组合前面讲到的子句。

2、99标准

SELECT `salary`,`grade_level`
FROM `employees` AS e
INNER JOIN `job_grades` AS j
ON `salary` BETWEEN `lowest_sal` AND `highest_sal`;

也可以组合前面讲到的子句。

---

自连接

相当于是一张表里面的等值连接，即一张表里面的某两个字段相等。

1、92标准

SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM `employees` AS e,`employees` AS m
WHERE e.`manager_id`=m.`employee_id`

2、99标准

SELECT e.`last_name` AS 员工,m.`last_name` AS 领导
FROM `employees` AS e
INNER JOIN `employees` AS m
ON e.`manager_id`=m.`employee_id`;

同样可以组合其他子句。

---

外连接

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

特点:
①外连接的查询结果为主表中的所有记录
②如果从表中有和它匹配的，则显示匹配的值
③如果从表中没有和它匹配的，则显示null
④外连接查询结果=内连接结果+主表中有而从表没有的记录

左外连接，left join左边的是主表
右外连接，right join右边的是主表
左外和右外交换两个表的顺序，可以实现同样的效果

#左外连接
SELECT be.`name`,bo.`boyName`
FROM `beauty` AS be
LEFT JOIN `boys` AS bo
ON be.`boyfriend_id`=bo.`id`;

#右外连接
SELECT be.`name`,bo.`boyName`
FROM `boys` AS bo
RIGHT JOIN `beauty` AS be
ON be.`boyfriend_id`=bo.`id`;

MySQL不支持全外连接
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

---

交叉连接

SELECT be.`name`,bo.`boyName`
FROM `boys` AS bo
CROSS JOIN `beauty` AS be;

相当于就是99标准的笛卡尔乘积