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!

Subquery and Main query

Discussion in 'SQL PL/SQL' started by Bhavna, Oct 4, 2012.

  1. Bhavna

    Bhavna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Only one column or expression can be compared between the subquery and main query.
    True or False?
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yes, Its true. Only one column or expression can be compared between the subquery and main query.
     
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260

    Maybe I didn't understand the question correctly, but what do you mean exactly that only one column or expression can be compared with the parent query?


    Consider the following example:

    Code (Text):

    CREATE TABLE films
    (
        title        VARCHAR2(50)        NOT NULL,
        director     VARCHAR2(50)        NOT NULL,
        producer     VARCHAR2(50)        NOT NULL
    );
    ALTER TABLE films ADD CONSTRAINT FILMS_PK PRIMARY KEY(title, director);


    CREATE TABLE viewed_films
    (
        spectator    VARCHAR2(50)        NOT NULL,
        title        VARCHAR2(50)        NOT NULL,
        director     VARCHAR2(50)        NOT NULL
    );
    ALTER TABLE viewed_films ADD CONSTRAINT VIEWED_FILMS_PK PRIMARY KEY(spectator, title);
    ALTER TABLE viewed_films ADD CONSTRAINT VIEWED_FILMS_FK1 FOREIGN KEY(title, director) REFERENCES films(title, director) ON DELETE CASCADE;



    INSERT ALL
      INTO films(title, director, producer) VALUES ('t1', 'd1', 'p1')
      INTO films(title, director, producer) VALUES ('t2', 'd1', 'p1')
      INTO films(title, director, producer) VALUES ('t3', 'd1', 'p2')
      INTO films(title, director, producer) VALUES ('t4', 'd2', 'p2')
      INTO films(title, director, producer) VALUES ('t5', 'd3', 'p3')
    SELECT * FROM DUAL;


    INSERT ALL
      INTO viewed_films(spectator, title, director) VALUES ('s1', 't1', 'd1')
      INTO viewed_films(spectator, title, director) VALUES ('s1', 't2', 'd1')
      INTO viewed_films(spectator, title, director) VALUES ('s1', 't3', 'd1')
      INTO viewed_films(spectator, title, director) VALUES ('s1', 't4', 'd2')
      INTO viewed_films(spectator, title, director) VALUES ('s1', 't5', 'd3')
      INTO viewed_films(spectator, title, director) VALUES ('s2', 't5', 'd3')
      INTO viewed_films(spectator, title, director) VALUES ('s3', 't4', 'd2')
      INTO viewed_films(spectator, title, director) VALUES ('s4', 't3', 'd1')
      INTO viewed_films(spectator, title, director) VALUES ('s4', 't4', 'd2')
      INTO viewed_films(spectator, title, director) VALUES ('s5', 't1', 'd1')
      INTO viewed_films(spectator, title, director) VALUES ('s5', 't2', 'd1')
      INTO viewed_films(spectator, title, director) VALUES ('s5', 't3', 'd1')
      INTO viewed_films(spectator, title, director) VALUES ('s5', 't4', 'd2')
      INTO viewed_films(spectator, title, director) VALUES ('s5', 't5', 'd3')
    SELECT * FROM DUAL;
     

    Now, suppose that we wish to have the list of people alongside with the films they viewed. Well, all I need to do is to write an INNER JOIN query, something like this:

    Code (Text):

    SELECT t1.title, t1.director, t1.producer, t2.spectator
    FROM films t1 INNER JOIN viewed_films t2
      ON (t1.title = t2.title) AND (t1.director = t2.director)
    ORDER BY t1.title, t1.director, t1.producer, t2.spectator;
     

    The same join query can be written as a correlated subquery

    Code (Text):

    SELECT  t1.title,
                t1.director,
                t1.spectator,
               (SELECT t2.producer
                FROM films t2
                WHERE t1.title=t2.title AND t1.director=t2.director)
    FROM viewed_films t1;
     

    As we can see in the WHERE clause "WHERE t1.title=t2.title AND t1.director=t2.director" we compare two columns title and director with those of the parent query.


    Regards,
    Dariyoosh
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Yes.. Multiple columns could be compared between main query and subquery.
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Raj,

    Can you please provide some example here..
     
  6. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    I gave you the example.
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Can you please explain about correlated queries? Iam bit confused with it..

    The Inner query is not as subquery in your example right!!
     
  8. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Ex:
    Code (SQL):
    SELECT * FROM po_distributions_all pda WHERE (pda.po_header_id,pda.po_line_id) = (SELECT po_header_id,po_line_id FROM po_lines_all WHERE po_header_id = 1001)
    if we have more than one line items

    Code (SQL):
    SELECT * FROM po_distributions_all pda WHERE (pda.po_header_id,pda.po_line_id) IN (SELECT po_header_id,po_line_id FROM po_lines_all WHERE po_header_id = 1001)
    We can compare with two values also
     
  9. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yeah, Thanks Kiran. Looking for this kind of example.
     
  10. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    What do you mean ?
     
  11. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    I mean does inner query relates to subquery?
     
  12. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    The inner query (which is the SELECT inside the pair of parenthesis) for me is a subquery.


    Code (Text):

    SELECT  t1.title,
                t1.director,
                t1.spectator,
               (SELECT t2.producer
                FROM films t2
                WHERE t1.title=t2.title AND t1.director=t2.director)
    FROM viewed_films t1;
     
    The parent query table has the alias t1 and the subquery has the alias t2.

    As you can see in the WHERE clause of the subquery I refer to 2 columns of the parent table t1.title and t1.director



    Regards,
    Dariyoosh
     
  13. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    From Oracle documentation....

     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is an example of a correlated subquery; notice that the subquery (in parenthesis) references a value from the main select statement:

    Code (SQL):
    SQL> CREATE TABLE main(
      2          id      NUMBER,
      3          val     varchar2(20),
      4          person  varchar2(20),
      5          dt      DATE
      6  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE corr(
      2          id      NUMBER,
      3          val     varchar2(20)
      4  );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000 loop
      3                  INSERT INTO main(id, dt)
      4                  VALUES(i, sysdate-i);
      5                  INSERT INTO corr(id)
      6                  VALUES(i);
      7          END loop;
      8
      9          commit;
     10
     11  END;
     12  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> UPDATE main
      2  SET person='Florpneester', val='Orpenflorp'
      3  WHERE MOD(id, 5) <4;
     
    800 ROWS updated.
     
    SQL>
    SQL> UPDATE main
      2  SET person='Plerpnuuster', val='Arpenflarp'
      3  WHERE MOD(id, 5) >=4;
     
    200 ROWS updated.
     
    SQL>
    SQL> UPDATE corr
      2  SET val='Arpenflarp';
     
    1000 ROWS updated.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT id, val, person, dt
      2  FROM main
      3  WHERE (id, val) IN (SELECT id, val FROM corr WHERE id = main.id);
     
            ID VAL                  PERSON               DT
    ---------- -------------------- -------------------- ---------
             4 Arpenflarp           Plerpnuuster         30-SEP-12
            24 Arpenflarp           Plerpnuuster         10-SEP-12
            44 Arpenflarp           Plerpnuuster         21-AUG-12
            64 Arpenflarp           Plerpnuuster         01-AUG-12
            89 Arpenflarp           Plerpnuuster         07-JUL-12
           109 Arpenflarp           Plerpnuuster         17-JUN-12
           124 Arpenflarp           Plerpnuuster         02-JUN-12
           144 Arpenflarp           Plerpnuuster         13-MAY-12
           164 Arpenflarp           Plerpnuuster         23-APR-12
           184 Arpenflarp           Plerpnuuster         03-APR-12
           199 Arpenflarp           Plerpnuuster         19-MAR-12
           219 Arpenflarp           Plerpnuuster         28-FEB-12
           239 Arpenflarp           Plerpnuuster         08-FEB-12
           259 Arpenflarp           Plerpnuuster         19-JAN-12
           274 Arpenflarp           Plerpnuuster         04-JAN-12
           294 Arpenflarp           Plerpnuuster         15-DEC-11
           314 Arpenflarp           Plerpnuuster         25-NOV-11
           334 Arpenflarp           Plerpnuuster         05-NOV-11
           349 Arpenflarp           Plerpnuuster         21-OCT-11
           369 Arpenflarp           Plerpnuuster         01-OCT-11
           394 Arpenflarp           Plerpnuuster         06-SEP-11
           414 Arpenflarp           Plerpnuuster         17-AUG-11
           434 Arpenflarp           Plerpnuuster         28-JUL-11
           454 Arpenflarp           Plerpnuuster         08-JUL-11
           469 Arpenflarp           Plerpnuuster         23-JUN-11
           489 Arpenflarp           Plerpnuuster         03-JUN-11
           509 Arpenflarp           Plerpnuuster         14-MAY-11
           529 Arpenflarp           Plerpnuuster         24-APR-11
           544 Arpenflarp           Plerpnuuster         09-APR-11
           564 Arpenflarp           Plerpnuuster         20-MAR-11
           584 Arpenflarp           Plerpnuuster         28-FEB-11
           604 Arpenflarp           Plerpnuuster         08-FEB-11
           619 Arpenflarp           Plerpnuuster         24-JAN-11
           639 Arpenflarp           Plerpnuuster         04-JAN-11
           659 Arpenflarp           Plerpnuuster         15-DEC-10
           679 Arpenflarp           Plerpnuuster         25-NOV-10
           694 Arpenflarp           Plerpnuuster         10-NOV-10
           714 Arpenflarp           Plerpnuuster         21-OCT-10
           734 Arpenflarp           Plerpnuuster         01-OCT-10
           754 Arpenflarp           Plerpnuuster         11-SEP-10
           769 Arpenflarp           Plerpnuuster         27-AUG-10
           779 Arpenflarp           Plerpnuuster         17-AUG-10
           799 Arpenflarp           Plerpnuuster         28-JUL-10
           819 Arpenflarp           Plerpnuuster         08-JUL-10
           834 Arpenflarp           Plerpnuuster         23-JUN-10
           854 Arpenflarp           Plerpnuuster         03-JUN-10
           874 Arpenflarp           Plerpnuuster         14-MAY-10
           894 Arpenflarp           Plerpnuuster         24-APR-10
           909 Arpenflarp           Plerpnuuster         09-APR-10
           929 Arpenflarp           Plerpnuuster         20-MAR-10
           949 Arpenflarp           Plerpnuuster         28-FEB-10
           969 Arpenflarp           Plerpnuuster         08-FEB-10
           984 Arpenflarp           Plerpnuuster         24-JAN-10
             9 Arpenflarp           Plerpnuuster         25-SEP-12
            14 Arpenflarp           Plerpnuuster         20-SEP-12
            19 Arpenflarp           Plerpnuuster         15-SEP-12
            29 Arpenflarp           Plerpnuuster         05-SEP-12
            34 Arpenflarp           Plerpnuuster         31-AUG-12
            39 Arpenflarp           Plerpnuuster         26-AUG-12
            49 Arpenflarp           Plerpnuuster         16-AUG-12
            54 Arpenflarp           Plerpnuuster         11-AUG-12
            59 Arpenflarp           Plerpnuuster         06-AUG-12
            69 Arpenflarp           Plerpnuuster         27-JUL-12
            74 Arpenflarp           Plerpnuuster         22-JUL-12
            79 Arpenflarp           Plerpnuuster         17-JUL-12
            84 Arpenflarp           Plerpnuuster         12-JUL-12
            94 Arpenflarp           Plerpnuuster         02-JUL-12
            99 Arpenflarp           Plerpnuuster         27-JUN-12
           104 Arpenflarp           Plerpnuuster         22-JUN-12
           114 Arpenflarp           Plerpnuuster         12-JUN-12
           119 Arpenflarp           Plerpnuuster         07-JUN-12
           129 Arpenflarp           Plerpnuuster         28-MAY-12
           134 Arpenflarp           Plerpnuuster         23-MAY-12
           139 Arpenflarp           Plerpnuuster         18-MAY-12
           149 Arpenflarp           Plerpnuuster         08-MAY-12
           154 Arpenflarp           Plerpnuuster         03-MAY-12
           159 Arpenflarp           Plerpnuuster         28-APR-12
           169 Arpenflarp           Plerpnuuster         18-APR-12
           174 Arpenflarp           Plerpnuuster         13-APR-12
           179 Arpenflarp           Plerpnuuster         08-APR-12
           189 Arpenflarp           Plerpnuuster         29-MAR-12
           194 Arpenflarp           Plerpnuuster         24-MAR-12
           204 Arpenflarp           Plerpnuuster         14-MAR-12
           209 Arpenflarp           Plerpnuuster         09-MAR-12
           214 Arpenflarp           Plerpnuuster         04-MAR-12
           224 Arpenflarp           Plerpnuuster         23-FEB-12
           229 Arpenflarp           Plerpnuuster         18-FEB-12
           234 Arpenflarp           Plerpnuuster         13-FEB-12
           244 Arpenflarp           Plerpnuuster         03-FEB-12
           249 Arpenflarp           Plerpnuuster         29-JAN-12
           254 Arpenflarp           Plerpnuuster         24-JAN-12
           264 Arpenflarp           Plerpnuuster         14-JAN-12
           269 Arpenflarp           Plerpnuuster         09-JAN-12
           279 Arpenflarp           Plerpnuuster         30-DEC-11
           284 Arpenflarp           Plerpnuuster         25-DEC-11
           289 Arpenflarp           Plerpnuuster         20-DEC-11
           299 Arpenflarp           Plerpnuuster         10-DEC-11
           304 Arpenflarp           Plerpnuuster         05-DEC-11
           309 Arpenflarp           Plerpnuuster         30-NOV-11
           319 Arpenflarp           Plerpnuuster         20-NOV-11
           324 Arpenflarp           Plerpnuuster         15-NOV-11
           329 Arpenflarp           Plerpnuuster         10-NOV-11
           339 Arpenflarp           Plerpnuuster         31-OCT-11
           344 Arpenflarp           Plerpnuuster         26-OCT-11
           354 Arpenflarp           Plerpnuuster         16-OCT-11
           359 Arpenflarp           Plerpnuuster         11-OCT-11
           364 Arpenflarp           Plerpnuuster         06-OCT-11
           374 Arpenflarp           Plerpnuuster         26-SEP-11
           379 Arpenflarp           Plerpnuuster         21-SEP-11
           384 Arpenflarp           Plerpnuuster         16-SEP-11
           389 Arpenflarp           Plerpnuuster         11-SEP-11
           399 Arpenflarp           Plerpnuuster         01-SEP-11
           404 Arpenflarp           Plerpnuuster         27-AUG-11
           409 Arpenflarp           Plerpnuuster         22-AUG-11
           419 Arpenflarp           Plerpnuuster         12-AUG-11
           424 Arpenflarp           Plerpnuuster         07-AUG-11
           429 Arpenflarp           Plerpnuuster         02-AUG-11
           439 Arpenflarp           Plerpnuuster         23-JUL-11
           444 Arpenflarp           Plerpnuuster         18-JUL-11
           449 Arpenflarp           Plerpnuuster         13-JUL-11
           459 Arpenflarp           Plerpnuuster         03-JUL-11
           464 Arpenflarp           Plerpnuuster         28-JUN-11
           474 Arpenflarp           Plerpnuuster         18-JUN-11
           479 Arpenflarp           Plerpnuuster         13-JUN-11
           484 Arpenflarp           Plerpnuuster         08-JUN-11
           494 Arpenflarp           Plerpnuuster         29-MAY-11
           499 Arpenflarp           Plerpnuuster         24-MAY-11
           504 Arpenflarp           Plerpnuuster         19-MAY-11
           514 Arpenflarp           Plerpnuuster         09-MAY-11
           519 Arpenflarp           Plerpnuuster         04-MAY-11
           524 Arpenflarp           Plerpnuuster         29-APR-11
           534 Arpenflarp           Plerpnuuster         19-APR-11
           539 Arpenflarp           Plerpnuuster         14-APR-11
           549 Arpenflarp           Plerpnuuster         04-APR-11
           554 Arpenflarp           Plerpnuuster         30-MAR-11
           559 Arpenflarp           Plerpnuuster         25-MAR-11
           569 Arpenflarp           Plerpnuuster         15-MAR-11
           574 Arpenflarp           Plerpnuuster         10-MAR-11
           579 Arpenflarp           Plerpnuuster         05-MAR-11
           589 Arpenflarp           Plerpnuuster         23-FEB-11
           594 Arpenflarp           Plerpnuuster         18-FEB-11
           599 Arpenflarp           Plerpnuuster         13-FEB-11
           609 Arpenflarp           Plerpnuuster         03-FEB-11
           614 Arpenflarp           Plerpnuuster         29-JAN-11
           624 Arpenflarp           Plerpnuuster         19-JAN-11
           629 Arpenflarp           Plerpnuuster         14-JAN-11
           634 Arpenflarp           Plerpnuuster         09-JAN-11
           644 Arpenflarp           Plerpnuuster         30-DEC-10
           649 Arpenflarp           Plerpnuuster         25-DEC-10
           654 Arpenflarp           Plerpnuuster         20-DEC-10
           664 Arpenflarp           Plerpnuuster         10-DEC-10
           669 Arpenflarp           Plerpnuuster         05-DEC-10
           674 Arpenflarp           Plerpnuuster         30-NOV-10
           684 Arpenflarp           Plerpnuuster         20-NOV-10
           689 Arpenflarp           Plerpnuuster         15-NOV-10
           699 Arpenflarp           Plerpnuuster         05-NOV-10
           704 Arpenflarp           Plerpnuuster         31-OCT-10
           709 Arpenflarp           Plerpnuuster         26-OCT-10
           719 Arpenflarp           Plerpnuuster         16-OCT-10
           724 Arpenflarp           Plerpnuuster         11-OCT-10
           729 Arpenflarp           Plerpnuuster         06-OCT-10
           739 Arpenflarp           Plerpnuuster         26-SEP-10
           744 Arpenflarp           Plerpnuuster         21-SEP-10
           749 Arpenflarp           Plerpnuuster         16-SEP-10
           759 Arpenflarp           Plerpnuuster         06-SEP-10
           764 Arpenflarp           Plerpnuuster         01-SEP-10
           774 Arpenflarp           Plerpnuuster         22-AUG-10
           784 Arpenflarp           Plerpnuuster         12-AUG-10
           789 Arpenflarp           Plerpnuuster         07-AUG-10
           794 Arpenflarp           Plerpnuuster         02-AUG-10
           804 Arpenflarp           Plerpnuuster         23-JUL-10
           809 Arpenflarp           Plerpnuuster         18-JUL-10
           814 Arpenflarp           Plerpnuuster         13-JUL-10
           824 Arpenflarp           Plerpnuuster         03-JUL-10
           829 Arpenflarp           Plerpnuuster         28-JUN-10
           839 Arpenflarp           Plerpnuuster         18-JUN-10
           844 Arpenflarp           Plerpnuuster         13-JUN-10
           849 Arpenflarp           Plerpnuuster         08-JUN-10
           859 Arpenflarp           Plerpnuuster         29-MAY-10
           864 Arpenflarp           Plerpnuuster         24-MAY-10
           869 Arpenflarp           Plerpnuuster         19-MAY-10
           879 Arpenflarp           Plerpnuuster         09-MAY-10
           884 Arpenflarp           Plerpnuuster         04-MAY-10
           889 Arpenflarp           Plerpnuuster         29-APR-10
           899 Arpenflarp           Plerpnuuster         19-APR-10
           904 Arpenflarp           Plerpnuuster         14-APR-10
           914 Arpenflarp           Plerpnuuster         04-APR-10
           919 Arpenflarp           Plerpnuuster         30-MAR-10
           924 Arpenflarp           Plerpnuuster         25-MAR-10
           934 Arpenflarp           Plerpnuuster         15-MAR-10
           939 Arpenflarp           Plerpnuuster         10-MAR-10
           944 Arpenflarp           Plerpnuuster         05-MAR-10
           954 Arpenflarp           Plerpnuuster         23-FEB-10
           959 Arpenflarp           Plerpnuuster         18-FEB-10
           964 Arpenflarp           Plerpnuuster         13-FEB-10
           974 Arpenflarp           Plerpnuuster         03-FEB-10
           979 Arpenflarp           Plerpnuuster         29-JAN-10
           989 Arpenflarp           Plerpnuuster         19-JAN-10
           994 Arpenflarp           Plerpnuuster         14-JAN-10
           999 Arpenflarp           Plerpnuuster         09-JAN-10
     
    200 ROWS selected.
     
    SQL>
     
  15. Bhavna

    Bhavna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanks all!

    I am preparing for the 1z0-007 exam and have some conceptual issues with sub-queries. This was a lot of help :)