SQL Cookbook 系列 -操作多个表

最新推荐文章于 2022-05-21 20:37:23 发布

本自具足反求诸己

最新推荐文章于 2022-05-21 20:37:23 发布

阅读量229

点赞数

CC 4.0 BY-SA版权

分类专栏：数据处理文章标签： sql基础

本文链接：https://blog.youkuaiyun.com/seacean2000/article/details/80748615

32 篇文章

订阅专栏

本文介绍了SQL中各种联接方式及其应用场景，包括union与union all的区别、内联接与外联接的使用方法、差集操作、笛卡尔积的避免、聚集运算与联接的结合使用等高级技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.记录集的叠加,union all可以将多个表中的行组合到一起，

  当然这个也是有要求的，对应的列要做到数据类型一致，

结果集的列数也是要一致。不过这种方式可能包含了重复的数据

   select ename ,deptno from emp where deptno=10

  union all

  select ename,deptno from emp_history  where deptno=10; 

如果要确保一定不会出现重复数据，可以使用union处理，当然代价

是去重的时间，因此在这两者做选择的时候，要通过合理的方式多

使用union all,除非有必要使用union

   select ename ,deptno from emp where deptno=10

  union

  select ename,deptno from emp_history  where deptno=10; 

Note:这部分是新接触到的，需要格外注意

2.组合相关的行，通过列值相等进行组合

第一种方案，以值相等作为条件

select e.ename,d.lot from emp e,dept d  

     where e.deptno=d.deptno and e.deptno=10;

第二种方案时候采用内连接的方式

select e.ename,d.loc from emp e inner join dept d

      on e.deptno=d.deptno where e.deptno=10;

Note:改变书写习惯，多采用第二方案

3.在两个表中查找共同行

第一种方案，适用于mysql,sqlserver

select e.ename,e.deptno,e.job,e.id from emp e,ccp v

    where e.ename=v.ename and e.job=v.job;

select e.ename,e.job,e.deptno e.id from emp e join v

   on (e.ename=v.ename , e.job=v.job);

第二种方案适用于db2,oracle,postgreSQL

select ename,job,deptno,id from emp where

   (ename,job) in 

(select  ename,job from emp intersect select ename,job from v);

解释：集合操作intersect返回两个行来源的共同行，

操作要求字段数目和数据类型完全匹配的，默认不返回重复行

Note:第一种比较好理解，第二种涨见识

4.从一个表中查找另一个表没有的值，差集操作非常有用，但是各有各的操作，有的还不支持

db2和postgreSQL: select deptno from dept except select deptno from emp;

oracle: select deptno from dept minus select deptno from emp;

mysql和sqlserver: select deptno from dept where deptno not in (select deptno from emp);

Note:这种操作还是有点差别的，差集操作会去重，同时mysql和sqlserver要注意null值问题

5.在一个表中查找与其他表不匹配的记录

select d.* from dept d left outer join emp e on (d.deptno=e.deptno)

     where e.deptno is null;

Note:涨见识，第一次见，又称为反联接

6.向查询中增加联接而不影响其他联接

在db2,mysql,postgreSQL,sqlserver,oracle中可以采用：

select e.ename,d.loc,eb.received from emp e 

        join dept d on (e.deptno=d.deptno)

       left join emp_bonus eb on (e.empno=eb.empno)

       order by d.loc;

当然也可以使用嵌套子查询的方式

Note:用而不知

7.检测两个表中是否有相同的数据

先做每个表的差集，再将结果合并

8.识别和消除笛卡尔积

使用联接查询可以消除这种笛卡尔积

Note: 什么是笛卡尔积呢？

Answer:笛卡尔积在数据库中又叫做交叉连接查询，假如第一张表有m条数据，第二张表有n条数据，

两表的笛卡尔积是m*n条数据，也是两表的交叉连接

9.聚集和联接

聚集在这里是指聚集运算函数就是类似求和，平均数，统计数字之类的函数运算。

当聚集运算和联接在一起工作的时候，要格外的关注运算的正确性，特别是在联接

操作过程中产生重复的行时。

解决思路是用distinct关键字去除重复的记录或者是先进行聚集运算再联接

Note：比较复杂的运算，需要格外的仔细

10.聚集和外联接

select deptno ,sum(distinct sal) as total_sal,sum(bonus) as total_bonus from 

      ( select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type is null then 0

                                                                                        when eb.type=1 then .1

                                                                                        when eb.type=2 then .2

                                                                                        else .3 as bonus

                     from emp left outer join emp_bonus eb on (e.empno=eb.empno)

                     where e.deptno=10

    ） group by deptno;

11.从多个有关联的表中返回关联信息不全的数据

select d.deptno,d.ename,d.dname from dept d full outer join emp e

    on (d.deptno=e.deptno)

12.在运算和比较时使用null值

null值在数据库中永远不会等于或者不等于任何值，包括null值本身。

通常在比较的时候，是将null值转换为一个标准值进行比较，coalesce函数可以帮助我们

select ename,comm from emp where coalesce(comm,0)<(select comm from emp where ename='warn');