欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle 标量子查询 博客分类: Oracle 开发  

程序员文章站 2024-03-22 13:08:28
...

ORACLE允许在select子句中包含单行子查询, 使用标量子查询可以有效的改善性能,当使用到外部连接,或者使用到了聚合函数,就可以考虑标量子查询的可能性。

下面来看几个例子,来了解标量子查询的用法:

 

1.生成模拟数据

 

CREATE TABLE TEST1(ID INT);
CREATE TABLE TEST2(ID INT);
INSERT INTO TEST1 VALUES(1);
INSERT INTO TEST1 VALUES(2);
INSERT INTO TEST1 VALUES(3);
INSERT INTO TEST2 VALUES(1);
INSERT INTO TEST2 VALUES(2);
COMMIT;

 

 

admin@ORCL> SELECT ID,(SELECT ID FROM TEST2 WHERE TEST1.ID = TEST2.ID) ID2 FROM TEST1;

        ID        ID2
---------- ----------
         1          1
         2          2
         3

从上面的结果可以看出标量子查询的表相当于是外连接中的内表,数据结果等价于下面的sql:

 

admin@ORCL> SELECT * FROM TEST1,TEST2 WHERE TEST1.ID = TEST2.ID(+);

        ID         ID
---------- ----------
         1          1
         2          2
         3

2.

admin@ORCL> SELECT A.USERNAME,
  2         (SELECT COUNT(*) FROM ALL_OBJECTS B WHERE A.USERNAME = B.OWNER) AS OBJ_CNT
  3    FROM ALL_USERS A
  4   WHERE A.USERNAME IN ('DIP', 'BI', 'HR');

USERNAME                          OBJ_CNT
------------------------------ ----------
BI                                       8
DIP                                     0
HR                                     35

 

查询结果等价于

 

admin@ORCL> SELECT A.USERNAME, COUNT(B.OBJECT_ID)
  2    FROM ALL_USERS A, ALL_OBJECTS B
  3   WHERE A.USERNAME = B.OWNER(+)
  4     AND A.USERNAME IN ('DIP', 'BI', 'HR')
  5  GROUP BY A.USERNAME;

USERNAME                       COUNT(B.OBJECT_ID)
------------------------------ ------------------
DIP                                             0
HR                                             35
BI                                                8

 

 

PS:

若把COUNT(B.OBJECT_ID)换成" * " 的话,DIP用户的COUNT值为1,这对于实际结果是不对的。