当一个子查询介于select 与from之间，这种子查询就叫标量子查询。

select e.ename,e.sal,(select d.dname from dept d where d.deptno = e.deptno) dnamefrom emp e;

scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5123bdvqcrffd, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ e.ename,        e.sal,
(select d.dname from dept d where d.deptno = e.deptno) dname   from emp
e

Plan hash value: 2981343222

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |     14 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      3 |00:00:00.01 |       5 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |      3 |00:00:00.01 |       2 |
|   3 |  TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPTNO"=:B1)

22 rows selected.

scott@orclpdb1:orclcdb>

尽量避免使用标量子查询，假如主表示返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能。如果主表数据量小，或者主表的连接列基数很低，那么这个时候我们也可以使用标量子查询，但要记得要给子查询中表的连接列建立索引。

Q: 如何优化SQL中标量子查询

可以将标量子查询等价改写为外连接，从而使它们可以进行HASH连接。

Q:为什么要将标量子查询改写为外连接而不是内连接呢？

因为标量子查询是一个传值的过程，如果主表传值给子查询，子查询没有查询到数据，这个时候会显示NULL。如果将标量子查询改写为内连接，会丢失没有关联上的数据。

scott@orclpdb1:orclcdb> select d.dname,2         d.loc,(select max(e.sal) from emp e where e.deptno = d.deptno) max_sal4    from dept d;DNAME          LOC              MAX_SAL
-------------- ------------- ----------
ACCOUNTING     NEW YORK            5000
RESEARCH       DALLAS              3000
SALES          CHICAGO             2850
OPERATIONS     BOSTON4 rows selected.scott@orclpdb1:orclcdb> select d.dname, d.loc, e.max_sal2    from dept dleft join (select max(sal) max_sal, deptno from emp group by deptno) e4      on d.deptno = e.deptno;DNAME          LOC              MAX_SAL
-------------- ------------- ----------
ACCOUNTING     NEW YORK            5000
RESEARCH       DALLAS              3000
SALES          CHICAGO             2850
OPERATIONS     BOSTON4 rows selected.scott@orclpdb1:orclcdb>