首页 > 其他分享 >多表查询和left join需要注意的问题

多表查询和left join需要注意的问题

时间：2023-07-21 14:37:45浏览次数：31  
标签：VALUES 00 join INTO dept SCOTT emp 多表 left

一、多表查询

1、内连接

隐式内连接

使用一张以上的表做查询就是多表查询

语法：SELECT {DISTINCT} *|列名.. FROM 表名 别名，表名 1 别名 {WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}

范例： emp表 
DROP TABLE "SCOTT"."EMP";
CREATE TABLE "SCOTT"."EMP" (
  "EMPNO" NUMBER(4) NOT NULL ,
  "ENAME" VARCHAR2(10 BYTE) ,
  "JOB" VARCHAR2(9 BYTE) ,
  "MGR" NUMBER(4) ,
  "HIREDATE" DATE ,
  "SAL" NUMBER(7,2) ,
  "COMM" NUMBER(7,2) ,
  "DEPTNO" NUMBER(2) 
);

插入14条数据

INSERT INTO "SCOTT"."EMP" VALUES ('7369', 'SMITH', 'CLERK', '7902', TO_DATE('1980-12-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '800', NULL, '20');
INSERT INTO "SCOTT"."EMP" VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', TO_DATE('1981-02-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '1600', '300', '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7521', 'WARD', 'SALESMAN', '7698', TO_DATE('1981-02-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '1250', '500', '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7566', 'JONES', 'MANAGER', '7839', TO_DATE('1981-04-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '2975', NULL, '20');
INSERT INTO "SCOTT"."EMP" VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', TO_DATE('1981-09-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '1250', '1400', '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7698', 'BLAKE', 'MANAGER', '7839', TO_DATE('1981-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '2850', NULL, '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7782', 'CLARK', 'MANAGER', '7839', TO_DATE('1981-06-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '2450', NULL, '10');
INSERT INTO "SCOTT"."EMP" VALUES ('7788', 'SCOTT', 'ANALYST', '7566', TO_DATE('1987-04-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '3000', NULL, '20');
INSERT INTO "SCOTT"."EMP" VALUES ('7839', 'KING', 'PRESIDENT', NULL, TO_DATE('1981-11-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '5000', NULL, '10');
INSERT INTO "SCOTT"."EMP" VALUES ('7844', 'TURNER', 'SALESMAN', '7698', TO_DATE('1981-09-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '1500', '0', '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7876', 'ADAMS', 'CLERK', '7788', TO_DATE('1987-05-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '1100', NULL, '20');
INSERT INTO "SCOTT"."EMP" VALUES ('7900', 'JAMES', 'CLERK', '7698', TO_DATE('1981-12-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '950', NULL, '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7902', 'FORD', 'ANALYST', '7566', TO_DATE('1981-12-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '3000', NULL, '20');
INSERT INTO "SCOTT"."EMP" VALUES ('7934', 'MILLER', 'CLERK', '7782', TO_DATE('1982-01-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '1300', NULL, '10');

dept表

DROP TABLE "SCOTT"."DEPT";
CREATE TABLE "SCOTT"."DEPT" (
  "DEPTNO" NUMBER(2) NOT NULL ,
  "DNAME" VARCHAR2(14 BYTE) ,
  "LOC" VARCHAR2(13 BYTE) 
);

插入4条数据

INSERT INTO "SCOTT"."DEPT" VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO "SCOTT"."DEPT" VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO "SCOTT"."DEPT" VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO "SCOTT"."DEPT" VALUES ('40', 'OPERATIONS', 'BOSTON');

salgrade表

DROP TABLE "SCOTT"."SALGRADE";
CREATE TABLE "SCOTT"."SALGRADE" (
  "GRADE" NUMBER ,
  "LOSAL" NUMBER ,
  "HISAL" NUMBER 
);

插入5条数据

INSERT INTO "SCOTT"."SALGRADE" VALUES ('1', '700', '1200');
INSERT INTO "SCOTT"."SALGRADE" VALUES ('2', '1201', '1400');
INSERT INTO "SCOTT"."SALGRADE" VALUES ('3', '1401', '2000');
INSERT INTO "SCOTT"."SALGRADE" VALUES ('4', '2001', '3000');
INSERT INTO "SCOTT"."SALGRADE" VALUES ('5', '3001', '9999');

查询员工表和部门表

select * from emp,dept;

由于emp表有14条记录，dept表有4条记录，故查询结果共有56条记录。

我们发现产生的记录数是 56 条，我们还会发现 emp 表是 14 条，dept 表是 4 条，56 正是 emp表和 dept 表的记录数的乘积，我们称其为笛卡尔积。

如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询。

在两张表中我们发现有一个共同的字段是 depno，depno 就是两张表的关联的字段，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。

select * from emp,dept where emp.deptno=dept.deptno

关联查询后，结果为14条记录

关联之后我们发现数据条数是 14 条，不在是 56 条。

多表查询我们可以为每一张表起一个别名

select * from emp e,dept d where e.deptno=d.deptno

范例：查询出雇员的编号，姓名，部门的编号和名称，地址

select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno

结果：

自关联查询

范例：查询出每个员工的上级领导

分析：emp 表中的 mgr字段是当前雇员的上级领导的编号，所以该字段对 emp表产生了自身关联，可以使用 mgr 字段和 empno 来关联

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno

结果为：

范例:在上一个例子的基础上查询该员工的部门名称 
--范例:在上一个例子的基础上查询该员工的部门名称
select e1.empno,e1.ename,d.dname,e2.empno,e2.ename from emp e1,emp e2,dept d 
where e1.mgr=e2.empno and d.deptno = e1.deptno

结果：

范例：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级

select e.empno,e.ename,
case when s.grade=1 then 'first grade'
      when s.grade=2 then 'second grade'
        when s.grade=3 then 'third grade'
          when s.grade=4 then 'fouth grade'
            when s.grade=5 then 'fifth grade' end as grade1 ,
d.dname,e1.empno, e1.ename,
case when s1.grade=1 then 'first grade'
      when s1.grade=2 then 'second grade'
        when s1.grade=3 then 'third grade'
          when s1.grade=4 then 'fouth grade'
            when s1.grade=5 then 'fifth grade' end as grade2
              from emp e,emp e1,dept d,salgrade s ,salgrade s1
where e.mgr=e1.empno and d.deptno=e.deptno 
and e.sal between s.losal and s.hisal
and e1.sal between s1.losal and s1.hisal

注意：1、salgrade表要用两张表进行区分。2、先根据sal在losal和hisal之间再根据grade的值来确定工资等级。

 结果：

 2、外连接

1、 左连接和右连接

　　当我们在做基本连接查询（内连接）的时候，查询出所有的部门下的员工，我们发现编号为 40 的部门下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的 
-- 查询出所有的部门下的员工
select * from dept left outer join emp on dept.deptno=emp.deptno

结果如下：

左连接右连接的另外一种表示方法：

使用(+)表示左连接或者右连接，当(+)在左边表的关联条件字段（即右边表的字段）上时是左连接，如果是在右边表的关联条件字段上就是右连接。

select * from dept,emp where dept.deptno=emp.deptno(+)

范例：查询出所有员工的上级领导

分析：我们发现使用我们以前的做法，即使用内连接，由于KING没有上级领导，所以KING没有被查询出来。发现 KING 的上级领导没有被展示，我们需要使用左右连接把他查询出来

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+)

结果为：

JOIN的左边尽量放小数据量的表，这样可以提高查询效率，优化查询速度。

多个left join的执行顺序

例如：

SELECT table_1.a, table_1.b, table_1.c
FROM table_1 
    LEFT JOIN table_2
    ON table_1.uid = table_2.uid
    LEFT JOIN talbe_3
    ON table_1.uid = table_3.uid

执行顺序是：table_1和table_2先组合成一个虚拟表，然后这个虚拟表再和table_3关联。

二、left join需要注意的事项

1、筛选条件放在where后和on后是不一样的

select * from emp left outer join dept on dept.deptno=emp.deptno

结果：

当筛选条件放到where后，表示对左外连接的数据进行筛选

select * from emp left outer join dept on dept.deptno=emp.deptno where dept.dname='SALES';

结果如下：

当筛选条件放到on后，表示在左外连接后，对右表的数据进行筛选，左表的数据全有。

select * from emp left outer join dept on dept.deptno=emp.deptno and dept.dname='SALES';

结果如下：

如果被关联的表中没有能匹配关联条件，这会让数据库用NULL去填充结果。

2、使用left join查询数据字典时需要先left join t_dict_type再left join t_dict_entry

当不使用left join时

select * from b_cts_product where product_name = '地西泮注射液' and authorized_no='H41024255';

此时只能查出一条数据

当我们先left join t_dict_type再left join t_dict_entry时

select t1.*,de.dictname as type
from b_cts_product t1                                                 
left join t_dict_type dt on dt.dicttypecode = 'PRODUCT_TYPE' 
left join t_dict_entry de on de.dictcode = t1.type and dt.dicttypeid = de.dicttypeid
where product_name = '地西泮注射液' and authorized_no='H41024255';

此时也只能查出一条数据。

当我们先left join t_dict_entry再left join t_dict_type时

select t1.*,de.dictname as type
from b_cts_product t1  
left join t_dict_entry de on de.dictcode = t1.type
left join t_dict_type dt on  dt.dicttypecode = 'PRODUCT_TYPE' and dt.dicttypeid = de.dicttypeid
where product_name = '地西泮注射液' and authorized_no='H41024255';

此时能查询出26条数据，显然是不正确的。

这种情况下，left join之后数据量增加，因为t_dict_entry表中有重复的dictcode(如存在很多值为3的dictcode)，并且正好符合关联条件的时候，结果表就会被撑大。

 

标签：VALUES,00,join,INTO,dept,SCOTT,emp,多表,left
From： https://www.cnblogs.com/zwh0910/p/14748228.html

相关文章

  • mysql多表关联的新增和修改操作
    1.多表的新增操作需求：从一个表查出的字段插入到另一个表示例：将loit_bear表的type_code和type_name的字段值全部分别新增到loit_dict表的dict_code和dict_name字段INSERTINTOloit_dict(dict_code,dict_name)selecttype_code,type_namefromloit_bear2.多表的修改操作......
  • Android No space left on device
    Android设备上的“无可用空间”问题在使用Android设备时，你可能会遇到一个常见的问题：设备上的存储空间不足。当你尝试安装或更新应用程序、拍摄照片、录制视频或下载文件时，你可能会收到一个错误消息：“无可用空间”。本文将解释这个问题的原因，并提供一些解决方案。原因Android设备......
  • docker “no space left on device”
     1、先使用dockerinfo查看docker的信息dockerinfo可以看到docker的根路径是 /var/lib/docker  2、查看docker根路径的磁盘占用率df-Th/var/lib/docker3、清理docker 方法一：删除所有未运行的容器（已经运行的删除不了，未运行的就一起被删除了）dockerrm$(docker......
  • MySQL只多表查询
    多表查询内连接隐式内连接查询每一个员工的姓名，及关联的部门的名称(隐式内连接实现)表结构:emp，dept连接条件:emp.dept_id=dept.idselectemp,name,dept,namefromemp,deptwhereemp.dept_id=dept.id;selecte,name,d.namefromempe,deptdwheree.......
  • spark多表join
    Spark多表Join在大数据处理中，数据通常以分布式存储和处理的方式进行管理。当数据存储在不同的表中，并且需要将它们合并在一起以进行分析时，就需要使用多表连接操作。Spark是一个流行的分布式计算框架，提供了强大的多表连接功能，可以高效地处理大规模数据集。什么是多表Join？多表Join......
  • P5427 [USACO19OPEN] Left Out S
    P5427[USACO19OPEN]LeftOutS-洛谷|计算机科学教育新生态(luogu.com.cn) 你有个01矩阵，每次可翻转一行或一列，问能否使得最后只有一个0或1。其中翻转指1变0，0变1。 做法基本上都是取第一行第一列给他全部翻成0。这个是一定可以办到的。你只需要找1的位置翻掉那一行/列......
  • js 数组join，内容用序号连接
    要将数组内容用序号连接，你可以使用JavaScript的`Array.prototype.map()`方法和`Array.prototype.join()`方法来实现。下面是一个示例代码：```javascriptconstarr=["apple","banana","orange"];constjoinedString=arr.map((item,index)=>`${index+1}.${item}`)......
  • 线程启动、结束，创建线程多法、join，detach
    线程启动、结束，创建线程多法、join，detach视频链接：https://www.bilibili.com/video/BV1Yb411L7ak/?p=4&spm_id_from=333.880.my_history.page.click&vd_source=4c026d3f6b5fac18846e94bc649fd7d0参考博主文章：https://blog.csdn.net/qq_38231713/article/details/106091372join......
  • StringJoiner
    JDK8出现的一个可变的操作字符串的容器，可以高效，方便的拼接字符串。在拼接的时候，可以指定间隔符号，开始符号，结束符号。1publicclassdemo15StringJoiner{2publicstaticvoidmain(String[]args){3int[]arr={1,2,3,4,5,};4StringJoine......
  • PostgreSQL 多表连接
    在关系型数据库中，通常将不同的实体和它们之间的联系存储到多个表中。比如员工的个人信息存储在employees表中，而与部门相关的信息存储在departments表中，同时employees表中存在一个外键字段（department_id），引用了departments表的主键（department_id）。当我们想要查看员工的信息时，通常只......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99