1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Why co-related queries?

Discussion in 'SQL PL/SQL' started by learnerSaumya, Apr 25, 2010.

  1. learnerSaumya

    learnerSaumya Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    With the knowledge that I have, I understand that using a co-related query is never a mandate then why should we use co-related queries?

    Thanks,
    Saumya
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Perhaps you may demonstrate that?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How else would you do something like this:

    Code (SQL):
     
    SQL> CREATE TABLE coqry_tst1(id NUMBER,
      2                          id_val varchar2(40),
      3                          id_val2 varchar2(60));
    TABLE created.
    SQL>
    SQL> CREATE TABLE coqry_tst2(id2 NUMBER,
      2                          id_ref  NUMBER,
      3                          id2_desc varchar2(60));
    TABLE created.
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..10000 loop
      3                  INSERT
      4                  INTO coqry_tst1 VALUES (i, 'Test '||i, to_char(to_date(i, 'J'), 'JSP'));
      5                  INSERT
      6                  INTO coqry_tst2 VALUES (i, 2*i, to_char(to_date(i, 'J'), 'JSP'));
      7          END loop;
      8
      9          commit;
     10
     11  END;
     12  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> SELECT id, id_val, id_val2
      2  FROM coqry_tst1;
            ID ID_VAL                                   ID_VAL2
    ---------- ---------------------------------------- ------------------------------------------------------------
           567 Test 567                                 FIVE HUNDRED SIXTY-SEVEN
           568 Test 568                                 FIVE HUNDRED SIXTY-EIGHT
           569 Test 569                                 FIVE HUNDRED SIXTY-NINE
           570 Test 570                                 FIVE HUNDRED SEVENTY
           571 Test 571                                 FIVE HUNDRED SEVENTY-ONE
           572 Test 572                                 FIVE HUNDRED SEVENTY-TWO
           573 Test 573                                 FIVE HUNDRED SEVENTY-THREE
           574 Test 574                                 FIVE HUNDRED SEVENTY-FOUR
           575 Test 575                                 FIVE HUNDRED SEVENTY-FIVE
           576 Test 576                                 FIVE HUNDRED SEVENTY-SIX
           577 Test 577                                 FIVE HUNDRED SEVENTY-SEVEN
    ...
    10000 ROWS selected.
    SQL>
    SQL> UPDATE coqry_tst1
      2  SET id_val2 = (SELECT initcap(id2_desc) FROM coqry_tst2 WHERE id_ref = id);
    10000 ROWS updated.
    SQL> SELECT id, id_val, id_val2
      2  FROM coqry_tst1;
            ID ID_VAL                                   ID_VAL2
    ---------- ---------------------------------------- ------------------------------------------------------------
           567 Test 567
           568 Test 568                                 Two Hundred Eighty-Four
           569 Test 569
           570 Test 570                                 Two Hundred Eighty-Five
           571 Test 571
           572 Test 572                                 Two Hundred Eighty-Six
    ...
    10000 ROWS selected.
    SQL>
     
     
  4. learnerSaumya

    learnerSaumya Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hey Zargon,

    Thanks for the reply. Well, I missed to specify the question is for SELECT statements only.

    Also, I did some more study & here's my elaborated question:
    Co-Related Query yielding Employee's Info earning the least salary in a department,
    SELECT EMP.EMP_NAME
    ,EMP.EMP_SAL
    ,EMP.DEPT_ID
    FROM EMPLOYEE EMP
    WHERE EMP.EMP_SAL IN
    (SELECT MIN(EMP_INR.EMP_SAL) FROM EMPLOYEE EMP_INR WHERE EMP_INR.DEPT_ID = EMP.DEPT_ID GROUP BY EMP_INR.DEPT_ID)

    Sub-Query yielding the same result,
    Co-Related Query yielding Employee's Info earning the least salary in a department,
    SELECT EMP.EMP_NAME
    ,EMP_TMP.SAL
    ,EMP.DEPT_ID
    FROM EMPLOYEE EMP
    ,(SELECT MIN(EMP_INR.EMP_SAL) SAL, EMP_INR.DEPT_ID FROM EMPLOYEE EMP_INR GROUP BY EMP_INR.DEPT_ID) EMP_TMP
    WHERE EMP_TMP.DEPT_ID = EMP.DEPT_ID

    Herein, which is a better way performance-wise?

    Thanks,
    Saumya
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Run both queries with autotrace on and see which plan looks better to you.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    By the Way the queries given above will give you different results.