MySQL连接查询-笛卡尔乘积

前言

SQL查询时，一般会涉及到多张表的字段，这时就需要表连接，然后不断匹配。

一、笛卡尔积

当from后跟多个表时，这些表的每行进行全排列，这就是笛卡尔积，也就是无条件的全排列连接。

二、SQL92（支持内连接）

通过表内的一些条件来连接，有内等值连接、非等值连接、自连接（自表引用）

# 一、sql92标准
# 1.内连接
# a 等值连接
# 注：表名如果取了别名，在形成的视图上有了新名，也只有新名，重名字段就应该用新名调用
SELECT
	boyName,
	`name` 
FROM
	beauty,
	boys 
WHERE
	boys.id = boyfriend_id # 内连接+筛选
SELECT
	boyName,
	beauty.NAME 
FROM
	beauty,
	boys 
WHERE
	boys.id = boyfriend_id 
	AND LENGTH( beauty.NAME ) > 9 # 注：一个汉字三个字节
# 内连接+ 三表连接
# 员工名、部门名、城市名
	USE myemployees;
SELECT
	e.last_name,
	d.department_name,
	l.city 
FROM
	employees e,
	departments d,
	locations l 
WHERE
	e.department_id = d.department_id 
	AND d.location_id = l.location_id # b 非等值连接  大于小于不等于 等
# 查询最高工资>10000的部门名
SELECT DISTINCT
	d.department_name 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id 
GROUP BY
	e.department_id 
HAVING
	MAX( salary ) > 10000 # c 自连接 表的自引用时才用，比如员工的上司也是员工
SELECT
	e1.last_name,
	e2.last_name 
FROM
	employees e1,
	employees e2 
WHERE
	e1.employee_id = e2.manager_id

三、SQL99（内、外、交叉）

1、内连接

等值、非等值、自连接。

/* 
 sql99语法
select 查询列表
from table1 别名 【连接类型】
[注：内连接 [INNER] 左右外 left|right [outer] 全外 full【outer】
交叉连接 cross]
join table2 on 连接条件 
[where]
[GROUP BY]
[HAVING]
[ORDER BY]
*/
#  一、内连接
# 1.可添加排序分组筛选 2.inner可省略 3.连接条件放在on后面，其它条件放在where后面 
SELECT
	* 
FROM
	employees e
	INNER JOIN departments d ON e.department_id = d.department_id # 三表内等值连接
SELECT
	* 
FROM
	employees e
	INNER JOIN departments d ON e.department_id = d.department_id
	INNER JOIN locations l ON d.location_id = l.location_id

2、外连接

2.1、左右外连接

/* 
 sql99语法
select 查询列表
from table1 别名 【连接类型】
[注：内连接 [INNER] 左右外 left|right [outer] 全外 full【outer】
交叉连接 cross]
join table2 on 连接条件 
[where]
[GROUP BY]
[HAVING]
[ORDER BY]
*/ #  一、内连接
# 1.可添加排序分组筛选 2.inner可省略 3.连接条件放在on后面，其它条件放在where后面
# 左右外连接,查询一个表中有，另一个表中没有的行
# 左外连接 = 内连接+主表没涉及到的行
# 查看没有男朋友的女神
USE girls;
SELECT
	* 
FROM
	beauty a
	LEFT JOIN boys b ON a.boyfriend_id = b.id;

WHERE
	b.id IS NULL

2.2、全外连接

SQL99有其规范，但是MySQL还未支持

/* 
/* 
 sql99语法
select 查询列表
from table1 别名 【连接类型】
[注：内连接 [INNER] 左右外 left|right [outer] 全外 full【outer】
交叉连接 cross]
join table2 on 连接条件 
[where]
[GROUP BY]
[HAVING]
[ORDER BY]
*/ #  一、内连接
# 1.可添加排序分组筛选 2.inner可省略 3.连接条件放在on后面，其它条件放在where后面

# 全外连接，MySQL不支持 其效果 = 左外连接+右外连接
# 找出单身的
SELECT
	a.NAME 
FROM
	beauty a
	FULL JOIN boys b ON a.boyfriend_id = b.id 
WHERE
	a.id IS NULL 
	AND b.id IS NULL;

3、交叉连接（MySQL支持）

/* 
 sql99语法
select 查询列表
from table1 别名 【连接类型】
[注：内连接 [INNER] 左右外 left|right [outer] 全外 full【outer】
交叉连接 cross]
join table2 on 连接条件 
[where]
[GROUP BY]
[HAVING]
[ORDER BY]
*/ #  一、内连接
# 1.可添加排序分组筛选 2.inner可省略 3.连接条件放在on后面，其它条件放在where后面
# 外连接 sql99中表达笛卡尔积的方式
SELECT
	a.NAME 
FROM
	beauty a
	CROSS JOIN boys b;

总结

1）SQL99语法功能更加细致和强大
2）多表连接查询

参考文献

[1] MySQL 尚硅谷