数据库语法总结（3）——多表联查

根据前两篇的基础内容，我们今天开始学习多表联查，喜欢的小伙伴可以收藏关注，我会持续更新修改文章。

1、合并多个行集

例如两张没有关联的表出现在同一个查询结果里，我们在前两张的table新增一张表T_EMP,其中有三个字段EMPID,EMPNAME,EMPAGE。此时我想把T_TEST和T_EMP的两表字段一起查出来

SELECT ID,datas,T_NAME FROM T_TEST 
UNION ALL
SELECT EMPID,EMPNAME,EMPAGE FROM T_EMP;

此时有两个注意点：union all必须要保持两个表中查询的字段类型和顺序一致；union all不会去重。

那如果想要去重的话去掉all直接使用union就可以了，其实写全了就是union distinct,distinct是可以省略的。(但是不推荐哦，因为数据量大效率会慢)

同理，还有单表去重时也尽量不使用distinct，尽量用exists替代distinct

2、合并相关的行（根据一张表的其中一个或多个相同的数据查询另一张表中的数据）

SELECT t.ID,t.datas,t.T_NAME,e.EMPNAME  FROM T_TEST t,T_EMP e
 WHERE t.ID =e.EMPID AND t.ID =2

SELECT t.ID,t.datas,t.T_NAME,e.EMPNAME  
 FROM T_TEST t INNER JOIN T_EMP e on  t.ID =e.EMPID WHERE t.ID =1

扩展：两种都属于内连接的形式，具体用哪种方法根据自身喜爱风格。

其中inner join是取on条件的交集，两表都有结果才会有；

全外连接，全拼full outer join(Mysql不支持，需要用到union连接两表结果)Oracle中有特殊的外连接语法：

full join是取两表并集，两表的结果都查出来，没有的数据为空代替；

--正常用法
SELECT t.ID,t.DATAS ,e.EMPID,e.EMPNAME  FROM T_TEST t  FULL JOIN T_EMP e ON
 t.ID =e.EMPID ORDER BY t.ID ;
--Oracle中特殊用法
 SELECT t.ID,t.DATAS,e.EMPID ,e.EMPNAME   FROM T_TEST t,T_EMP e WHERE t.ID =e.EMPID (+)
 UNION
 SELECT t.ID,t.DATAS,e.EMPID ,e.EMPNAME  FROM T_TEST t,T_EMP e WHERE t.ID(+) =e.EMPID ;

还有反连接（anti_join）：全写法是left/right outer join,outer可以省略写

left join 以左边表为基准，左表有就显示数据，左表没有就不显示数值，以NULL代替；

right join 以右表为基准，和left join相反。

多提一下：如果多个表想要保留不同数据，可以内连接和反连接同时作用；或者使用子查询，即：

 SELECT a.id,b.name,(SELECT c.age FROM table_c WHERE c.id=b.id) AS age
 FROM table_a a,table_b b WHERE a.id=b.id;

select a.id,b.name,c.age from table_a a 
 inner join table_b b on a.id=b.id 
 left join table_c c on b.id=c.id;

此时select c.age 这的子查询只能查询一个字段，想要进行多字段的子查询，解决方法会在后续具体提到，可以使用对象类型来解决。更新文章之后会附上连接

 针对于上述情况，我们还可以用集合运算  intersect来返回两个表的交集

SELECT EMPID,EMPNAME,EMPAGE  FROM T_EMP 
INTERSECT
SELECT ID,DATAS,t_name  FROM T_TEST;

--两表连接借用in
SELECT EMPID,EMPNAME,EMPAGE  FROM T_EMP 
WHERE (EMPID) IN 
(
    SELECT EMPID  FROM T_EMP 
    INTERSECT
    SELECT ID  FROM T_TEST
);

  扩展：intersect中会返回两个表中相同的行，在此时我们如果只想通过一个关联字段而查询一张表的全部行，则可以借助关键字in。

