Subquery question

[farrokhp]

Can somebody with an example tell me how many values could a subquery with a single row comparison operator return and why ?

[chrisrlong]

?

[marist89]

7

[rcherch]

farrokhp-

Are you talking about the exists / not exists subqueries in your where clause?

If it that I believe that answer would be one because in the past when I wasn't unique to one row I had lots of errors generated.

Good Luck

[farrokhp]

In fact this is an OCP question and I think the correct answer is ' 1 ' but I don't know why :( Now can you help more

[rcherch]

I think it is due to the fact your not returning one row or unique row.

I am not an OCP person, and I have found the questions to be interesting.

Another one you will need to know is how the PL/SQL engine processes different PL/SQL blocks of code.

Good Luck

[akkerend]

The subquery can return any number of rows, 0, 1, 10 or 8634785, it depends on the subquery. If it returns more than 1 row you get an error.

Try these queries:

SELECT *
FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE deptno = 0);

SELECT *
FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE deptno = 10);

SELECT *
FROM emp
WHERE deptno = (SELECT deptno FROM dept);

[rcherch]

Akkerend,

I believe your last example is wrong. I think you meant use an "in" instead of "=".

Which then allows more than one row to be returned in the subquery.

[chrisrlong]

That is his point, actually. The aswer to the question, as stated, is *any non-negative number*. As akerand has shown, a SQL statement with a sub-query and 'single row comparison operator' (=) can return *any* number of rows. however, if that number is greater than 1, then an error is generated. If the question had included the phrase 'without error', or something similar, then the answer would have been 1 or 0. See, the question is still not specific enough to only have a single numeric answer.

- Chris