Which Statements Are True About Single-Row Subqueries in Oracle?
Which three statements are true regarding single row subqueries? (Choose three.)
Community Insight
The question tests where a single-row subquery may be positioned and which clauses accept it, and the trap is option C — the belief that a subquery returning zero rows raises an error.
Single-row subqueries in Oracle SQL follow fixed placement and clause rules: they sit to the right of the comparison operator, a statement may contain several single-row subquery blocks, and they are valid inside the HAVING clause. This page establishes that B, D and E are the three true statements and explains why the 'must return a row' claim is wrong.
Most candidates choose C, assuming that an empty single-row subquery aborts the SQL statement; in reality zero rows simply yield NULL and no error — only a result of more than one row triggers ORA-01427.
Community Discussion (4 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Single-row subqueries are compared against an outer column or expression, and Oracle's documented pattern places the subquery on the right of the comparison operator, for exampleWHERE salary > (SELECT AVG(salary) FROM employees), so option B states the exam's rule correctly. Option D is true because a single SQL statement can nest several independent single-row subquery blocks — one in the WHERE clause and another in the SELECT list or HAVING clause of the same query. Option E is true because aggregate results can be filtered against a single-row subquery, as in HAVING AVG(salary) > (SELECT AVG(salary) FROM employees WHERE department_id = 50). Together, B, D and E describe the placement rule, the repeatability of the construct, and an accepted clause location, which is exactly what the three-answer question asks for.Why the Other Options Are Wrong
Option A is the mirror-image distractor: nothing in Oracle requires a single-row subquery to be placed on the left of the comparison operator, and the exam doctrine always shows the outer column on the left and the subquery on the right. Option C is factually wrong — a single-row subquery that returns no rows does not raise an exception; it produces a NULL comparison so the outer query simply returns no matching rows. The error most often confused with this is ORA-01427 ("single-row subquery returns more than one row"), which belongs to the multi-row case, not the empty case. That distinction is why C reads plausibly but fails the test of actual server behaviour.Community Comment Notes
ArslanAltaf rejects C directly, noting that "if no row is returned, it will not throw an error", which matches the server behaviour described above and supports the D/E half of the verdict. kay000001 lists B among the correct answers, consistent with the right-side placement rule chosen here. salander71 writes "EF are correct. I tried", but the letter F does not exist among this question's five options, so that vote came from a different option set and cannot overturn the B/D/E conclusion for this version. Another learner's "CDF is correct" claim conflicts with the ORA-01427-only error rule and should be treated as a misreading rather than evidence.Official Reference
Exam Strategy
When a question says "Choose three", count your selections before submitting — the source key here listed only two letters, which is an immediate red flag. Eliminate options containing absolute wording such as "must be placed on the left" or "must return a row" by testing them against real Oracle behaviour before you spend time on the remaining candidates.
Frequently Asked Questions
Does a single-row subquery that returns no rows cause an error?
No. Zero rows produce a NULL comparison and no exception; only a result of more than one row raises ORA-01427, which is why option C is false.
Why is option A (subquery on the left of the operator) considered wrong?
Oracle's documented examples and exam doctrine place the subquery on the right of the comparison operator, with the outer column or expression on the left.