集合运算默认去重，不会返回重复的行。用法类似于union distinct，功能类似于inner join。

3、检索一张表没有出现在另一张表中的数据

与上面集合运算相反，如果我们想要检索在第一张表中出现的数据，而没有在第二章表中出现的数据时，则出现了对应的差集运算。但是在不同类型数据库中差集运算的关键字不同，我们逐一说一下吧：

DB2、PostgreSQL、Sqlserver：使用关键字except

SELECT EMPID,EMPNAME,EMPAGE  FROM T_EMP 
except
SELECT ID,DATAS,t_name  FROM T_TEST 

Oracle中使用关键字minus

SELECT EMPID,EMPNAME,EMPAGE  FROM T_EMP 
minus
SELECT ID,DATAS,t_name  FROM T_TEST 

Mysql中没有差集运算这一关键字，所以我们可以考虑用字段 not in 来查询

SELECT ID,DATAS,t_name  FROM T_TEST  WHERE ID NOT  IN (SELECT EMPID FROM T_EMP);

但是此时我们又遇到了一个问题就是当T_EMP的EMPID字段有NULL值时，则不会有查询结果，因为从本质来说in或not in都是or运算，由于数据库中逻辑运算方式导致的。此时我们扩展一下关于or、and、not在数据库中的逻辑处理（此处是自我理解，有补充的可以留言）

or：有真则为真，T+F=T,T+T=T,T+N=T,相反全假则为假：F+F=F,而N位于T和F之间的位置N+F=N,N+N=N,N+T=T。总结一下就是T>N>F。

and:有假则为假：F+T=F,F+N=F,F+F=F;  同样是全真则为真：T+T=T，而N同样是在两者之间N+T=T,N+N=N,N+F=F。总结一下就是F>N>T

not:很简单，就是取反T<——>F；F<——>T；N<——>N。   

为了避免上述not in的情况我们可以使用关联子查询 not exists

（关联子查询含义：在子查询中引用了外部查询返回的行）

--select 1 代表查询表中任意一行有值数据
SELECT ID,DATAS,t_name  FROM T_TEST t  WHERE  NOT EXISTS
(SELECT 1  FROM T_EMP e WHERE t.ID=e.EMPID);
--或
SELECT ID,DATAS,t_name  FROM T_TEST t  WHERE  NOT EXISTS
(SELECT e.EMPID  FROM T_EMP e WHERE t.ID=e.EMPID);

