扫码加入数据分析学习群

多表查询

随着我们使用数据库的灵活性越来越高，当我们需要的数据在不同的表中时，就需要使用多表查询来检 索我们需要的数据。

多表查询分为横向连接查询和纵向合并查询。

1.连接查询

连接查询是通过多张表中共有的关键字段，将多张表连成一张虚拟结果集，来补充字段信息，我们可以 按照业务需求检索多张表中的数据。

SQL查询的基本原理：

单表查询：根据where条件过滤表中的记录，然后根据select指定的列返回查询结果。

两表连接查询：使用on条件对两表进行连接形成一张虚拟结果集；然后根据where条件过滤结果 集中的记录，再根据select指定的列返回查询结果。

多表连接查询：先对第一个和第二个表按照两表连接查询，然后用用连接后的虚拟结果集和第三个 表做连接查询，以此类推，直到所有的表都连接上为止，终形成一张虚拟结果集，然后根据 where条件过滤虚拟结果集中的记录，再根据select指定的列返回查询结果。

多表连接的结果通过三个属性决定：

方向性：在外连接中写在前边的表为左表、写在后边的表为右表。

主附关系：主表要出所有的数据范围，附表与主表无匹配项时标记为null，内连接时无主附表之 分。

对应关系：关键字段中有重复值的表为多表，没有重复值的表为一表

1.1连接方式

MySQL支持的连接方式：内连接和外连接（左外连接、右外连接）

内连接：按照连接条件，返回两张表中满足条件的记录。

select <字段名1>[,<字段名2>...,<字段名n>] from <表名1>[ inner] join <表名2> on < 连接条件>

等值连接：连接条件是两张表中的关键字段取值相等。

不等值连接：连接条件是两张表中的关键字段取值满足非等值比较运算。

select ename,job,hiredate,sal,grade from emp inner join salgrade on sal between losal and hisal;

笛卡尔积连接：两张表中的每一条记录进行笛卡尔积组合，然后根据where条件过滤虚拟结果集中 的记录。

select ename,job,hiredate,sal,dname from emp,dept where emp.deptno=dept.deptno;

自连接：通过设置表别名，将一张表虚拟成多张表。

select t1.ename as 员工姓名,t2.ename as 领导姓名 from emp as t1 inner join emp as t2 on t1.mgr=t2.empid;

表限定符.：如果表1和表2中的字段名相同，则必须使用表限定符.指定引用的是哪个表中的字段。

左连接：按照连接条件，返回两张表中满足条件的记录，以及左表中的所有记录，右表匹配不到显 示为null。

select <字段名1>[,<字段名2>...,<字段名n>] from <表名1> left join <表名2> on <连接 条件>；

示例：查询每个部门的员工数（没有员工的部门，员工数统计为0）

select dept.deptno,dname,count(empid) from dept left join emp on dept.deptno=emp.deptno group by dept.deptno;

右连接：按照连接条件，返回两张表中满足条件的记录，以及右表中的所有记录，左表匹配不到显 示为null

select <字段名1>[,<字段名2>...,<字段名n>] from <表名1> right join <表名2> on <连 接条件>；

1.2连接规则

一表作为主表可以保证维度的完整性，多表作为主表可以保证度量的准确性。

在没有明确表示需要保证维度完整性的情况下，优先保证度量的准确性，所以将度量值所在的表作为主表。 度量字段通常存在于多表中，因此通常情况下可以将多表作为主表进行外连接。

确定查询的信息在哪几张表

确定表和表之间的对应关系和主附关系

确定表和表之间的连接条件

2.合并查询

纵向合并查询又称为联合查询，它是用union关键字把多条select语句的查询结果合并为一个结果集。

纵向合并的前提是被合并的结果集的字段数量、顺序和数据类型必须完全一致。字段名不一样的情况 下，会将第一个结果集的字段名作为合并后的虚拟结果集的字段名。

select <字段1>[,<字段2>,…] from <表名1> union[ all] select <字段1>[,<字段2>,…] from <表名2>;

union去重

union all不去重