(Oracle) Subquery Example

(Oracle) Subquery Example

 

-----------------------------------------------------------------
-- SubQuery_1_Select_Scalar.sql
-- SubQuery 예제 1 : Select 절 Scalar SubQuery
-- SKY 2013-11-15

-----------------------------------------------------------------
SELECT ATB_SAA.AAA,
       ATB_SAA.BBB,
       (SELECT COUNT(ATB_SBB.KKK) AS CNT_KKK
          FROM ATB_SBB
         WHERE ATB_SAA.BBB = ATB_SBB.BBB (+) ) SUBQUERY ,
       ATB_SAA.CCC,
       ATB_SAA.DDD,
       ATB_SAA.EEE
  FROM ATB_SAA
 ORDER BY ATB_SAA.AAA ASC ;
 

-----------------------------------------------------------------
-- SubQuery_2_From_InlineView.sql
-- SubQuery 예제 2 : From 절 Inline View
-- SKY 2013-11-15

-----------------------------------------------------------------
SELECT ATB_SAA.AAA,
       ATB_SAA.BBB,
       SUBQUERY.CNT_KKK,
       ATB_SAA.CCC,
       ATB_SAA.DDD,
       ATB_SAA.EEE
  FROM ATB_SAA,
       (SELECT ATB_SBB.BBB,
               COUNT(ATB_SBB.KKK) AS CNT_KKK
          FROM ATB_SBB
         GROUP BY ATB_SBB.BBB ) SUBQUERY
 WHERE ATB_SAA.BBB = SUBQUERY.BBB (+)
 ORDER BY ATB_SAA.AAA ASC ;
 

-----------------------------------------------------------------
-- SubQuery_3_Where_Nested.sql
-- SubQuery 예제 3 : Where 절 Nested SubQuery
-- SKY 2013-11-15

-----------------------------------------------------------------
SELECT ATB_SAA.AAA,
       ATB_SAA.BBB,
       ATB_SAA.CCC,
       ATB_SAA.DDD,
       ATB_SAA.EEE
  FROM ATB_SAA
 WHERE ATB_SAA.BBB IN (SELECT ATB_SBB.BBB
                         FROM ATB_SBB )
 ORDER BY ATB_SAA.AAA ASC ;

 

 

-- SubQuery 예제 1 : Select 절 Scalar SubQuery

-------------------------[Start Time: 2013-11-15 14:40:30]-------------------------
SQL> SELECT ATB_SAA.AAA,
       ATB_SAA.BBB,
       (SELECT COUNT(ATB_SBB.KKK) AS CNT_KKK
          FROM ATB_SBB
         WHERE ATB_SAA.BBB = ATB_SBB.BBB (+) ) SUBQUERY ,
       ATB_SAA.CCC,
       ATB_SAA.DDD,
       ATB_SAA.EEE
  FROM ATB_SAA
 ORDER BY ATB_SAA.AAA ASC;

AAA    BBB    SUBQUERY         CCC    DDD    EEE      
------ ------ ---------------- ------ ------ ---------
11     KOR                   3 AAAAA       0  12345.12
12     IND                   0 BB456       2  22222.22
13     CHN                   2 BR789     333         3
21     USA                   2 AAAAA      22  77777.22
22     BRA                   0 NZ345     777         
51     RUS                   0 KL678       0         
52     GBR                   0 CCCCC       0      7.88
71     KOR                   3 SE234     555     55.55
72     USA                   2 BU567       0  12345.12
81     NZL                   0 WI890       0         

10 rows selected.

SQL Execution Time > 00:00:00.016
Total Elapsed Time > 00:00:00.032

 

-- SubQuery 예제 2 : From 절 Inline View

-------------------------[Start Time: 2013-11-15 14:40:37]-------------------------
SQL> SELECT ATB_SAA.AAA,
       ATB_SAA.BBB,
       SUBQUERY.CNT_KKK,
       ATB_SAA.CCC,
       ATB_SAA.DDD,
       ATB_SAA.EEE
  FROM ATB_SAA,
       (SELECT ATB_SBB.BBB,
               COUNT(ATB_SBB.KKK) AS CNT_KKK
          FROM ATB_SBB
         GROUP BY ATB_SBB.BBB ) SUBQUERY
 WHERE ATB_SAA.BBB = SUBQUERY.BBB (+)
 ORDER BY ATB_SAA.AAA ASC;

AAA    BBB    CNT_KKK        CCC    DDD    EEE      
------ ------ -------------- ------ ------ ---------
11     KOR                 3 AAAAA       0  12345.12
12     IND                   BB456       2  22222.22
13     CHN                 2 BR789     333         3
21     USA                 2 AAAAA      22  77777.22
22     BRA                   NZ345     777         
51     RUS                   KL678       0         
52     GBR                   CCCCC       0      7.88
71     KOR                 3 SE234     555     55.55
72     USA                 2 BU567       0  12345.12
81     NZL                   WI890       0         

10 rows selected.

SQL Execution Time > 00:00:00.016
Total Elapsed Time > 00:00:00.016

 

-- SubQuery 예제 3 : Where 절 Nested SubQuery

-------------------------[Start Time: 2013-11-15 14:40:41]-------------------------
SQL> SELECT ATB_SAA.AAA,
       ATB_SAA.BBB,
       ATB_SAA.CCC,
       ATB_SAA.DDD,
       ATB_SAA.EEE
  FROM ATB_SAA
 WHERE ATB_SAA.BBB IN (SELECT ATB_SBB.BBB
                         FROM ATB_SBB )
 ORDER BY ATB_SAA.AAA ASC;

AAA    BBB    CCC    DDD    EEE      
------ ------ ------ ------ ---------
11     KOR    AAAAA       0  12345.12
13     CHN    BR789     333         3
21     USA    AAAAA      22  77777.22
71     KOR    SE234     555     55.55
72     USA    BU567       0  12345.12

5 rows selected.

SQL Execution Time > 00:00:00.016
Total Elapsed Time > 00:00:00.031