在数据库日新月异的时代，不实际跑跑，谁快谁慢还真不好说。
有这么一个员工表：
create table tb_emp03( id number(8,0) primary key, name nvarchar2(60), salary number(5,0), deptno number(2,0) )
可以这么填充数据：
Insert into tb_emp03 select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(1000,50000),dbms_random.value(0,10) from dual connect by level<=2000000 order by dbms_random.random
还有这么一个部门表：
create table tb_dept03( id number(5,0) primary key, name nvarchar2(20) )
它的数据是定死的：
insert into tb_dept03(id,name) values('1','sales'); insert into tb_dept03(id,name) values('2','manage'); insert into tb_dept03(id,name) values('3','postsale'); insert into tb_dept03(id,name) values('4','dev'); insert into tb_dept03(id,name) values('5','manu'); insert into tb_dept03(id,name) values('6','maintenance'); insert into tb_dept03(id,name) values('7','fix'); insert into tb_dept03(id,name) values('8','counter'); insert into tb_dept03(id,name) values('9','sweeper'); insert into tb_dept03(id,name) values('10','cleaner');
然后，做个带标量子查询的sql，目的是把员工的部门名称秀出来：
select emp.id,emp.name,emp.salary,emp.deptno,(select dept.name from tb_dept03 dept where dept.id=emp.deptno) as deptname from tb_emp03 emp where emp.salary>49990 已选择381行。 已用时间: 00: 00: 00.40
用时0.4秒。
而把上面的SQL改成左联结再跑：
select emp.id,emp.name,emp.salary,emp.deptno,dept.name from tb_emp03 emp left join tb_dept03 dept on emp.deptno=dept.id where emp.salary>49990 已选择381行。 已用时间: 00: 00: 00.40
发现运行时间两者几乎一样啊。
再跑跑两者的解释计划：
SQL> select emp.id,emp.name,emp.salary,emp.deptno,(select dept.name from tb_dept03 dept where dept.id=emp.deptno) as deptname from tb_emp03 emp where emp.salary>49990; 已用时间: 00: 00: 00.00 执行计划 ---------------------------------------------------------- Plan hash value: 84985261 -------------------------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------- ------------ | 0 | SELECT STATEMENT | | 418 | 42218 | 3609 (2) | 00:00:44 | | 1 | TABLE ACCESS BY INDEX ROWID| TB_DEPT03 | 1 | 35 | 1 (0) | 00:00:01 | |* 2 | INDEX UNIQUE SCAN | SYS_C0011427 | 1 | | 1 (0) | 00:00:01 | |* 3 | TABLE ACCESS FULL | TB_EMP03 | 418 | 42218 | 3609 (2) | 00:00:44 | -------------------------------------------------------------------------------- ------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPT"."ID"=:B1) 3 - filter("EMP"."SALARY">49990) Note ----- - dynamic sampling used for this statement (level=2) SQL>
Cost是3609
再看看左联结的：
SQL> select emp.id,emp.name,emp.salary,emp.deptno,dept.name from tb_emp03 emp left join tb_dept03 dept on emp.deptno=dept.id where emp.salary>49990; 已用时间: 00: 00: 00.00 执行计划 ---------------------------------------------------------- Plan hash value: 3854866026 -------------------------------------------------------------------------------- --- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- --- | 0 | SELECT STATEMENT | | 418 | 56848 | 3612 (2)| 00:00:4 4 | |* 1 | HASH JOIN RIGHT OUTER| | 418 | 56848 | 3612 (2)| 00:00:4 4 | | 2 | TABLE ACCESS FULL | TB_DEPT03 | 10 | 350 | 3 (0)| 00:00:0 1 | |* 3 | TABLE ACCESS FULL | TB_EMP03 | 418 | 42218 | 3609 (2)| 00:00:4 4 | -------------------------------------------------------------------------------- --- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("EMP"."DEPTNO"="DEPT"."ID"(+)) 3 - filter("EMP"."SALARY">49990) Note ----- - dynamic sampling used for this statement (level=2) SQL>
Cost是3612，和标量子查询也差不多。
到这里，可以得出结论了，标量子查询不是肯定慢的，左联结也未必快了。
在DB优化器越做越好的今天，很多以前的理念都不能墨守成规了。
--2020-01-27--