查询（二）多表查询----连接查询

多表查询有两种方式：1）连接查询；2）合并查询。这里先讲连接查询

一、概念：

在关系数据库中，一个实例数据库可能包含多张关系表，这些关系表可能存在某种联系，根据这些联系将表与表连接起来，这种查询设计称为连接查询。基础概念：
1、笛卡尔积：域或关系在连接时的所有可能的元组集合。 基本定义：两个关系R，S ，其度分别为n，m，则它们的笛卡尔积是所有这样的元组集合：

其中，元组的前n个分量是R中的一个元组，后m个分量是S 中的一个元组。举例：

2、θ连接：笛卡尔积的“变种”，它是从两个关系的笛卡尔积中选取给定属性间满足一定条件的元组集合：

其中θ 为算术比较符，θ∈{ > ,≥ ,< ,≤ ,= ,≠}  ,举例：

连接查询本质上由广义笛卡尔积演变而来的。 基本定义：通过连接运算符进行多表的查询

二、连接方式：

连接查询通过广义笛卡尔积的形式将多张表的数据连接在一起，从而生成一张描述结果集的表，常见的有三种连接方式：

对于外连接（left/right join），不管on后面跟什么条件，左/右表的数据全部查出来，因此要想过滤需把条件放到where后面； 对于内连接inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用，也可以把条件放到where后面。

1、内连接：

内连接实际上就是θ连接的SQL实现，是通过比较运算符（join或inner join）查询两张表的关联数据，只返回符合条件的行。一般情况下，两张表的连接条件都是依照外码约束来建立的。例：

查询软件开发部的所有员工，获取员工的部门名称D、员工姓名E和员工年龄A等信息。两种写法：

（1）显式内连接：

是标准的内连接的写法（from    表1    inner  join  表2   on  条件）：

（2）隐式连接：

  比较老旧的写法（from    表1,表2    where   条件）：

从性能角度来看，对于MySQL来说，显示连接通常比隐式连接更高效。这是因为MySQL查询优化器可以更好地优化显示连接，使用适当的连接顺序和连接类型来提高查询执行效率。而对于隐式连接，查询优化器可能无法进行相同的优化，从而导致性能下降。

2、交叉连接：

交叉连接使用cross join关键字，它的用法有两种：
（1）执行 select * from A cross join B 的结果集，实际上就是关系A和关系B的笛卡尔积。
（2）执行 select * from A cross join B where A.xx=B.xx ...; 的结果集，实际上就是关系A和关系B的θ连接（内连接）。
例一：

如果每个员工都没有分配部门，即dept_id列的值均为null，那么每一位员工都有哪些分配方案呢？针对这种情况，就可以使用不带where字句的交叉连接，它会将连接的所有可能都列举出来，sql语句：select * from  employee   cross  join    dept;查询得到的结果集如下：

有点像两张表相乘得到的结果。

例二：

查询软件开发部有哪些员工，可以这样写：

和内连接的效果一样。

3、外连接：

（1）左外连接：
左外连接使用 left join 或 left outer join 关键字，执行结果中包含左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL)。

例：

    

获取所有员工信息，输出部门D、员工姓名E，要求显示所有部门，无论该部门是否有员工，如果没有显示null：

      

查询得到的结果集如下：

        

（2）右外连接：
右外连接使用 right join 或 right outer join 关键字，执行结果中包含右表所有行，如果右表中某行在左表没有匹配，则结果中对应行左表的部分全部为空(NULL)。它实际上和左外连接是“左手和右手”的问题。

例：获取所有员工信息，输出部门D、员工姓名E，要求显示所有员工，无论该员工是否有部门归属，如果没有显示null。可以这样写：

            

得到的结果集如下：

       

（3）全外连接：
全外连接使用 full join 或 full outer join 关键字，执行结果中包含左表和右表所有行，如果右表中某行在左表没有匹配，则结果中对应行左表的部分全部为空(NULL)，反之亦然。

例：获取所有员工信息，输出部门名称D、员工姓名E，要求显示所有部门、员工，没有显示null。

          

得到的结果集如下：

三、外连接查询注意事项：

1、左连接（右连接），先执行on，再执行where：不管on的条件是什么一定会把左（右）的数据都查出来，对另一个关联表关联不上的字段置空，如果有where，最后对整体结果进行where过滤。这时候如果where对关联不上的字段进行过滤，查出来的数据就不是左（右）表全部数据了。如现有请假表

（1）查询请假记录，如果发起用户未删除，则展示用户信息。执行

SELECT
	*
FROM
	leave_detail ld
LEFT JOIN (
	SELECT
		*
	FROM
		t_user
	WHERE
		ISDELETE = 0
) tu ON ld.apply_user_account = tu.ACCOUNT
WHERE
	ld.is_delete = 0

 查询出来的结果也为89条，表示查询全部请假数据，再与用户表关联，匹配未删除的用户做为请假申请人；

（2） 查询未删除的用户请假记录。where放在后面对整体结果过滤

SELECT
	*
FROM
	leave_detail ld
LEFT JOIN t_user_new tu ON ld.apply_user_account = tu.ACCOUNT
WHERE
	ld.is_delete = 0
AND tu.ISDELETE = 0

结果为72条 ，可见含义不同。

2、多表连接查询去重复问题,可以使用Group By 或者distinct

（1）group by：如：

<!--获取分页数据-->
    
        select * from user u left join userrole ur on u.id=ur.uid left join role r on r.rid=ur.rid
        where 1=1    
        group by u.id
        limit 0,7
    </select>

select count(*) from 
        (select u.id from user u left join userrole ur on u.id=ur.uid left join role r on r.rid=ur.rid
        group by u.id) as a

2）distinct：如：

SELECT DISTINCT
	u.user_id,
	u.user_name
FROM
	USER u
LEFT JOIN userrole ur ON u.id = ur.uid
LEFT JOIN role r ON r.rid = ur.rid    

SELECT
	count(DISTINCT u.user_id) from  t_user u
LEFT JOIN userrole ur ON u.id = ur.uid
LEFT JOIN role r ON r.rid = ur.rid