数据库哪种查询效率高? 数据库多表查询和嵌套查询哪个效率高?今天老大看我写的SQL,让我不要用嵌套查询。我认为嵌套查能更高效,老大说这样没效率,而且不好懂。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 什么事情都是具体问题具体分析的物竞天择 如果能确定哪一种效率高 另一种方法就该被淘汰了既然能够并存 就说明 存在的就是合理的我的理解是 如果两个表是通过某一个主键或索引相关联,那么就可以用多表关联,这样可能比嵌套查询效率高,而且嵌套的话,sql看起来也不简洁明了 哦,不好意思,是我的概念错误了,不应该叫嵌套查询我说的是这种情况,要不要把所有表都放在一个FROM里?下面两种写法哪种好?--1select t1.*, t2.*, t3.*from t1,t2,t3where t1.c1 = t2.c1 and t2.c2 = t3.c2;--2select t_a.*, t_3.* from (select t1.* t2.* from t1, t2 where t1.c1 = t2.c1) t_awhere t3.c2 = t_a.c2; --这两种的执行计划是一样的select t.*,sg.*from (select emp.empno,emp.sal,dept.deptno,dept.dnamefrom emp,deptwhere emp.deptno=dept.deptno) t, salgrade sgwhere t.sal=sg.sal EMPNO SAL DEPTNO DNAME SAL SALLEVEL---------- ---------- ---------- -------------- ---------- ---------- 7369 800 20 RESEARCH 800 least 7788 3000 20 RESEARCH 3000 middle 7839 5000 10 ACCOUNTING 5000 high 7902 3000 20 RESEARCH 3000 middleExecution Plan----------------------------------------------------------Plan hash value: 802173295----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 176 | 8 (13)| 00:00:01 || 1 | NESTED LOOPS | | 4 | 176 | 8 (13)| 00:00:01 ||* 2 | HASH JOIN | | 4 | 124 | 7 (15)| 00:00:01 || 3 | TABLE ACCESS FULL | SALGRADE | 3 | 60 | 3 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 | 1 | | 0 (0)| 00:0----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("EMP"."SAL"="SALGRADE"."SAL") 4 - filter("EMP"."SAL" IS NOT NULL) 6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note----- - dynamic sampling used for this statement select emp.empno,emp.sal,dept.deptno,dept.dname,salgrade.sal,salgrade.sallevel from emp,dept,salgrade where emp.deptno=dept.deptno and emp.sal=salgrade.sal EMPNO SAL DEPTNO DNAME SAL SALLEVEL---------- ---------- ---------- -------------- ---------- ---------- 7369 800 20 RESEARCH 800 least 7788 3000 20 RESEARCH 3000 middle 7839 5000 10 ACCOUNTING 5000 high 7902 3000 20 RESEARCH 3000 middleExecution Plan----------------------------------------------------------Plan hash value: 802173295----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 176 | 8 (13)| 00:00:01 || 1 | NESTED LOOPS | | 4 | 176 | 8 (13)| 00:00:01 ||* 2 | HASH JOIN | | 4 | 124 | 7 (15)| 00:00:01 || 3 | TABLE ACCESS FULL | SALGRADE | 3 | 60 | 3 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 | 1 | | 0 (0)| 00:0----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("EMP"."SAL"="SALGRADE"."SAL") 4 - filter("EMP"."SAL" IS NOT NULL) 6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note----- - dynamic sampling used for this statement 求一条sql语句 一个复杂的SQL拼接问题, 请教 oracle存储过程的动态游标出错 ORACLE 执行作业出现的问题 数据导入问题????? 请版主帮忙回答一下 如何导出oracle数据库中表的结构(字段名称,类型等) 极其简单的时间插入问题!! Oracle从AIX迁移到Linux方案请教 orcle导入dmp数据报错!重新把自己的本地数据库改为zhs16gbk字符集重新导入依然会报错! 关于一个SQL语句group by的实现 oracle current 关键字用法
物竞天择 如果能确定哪一种效率高 另一种方法就该被淘汰了
既然能够并存 就说明 存在的就是合理的我的理解是 如果两个表是通过某一个主键或索引相关联,那么就可以用多表关联,这样可能比嵌套查询效率高,而且嵌套的话,sql看起来也不简洁明了
下面两种写法哪种好?
--1
select t1.*, t2.*, t3.*
from t1,t2,t3
where t1.c1 = t2.c1
and t2.c2 = t3.c2;
--2
select t_a.*, t_3.*
from
(select t1.* t2.*
from t1, t2
where t1.c1 = t2.c1) t_a
where t3.c2 = t_a.c2;
select t.*,sg.*
from (select emp.empno,emp.sal,dept.deptno,dept.dname
from emp,dept
where emp.deptno=dept.deptno) t, salgrade sg
where t.sal=sg.sal EMPNO SAL DEPTNO DNAME SAL SALLEVEL
---------- ---------- ---------- -------------- ---------- ----------
7369 800 20 RESEARCH 800 least
7788 3000 20 RESEARCH 3000 middle
7839 5000 10 ACCOUNTING 5000 high
7902 3000 20 RESEARCH 3000 middle
Execution Plan
----------------------------------------------------------
Plan hash value: 802173295----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 176 | 8 (13)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 176 | 8 (13)| 00:00:01 |
|* 2 | HASH JOIN | | 4 | 124 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | SALGRADE | 3 | 60 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 | 1 | | 0 (0)| 00:0
----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("EMP"."SAL"="SALGRADE"."SAL")
4 - filter("EMP"."SAL" IS NOT NULL)
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note
-----
- dynamic sampling used for this statement select emp.empno,emp.sal,dept.deptno,dept.dname,salgrade.sal,salgrade.sallevel
from emp,dept,salgrade
where emp.deptno=dept.deptno and emp.sal=salgrade.sal EMPNO SAL DEPTNO DNAME SAL SALLEVEL
---------- ---------- ---------- -------------- ---------- ----------
7369 800 20 RESEARCH 800 least
7788 3000 20 RESEARCH 3000 middle
7839 5000 10 ACCOUNTING 5000 high
7902 3000 20 RESEARCH 3000 middle
Execution Plan
----------------------------------------------------------
Plan hash value: 802173295----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 176 | 8 (13)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 176 | 8 (13)| 00:00:01 |
|* 2 | HASH JOIN | | 4 | 124 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | SALGRADE | 3 | 60 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 | 1 | | 0 (0)| 00:0
----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("EMP"."SAL"="SALGRADE"."SAL")
4 - filter("EMP"."SAL" IS NOT NULL)
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note
-----
- dynamic sampling used for this statement