Single-Row Subquery FAQS

1. What is a Single-Row Subquery in Oracle?

A Single-Row Subquery is a subquery that returns exactly one row and one column.

It is typically used with single-row comparison operators such as:

·        =

·        > 

·        < 

·        >=

·        <=

·        <> 

Example:

SELECT employee_id, salary

FROM employees

WHERE salary > (

    SELECT AVG(salary) FROM employees

);

Here:

·        The inner query returns one value (average salary).

·        The outer query compares each row’s salary to that single value.

2. Why is it called “Single-Row” Subquery?

Because it must return:

·        Only one row

·        Only one column

If it returns:

·        More than one row → Oracle throws error
ORA-01427: single-row subquery returns more than one row

·        More than one column → Oracle throws error
ORA-00913: too many values

3. Where can Single-Row Subqueries be used?

They can appear in:

·        WHERE clause

·        HAVING clause

·        SELECT clause (as scalar subquery)

·        UPDATE statements

·        INSERT statements

4. What operators are used with Single-Row Subqueries?

Only single-row operators:

·        =

·        > 

·        < 

·        >=

·        <=

·        <> 

Incorrect usage example:

WHERE salary = (SELECT salary FROM employees WHERE department_id = 10);

If department 10 has multiple employees → error occurs.

5. Example: Using Aggregate Functions

Single-row subqueries often use aggregate functions because they guarantee one row:

·        AVG()

·        MAX()

·        MIN()

·        SUM()

·        COUNT()

Example:

SELECT *

FROM employees

WHERE salary = (

    SELECT MAX(salary) FROM employees

);

This safely returns one value.

6. What is a Scalar Subquery?

A Scalar Subquery is a type of single-row subquery that:

·        Returns exactly one value

·        Can be used in the SELECT clause

Example:

SELECT employee_id,

       (SELECT department_name

        FROM departments d

        WHERE d.department_id = e.department_id) AS dept_name

FROM employees e;

For each employee, it returns one department name.

7. What happens if a Single-Row Subquery returns no rows?

If the subquery returns no rows, Oracle treats it as:

·        NULL

Example:

WHERE salary > (SELECT salary FROM employees WHERE employee_id = 99999);

If employee 99999 doesn’t exist:

·        Subquery returns NULL

·        Comparison becomes salary > NULL

·        Result → No rows returned

8. What is ORA-01427 error?

Error:

ORA-01427: single-row subquery returns more than one row

Cause:

·        Subquery returns multiple rows.

·        Used with single-row operator like =.

Solution:

·        Use IN instead of =

·        Or ensure subquery returns one row (using aggregation or unique condition)

Example Fix:

WHERE salary IN (

    SELECT salary FROM employees WHERE department_id = 10

);

9. Execution Behavior

Non-Correlated Single-Row Subquery

·        Executed once.

·        Result reused for outer query.

Example:

WHERE salary > (SELECT AVG(salary) FROM employees);

Efficient because calculated once.

Correlated Single-Row Subquery

·        Executed once per outer row.

·        Can be expensive.

Example:

SELECT e.employee_id

FROM employees e

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

);

Here, inner query runs for each department row.

10. Performance Considerations

Good performance when:

·        Subquery is non-correlated

·        Proper indexes exist

·        Subquery uses aggregation

Potential performance issue when:

·        Correlated subquery scans large tables

·        Missing indexes on correlated columns

Optimizer may rewrite subquery as JOIN internally.

11. Single-Row Subquery in UPDATE

Example:

UPDATE employees

SET salary = (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = 10

)

WHERE department_id = 20;

Sets department 20 salary equal to department 10 average.

12. Single-Row Subquery in HAVING

Example:

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

HAVING AVG(salary) > (

    SELECT AVG(salary) FROM employees

);

Compares group average with overall average.

13. Common Mistakes

·        Using = when subquery returns multiple rows

·        Forgetting that no-row return = NULL

·        Using correlated subquery without index

·        Ignoring execution plan

·        Assuming subquery always slower than join

14. Single-Row Subquery vs Multi-Row Subquery

Feature	Single-Row	Multi-Row
Rows returned	1	Multiple
Operators	=, >, <	IN, ANY, ALL
Common use	Aggregates	List comparisons
Error risk	ORA-01427	No error

15. Real-World Example

Requirement:
Find employees earning more than company average.

SELECT employee_id, salary

FROM employees

WHERE salary > (

    SELECT AVG(salary) FROM employees

);

Why Single-Row?

·        AVG() guarantees one value.

16. Interview Tip

If asked:

“What is a single-row subquery?”

Answer:

“A single-row subquery is a subquery that returns exactly one row and one column. It is used with single-row comparison operators such as =, >, <, >=, <=. If it returns multiple rows, Oracle raises ORA-01427 error. Aggregation functions are commonly used to ensure it returns a single value.”

17. Best Practices Summary

·        Use aggregate functions to guarantee one row

·        Ensure uniqueness when using =

·        Check execution plan for correlated subqueries

·        Add indexes on correlated columns

·        Use IN if multiple rows expected

·        Handle NULL carefully

If you'd like, I can next explain:

·        Multi-Row Subqueries in detail

·        Correlated Subqueries deep dive

·        Single-row vs Scalar Subquery difference

·        Execution plan analysis with examples

·        Real interview scenario questions

Just tell me what you want next.