MySQL多表连接查询

1 什么是多表连接查询

在一个查询语句中显示多张表的数据，这也叫多表数据记录的连接查询。

在实现连接查询时，首先是将两个或两个以上的表按照某种关系连接起来（连接后形成一个新的关系表），然后再查询到所要求的的数据记录。

连接查询分为外连接查询和内连接查询。

2 表和表之间的关系

2.1 并

并（union）：将具有相同字段数目和字段类型的两张表关联到一起进行查询，
（1）union：去掉重复记录（两个表中每个字段的值都一样）

select 列 from 表1
union
select 列 from 表2

（2）union all：不会去掉重复的记录

select 列 from 表1
union all
select 列 from 表2

示例：查询cstudent表和mstudent两张表中的姓名和性别。（UNION）

sql语句：

SELECT name,sex FROM cstudent
UNION
SELECT name,sex FROM mstudent;

从上面结果可以看出，UNION去掉了重复的记录：张三，只显示一条记录。如果使用UNION ALL，则会显示两个张三：

SELECT name,sex FROM cstudent
UNION ALL
SELECT name,sex FROM mstudent;

2.2 笛卡尔积

笛卡尔积：将两张表的所有字段进行合并，两张表中的记录进行组合，比如第一个表中有3条记录，第二个表中有5条记录，两个表经过笛卡尔积操作后将一共会产生3*5=15 种数据记录。

select * from 表1,表2;

笛卡尔积示意图：

示例：求R表和S表的笛卡尔积

sql语句：

SELECT * FROM r,s;

2.3 连接查询

连接操作（JOIN）：在表关系的笛卡尔积数据记录中，按照两个表中相应字段值的比较条件进行选择生成一个新的关系。其实就是将笛卡尔积后的数据记录进行筛选得到相应的数据，根据筛选方式不同，分为内连接（INNER JOIN），外连接 (OUTER JOIN)，交叉连接（CROSS JOIN）。这些连接的基础都是笛卡尔积。

3 查询操作

3.1 内连接查询

内连接查询：保留关系中所有匹配的数据记录，舍弃不匹配的记录，注意，是只会保留符合匹配条件的记录，根据匹配条件分为：自然连接（NATURAL JOIN）、等值连接、不等连接。

1、自然连接（NATURAL JOIN）
在笛卡尔积的数据记录中，首先自动根据关系中相同名称的字段进行记录匹配（即只保留两个同名的字段下值相同的这条记录），然后去除重复字段（重复的字段保留一个），使用关键字 NATURAL JOIN来进行自连接查询操作，自连接自动完成，无法指定连接条件。

换句话说，自然连接不设置连接条件，根据表中相同的字段进行记录匹配，去掉重复的字段。

SELECT * FROM 表1 NATURAL JOIN 表2;

示例：将r表和s表自然连接起来

sql语句：

SELECT * FROM r NATURAL JOIN s;

2、等值连接
内连接查询中的等值连接，使用INNER JOIN…ON…的方式来实现，就是在关键字ON后面使用关系运算符“=”来指定等值条件，顾名思义，就是两个字段的值相等的条件.

等值连接要连接的表需有相同的字段，会匹配字段值相同的记录。

SELECT * FROM 表1 [AS] [表1别名] INNER JOIN 表2 [AS] [表2别名] ON 表1别名.字段=表2别名.字段;

在这个语法中，AS可以省略，所以表后面可以直接跟着别名；如果不使用别名也可以，采用别名是为了简化过长的表名。

示例：将R表和S表进行等值连接

sql语句如下：

SELECT * FROM r INNER JOIN s ON r.B=s.B;

3、不等连接
使用INNER JOIN…ON…的方式来实现，就是在关键字ON后面可以使用>、<、>=、<=、！=这些运算符来指定不等值的条件，所以叫不等连接。

示例：将R表和S表进行R.C<S.E的不等值连接。

SELECT * FROM r INNER JOIN s ON r.C<s.E;

3.2 外连接查询（OUTER JOIN）

在表的笛卡尔集中，不仅保留所有匹配的数据记录，而且还会保留部分不匹配的数据记录，按照保留不匹配条件数据记录的来源可分为：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN），外连接查询会返回操作表中至少一个表的所有数据记录。

1、左外连接（left join）：又称左连接，使用关键字 LEFT JOIN…ON…来实现，将笛卡尔积中满足条件的记录和左表中不满足条件的记录匹配出来。

select * from 左表 left join 右表 on 条件;

示例：将R表和S表进行左外连接。

sql语句：

SELECT * FROM r LEFT JOIN s ON r.B=s.B;

2、右外连接（right join）：又称右连接，使用关键字 RIGHT JOIN…ON…来实现，将笛卡尔积中满足条件的记录和右表中不满足条件的记录匹配出来（保留右表中的所有记录，左表中没有匹配的记录显示为Null）。

select * from 左表 right join 右表 on 条件;

示例：将R表和S表进行右外连接。

sql语句为：

SELECT * FROM r RIGHT JOIN s ON r.B=s.B;

3、全外连接（FULL JOIN）：将笛卡尔积中满足条件的记录和左右表中不满足条件的记录都匹配出来。

示例：将R表和S表进行全外连接。

sql语句为：

SELECT * FROM r FULL JOIN s;

3.3 交叉连接（CROSS JOIN）

交叉连接，实际上就是表笛卡尔积后的没有经过条件筛选后所有数据记录，不需要任何匹配条件。（与笛卡尔积结果相同）

示例：将R表和S表进行交叉连接

sql语句为：

SELECT * FROM r1 CROSS JOIN s1;