select 1 from mytable;与select anycol(目的表集合中的任意一行） from mytable;与select * from mytable 作用上来说是没有差别的，都是查看是否有记录，一般是作条件用的。select 1 from 中的1是一常量，查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表。

4、查询两张表中字段不相同的数据或者数量不相同的数据。（就是查询结构相同的两张表中不相同的数据）

此处只提供思路，具体代码就不详细描写了。根据上面3提到的内容，如果我们有两张类似的表想要查看两张表中不同的数据（包括数量，某一字段的数值等等，只要有一个地方不同就列出来，但是如果数量重复还要除重）

这里呢我关注了其他博主的文章；连接如下：

原文链接：https://blog.csdn.net/m0_67621290/article/details/128934531

小伙伴们具体详读就很好理解了！！！

就是使用差集运算算出两表之间不同的字段，然后使用union all连接，但是Mysql和Sqlserver略有不同，使用上述的关联子查询来查找数据然后用union all连接。

扩展：我们在查询数据的时候需要避免笛卡尔积，笛卡尔积又叫笛卡尔乘积，是一个名叫笛卡尔的人提出来的，简单的说就是两个集合相乘的结果，具体有兴趣的小伙伴可以查一下，类似于集合A{a1,a2,a3}三个数，集合B{b1,b2}两个数。然后他们的笛卡尔积就是A*B={(a1,b1),(a1,b2),(a2,b1),(a2,b2),(a3,b1),(a3,b2)}共2*3=6个结果。

避免笛卡尔积就是说要考虑至少需要n-1次连接，此处n是from子句中表的个数。说白话，就是多表联查就首先考虑字段联查，避免出现冗余数据。此处是联查考虑的事前逻辑。

笛卡尔积关键字cross join,用法同inner join不同，cross join不需要on,不用内连接

如果inner join的on条件是1=1，就会有cross join效果，例如：select * from table1 t inner join table2 e on1=1

顺便提一下还有natural join,自然连接相当于=，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。也不用on,不用内连接，但是会除重，作用是内连接。

下面是其他博主的例子，写的很详细，大家可以去看看

https://www.cnblogs.com/klb561/p/11657943.html

5、聚合函数与内/外连接同时使用

聚合函数：是在数据查找基础上对数据的进一步整理筛选行为，常见的聚合函数有：

avg(),max(),min(),sum(),count(),stdev(),stdevp()等等。

（1）同时使用内连接和聚合：

我们在用内连接时，会遇到条件相等时的数据，返回返回多行的情况，如图：执行下面的语句会返回ID为4的数据两条，这是因为我们在T_TEST表中有两条同为4的数据，即使T_EMP只有一条，也会产生两条数据

--两种用法相同，任意一种即可： 
SELECT t.ID,t.DATAS ,e.EMPID,e.EMPNAME  FROM T_TEST t  INNER JOIN T_EMP e ON
 t.ID =e.EMPID ORDER BY t.ID ;
 
  SELECT t.ID,t.DATAS ,e.EMPID,e.EMPNAME  FROM T_TEST t ,T_EMP e WHERE 
 t.ID =e.EMPID ORDER BY t.ID; 

如果我们想要计算T_TEST的ID=1+2+3+4+4=14和T_EMP的EMPID=1+2+3+4=10分别的总和，直接聚合计算结果sumenpid的数值不对，因为4也同时计算了两遍

   SELECT sum(t.ID) AS sumid,SUM(e.EMPID) AS sumempid  
FROM T_TEST t ,T_EMP e WHERE t.ID =e.EMPID;

 为了避免上述情况出现，有两种解决方案：一种是在调用聚合函数时使用distince，这样可以避免重复；另一种是在两表连接前先执行聚合，这样可以避免聚合函数重复运算。

我们先介绍第一种distinct：

Mysql、PostgresSQL中：

SELECT sum(t.ID) AS sumid,SUM(DISTINCT e.EMPID) AS sumempid  
 FROM T_TEST t ,T_EMP e WHERE t.ID =e.EMPID;

DB2、Oracle、Sqlserver中支持使用上面Mysql的方法，也可以使用开窗函数sum over：over后面也可以加分组，由于数据原因，我就不加了。

SELECT DISTINCT  x.IDs,x.EMPIDs FROM ( 
SELECT SUM(t.ID)  OVER () AS  IDs ,SUM(DISTINCT e.EMPID) OVER () AS EMPIDs
FROM T_TEST t  INNER JOIN T_EMP e ON
 t.ID =e.EMPID  ) x;

这样就使返回的结果如下：

 第二种先聚合在连接：

所有数据库都可以使用：

SELECT SUM(IDS) ,SUM(EMPIDs) FROM (

SELECT  SUM(t.ID) AS IDs,x.EMPIDs FROM T_TEST t,

(SELECT e.EMPID,SUM(e.EMPID) AS EMPIDs FROM T_EMP e GROUP BY EMPID) x 

 WHERE t.ID =x.EMPID  GROUP BY x.EMPIDs);

这样就解决了

（2）同时使用外连接和聚合：与内连接和聚合的原理相同，即distinct，sum over，先聚合在外连接，只是需要注意外连接用left outer join。其实方法都是殊途同归的，主要是看大家已知的函数运用是否娴熟，逻辑是否清晰合理。