Mysql中的多表连接

Mysql中的多表连接
一、连接关系
1、内联接（典型的联接运算，使用像 =  或 <> 之类的比较运算符）。包括相等联接和自然联接。 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。   
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。     
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     
1）LEFT  JOIN或LEFT OUTER JOIN     
左向外联接的结果集包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。     2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN     
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。       
3）FULL  JOIN 或 FULL OUTER JOIN（注意MYSQL不支持全外连接查询）
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。   
3、交叉联接   
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。 FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。


二、示例演示

首先创建两个数据表，内容如下：

DeptTB

EmployeeTB

现在需要进行连接查询，连接两张表来检索数据。分别检索员工信息表的员工编号、员工姓名和部门信息表中的部门名称。 显然，两个表的连接条件是 员工表的部门编号=部门表的部门编号 
注意：唐七不属于任何部门，而工程部不存在任何的员工。

1、内连接查询 
有两种方式，这两种是等效的 
一种是：隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。如下:

SELECT   e.employeeid,  e.employeename,  d.deptname FROM  EmployeeTB AS e,  DeptTB AS d WHERE e.deptid = d.deptid 

另外一个是：显式的内连接，即一般内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。如下：

SELECT   e.employeeid,  e.employeename,  d.deptname FROM  EmployeeTB AS e   INNER JOIN DeptTB AS d     ON e.deptid = d.deptid 

两个语句的检索结果都是：  




因为采用内连接计算的时候必须要保证连接的条件e.deptid=d.deptid匹配，结果才会被检索出来。当我们连接两张检索数据的时候，检索的方式是首先逐行扫描“员工信息表”中的记录，然后根据连接条件来决定此记录是否被检索。比如对于张三，这条记录的deptid是01（部门编号），它在部门表中能找到和它匹配的编号01，而编号01的部门名称（deptname）是“技术部”所以张三这条记录会被检索。但是唐七的部门编号是NULL，它在部门信息表中找不到匹配的项（因为部门信息表中不存在部门编号为NULL的部门），所以唐七不会被检索。 

2、外连接查询
外连接查询不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。
三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。
说明：左表就是在“（LEFT OUTER JOIN）”关键字左边的表。右表当然就是右边的了。在三种类型的外连接中，OUTER 关键字是可省略的。
1）左外连接示例
有些情况下，我们需要知道所有员工的信息，即使他不属于任何部门。这样我们就可以采用外连接，在这里为左外连接，也就是连接中的左表的表中的记录，无论能不能在右表中找到匹配的项，都要检索，如果没有匹配的项目，那么右表中的字段值为NULL（空），在这里就代表，此员工不属于任何部门。检索语句为： 

SELECT   e.employeeid,  e.employeename,  d.deptname FROM  EmployeeTB AS e   LEFT OUTER JOIN DeptTB AS d     ON e.deptid = d.deptid 

查询结果如下：

2）右外连接示例
有时，我们需要知道，全部部门的信息，即使它没有任何的员工。在我们的查询中部门表在连接的右边，如果我们想知道右边表中的所有记录信息，那么就可以采用右外连接，如果此记录在左边的表中找不到匹配项，则相应字段（employeeid,employeename)为NULL 检索语句为： 

SELECT   e.employeeid,  e.employeename,  d.deptname FROM  EmployeeTB AS e   RIGHT   OUTER JOIN DeptTB AS d     ON e.deptid = d.deptid 

检索的结果是： 

3）完全外连接（MYSQL不支持）

如果我们想知道所有的记录呢？无论员工有没有部门，部门有没有员工，我们都需要检索。这里就可以使用完全外连接。关注连接中的两部分。如果没有部门，部门为空，没有员工，员工信息为空。 检索语句为： 

SELECT   e.employeeid,  e.employeename,  d.deptname FROM  EmployeeTB AS e FULL   OUTER JOIN DeptTB AS d     ON e.deptid = d.deptid 

上述语句对于mysql来说是无法执行的，那么如何利用MYSQL来实现完全外连接？

方法：left join + union(可去除重复数据)+ right join
例如两张表时可以使用：
select * from A left join B on A.id = B.id (where 条件）
union
select *from A right join B on A.id = B.id （where条件);
例如：

SELECT   e.employeeid,  e.employeename,  d.deptname FROM  EmployeeTB AS e   LEFT OUTER JOIN DeptTB AS d     ON e.deptid = d.deptid UNIONSELECT   e.employeeid,  e.employeename,  d.deptname FROM  EmployeeTB AS e   RIGHT   OUTER JOIN DeptTB AS d     ON e.deptid = d.deptid 

查询结果如下：


3、交叉联接
将两个表的所有行进行组合，连接后的行数为两个表的乘积数。（笛卡尔积） 
关键词：CROSS JOIN 
格式：FROM 表名1 CROSS JOIN 表名2

例如：

SELECT   * FROM  DeptTB   CROSS JOIN EmployeeTB 

结果如下：

三、SQL查询的基本原理
第一、单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。
第二、两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
理解SQL查询的过程是进行SQL优化的理论依据。


四、ON后面的条件（ON条件）和WHERE条件的区别：
ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：
ON只进行连接操作，WHERE只过滤中间表的记录。