Mysql中的多表连接

向右看齐 2022-06-17 08:09 156阅读 0赞

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

![20170420212943813][]

EmployeeTB

![20170420213020985][]

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

两个语句的检索结果都是：  
  
![20170420213212122][]  
  
  
因为采用内连接计算的时候必须要保证连接的条件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

查询结果如下：  
![20170420213238079][]  
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

检索的结果是：

![20170420213333329][]

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 
    UNION
    SELECT 
      e.employeeid,
      e.employeename,
      d.deptname 
    FROM
      EmployeeTB AS e 
      RIGHT 
      OUTER JOIN DeptTB AS d 
        ON e.deptid = d.deptid

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

例如：

SELECT 
      * 
    FROM
      DeptTB 
      CROSS JOIN EmployeeTB

结果如下：

![20170420213538716][]  
三、SQL查询的基本原理  
第一、单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。  
第二、两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。  
第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。  
理解SQL查询的过程是进行SQL优化的理论依据。  
  
  
四、ON后面的条件（ON条件）和WHERE条件的区别：  
ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。  
WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。  
从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：  
ON只进行连接操作，WHERE只过滤中间表的记录。

[20170420212943813]: /images/20220617/40e8c010dac6461bb0ceaeb5eafc6921.png
[20170420213020985]: /images/20220617/c668380e52fe4f53abae6ec4b61b03d3.png
[20170420213212122]: /images/20220617/2534bd65dc874b0781d1ba77963222e7.png
[20170420213238079]: /images/20220617/8406efde6fff489ebb7fd96852fda278.png
[20170420213333329]: /images/20220617/dd82c6142f304caa85ec2c22dfa41caa.png
[20170420213508938]: /images/20220617/3ba3196c64a14aa48871612b7340875a.png
[20170420213538716]: /images/20220617/b0bba8bd0e2d46a0b5596135d56b921f.png