MySQL中：内连接，外链接，交叉连接的详细讲解(还有sql92语法和99语法对连接语句支持的对比)

这两天把MySQL复习了一下，当时各种连接这部分学得比较迷糊，所以着重复习并总结了一下。

分类

按年代分：

1. sql92标准：仅仅支持内连接

2. sql99标准：支持内连接，外链接（只支持左外和右外，不支持全外），交叉连接

按类型分：

1. 内连接：

a .等值连接

b. 非等值连接

c. 自连接

2. 外链接：

a. 左外连接

b. 右外连接

c. 全外连接（MySQL不支持）

3: 交叉连接

-------------------------------------------------------------------------------------------------------------------------------

各种连接用法以及适用哪种情况：

1. 内连接：

a. sql92语法的内连接：

1. 等值连接

语法：

select column1,column2,...

from table1 t1,table2 t2,...

where connection_expression

【group by 分组条件】

【having 筛选条件】

【order by 排序列表】

解释：from之后可以跟多个表，表之间使用逗号隔开，顺序没有要求。connection_expression需包含等值连接的条件表达式，如：t1.id=t2.id... 如果有n个表连接，那么条件表达式的数量至少为n-1。【】之间的子句可选。

适用情景：查询两个表之间共同的内容，可以理解为交集。

2. 非等值连接

语法：

语法基本同上，只是连接的条件表达式不再使用等于，需要根据具体需求来定。比如employee(员工)表(含有id, name, salary，三个字段)和salaryLevel(薪资等级)表(含有status, lowest, higheset， 三个字段)，需求为查出所有员工薪资的对应等级，那么连接表达式应该为：薪资 between 最低薪资 and 最高薪资。

适用情景：

需要根据具体需求来定。上述例子图：

employee表：

salaryLevel表:

sql：SELECT id,NAME,STATUS FROM employee e, salarylevel sl WHERE e.salary BETWEEN sl.lowest AND sl.highest;

结果集：

3. 自连接

语法：

语法基本同上，只不过from子句后的表都是同一个表。且连接的条件表达式不再使用等于，需要根据具体需求来指定。比如有一个记录公司所有职员的表company(id,name,boss_id)，表中记录了职员自己的id，姓名，以及他的上级id。整个sql语句应该为：SELECT c1.name AS emp_name,c2.name AS boss_name FROM company c1,company c2 WHERE c2.id = c1.boss_id。

适用情景：

当查询的条件需要使用当前表的字段时应该使用自连接。例子同上描述，因为上级id也需要从company表中查询，所以需要使用自连接。

company表:

sql： SELECT c1.id emp_id,c1.name emp_name,c2.name boss_name FROM company c1,company c2 WHERE c1.boss_id=c2.id;

结果集：

b. sql99语法的内连接：

条件表达式和适用情景上面已经描述不再赘述，根据需求做相应的改变即可，这里只写语法

语法：

select 查询列表

from 表1 别名 【连接类型】

join【inner】表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】

2. 外链接：

a. 左外连接：

语法：

select 查询列表

from 表1 别名 【连接类型】

left【outer】join 表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】

适用情景：

当需要查找一个表与另一个表的共同记录以及第一个表的其余信息，则需要使用左连接。此时第一张表放在left join左边，称为主表，第二张表放在left join右边，称为从表，两表的位置不能交换。

b. 右外连接：

语法：

select 查询列表

from 表1 别名 【连接类型】

right【outer】join 表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】

适用情景：

当需要查找一个表与另一个表的共同记录以及第二个表的其余信息，则需要使用右连接。此时第一张表放在right join左边，称为从表，第二张表放在righ join右边，称为主表，两表的位置不能交换。

c. 全外连接(MySQL不支持)：

语法：

select 查询列表

from 表1 别名 【连接类型】

full【outer】join 表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】

适用情景：

要查找两个表的交集部分以及两个表各自剩余的部分(与“笛卡尔积-自然连接”不同)。

3. 交叉连接：

语法：

select 查询列表

from 表1 别名 【连接类型】

cross join 表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】

适用情景：

交叉连接也就是笛卡尔积，自然连接。