Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Unanswered: Question about SubSelects

    Hi all,

    I happen to have an oracle quiz to answer at school and I completed it except this one very question:

    If used in a WHERE clause, what is the maximum number of rows that can be returned by a SUB SELECT? Why?
    I just don't know what to answer. I'd say there is no maximum, but I can't find anything confirming that anywhere, and I have no idea how to answer the "why".

    Your thoughts would be much appreciated!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Rhetorical question -
    can sub-select return more rows than exist within complete result set?
    Please justify your response.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Depends on the WHERE condition. Look at this:
    SELECT some_fields
    FROM some_table
    WHERE a_field = (sub_SELECT)

    Then look at ORA-01427
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Heh, are you saying that a subquery can return too many rows?

  5. #5
    Join Date
    Dec 2011
    Posts
    3

    Red face

    Quote Originally Posted by aflorin27 View Post
    Depends on the WHERE condition. Look at this:
    SELECT some_fields
    FROM some_table
    WHERE a_field = (sub_SELECT)

    Then look at ORA-01427
    Yeah I understand it's because of the "=" which means we're only allowed one row in this case.

    But what if it was WHERE a_field in (sub_SELECT) ?

    I mean, the sub select can return a full table so how do I know what is the maximum number of rows returned without any clue on the WHERE clause?

    That's why I'm confused by this question :/

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    So you got the answer: it depends on the condition.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Dec 2011
    Posts
    3
    I got all the points on this question thx guys

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •