Multi Row Subquery FAQS

1. What Is a Multi-Row Subquery?

A Multi-Row Subquery is a subquery that returns:

• More than one row
• Usually one column

Because it returns multiple values, you cannot use single-row operators (=, >, <).

Instead, you must use multi-row operators.

2. Which Operators Work with Multi-Row Subqueries?

These operators are designed to handle multiple rows:

• IN
• NOT IN
• ANY
• ALL
• EXISTS
• NOT EXISTS

3. What Happens If You Use = with Multi-Row Subquery?

You get this error:

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

Example (Incorrect)

SELECT employee_id

FROM employees

WHERE department_id = (

    SELECT department_id

    FROM departments

    WHERE location_id = 1700

);

If multiple departments exist in location 1700 → error occurs.

4. How Does the IN Operator Work?

IN checks if a value matches any value in the list returned by the subquery.

Example

SELECT employee_id, department_id

FROM employees

WHERE department_id IN (

    SELECT department_id

    FROM departments

    WHERE location_id = 1700

);

Meaning:

Return employees whose department_id matches any department in location 1700.

5. What Is the Difference Between IN and ANY?

IN

Equivalent to:

= ANY

ANY

Compares value with any value returned by subquery.

Example

SELECT employee_id, salary

FROM employees

WHERE salary > ANY (

    SELECT salary

    FROM employees

    WHERE department_id = 10

);

Meaning:

Salary must be greater than at least one salary in department 10.

Equivalent logic:

salary > MIN(salary of dept 10)

6. What Is ALL Operator?

ALL compares against all values returned.

Example

SELECT employee_id, salary

FROM employees

WHERE salary > ALL (

    SELECT salary

    FROM employees

    WHERE department_id = 10

);

Meaning:

Salary must be greater than every salary in department 10.

Equivalent logic:

salary > MAX(salary of dept 10)

7. Difference Between ANY and ALL

Operator	Meaning	Equivalent Aggregate
> ANY	Greater than at least one value	> MIN
> ALL	Greater than all values	> MAX
< ANY	Less than at least one	< MAX
< ALL	Less than all	< MIN

8. What Is EXISTS?

EXISTS checks whether the subquery returns at least one row.

It does not care about values — only row existence.

Example

SELECT e.employee_id

FROM employees e

WHERE EXISTS (

    SELECT 1

    FROM departments d

    WHERE d.department_id = e.department_id

);

Meaning:

Return employees who belong to a department that exists.

9. Difference Between IN and EXISTS

Feature	IN	EXISTS
Compares values	Yes	No
Checks existence	No	Yes
Performance	Good for small result sets	Better for large correlated queries

10. What Is NOT IN?

NOT IN returns rows whose value is not in the subquery result.

Example

SELECT employee_id

FROM employees

WHERE department_id NOT IN (

    SELECT department_id FROM departments

);

11. Important Warning: NOT IN with NULL

If subquery returns even one NULL, then:

• Entire condition becomes UNKNOWN
• Returns no rows

Example:

If departments table contains one NULL department_id → query returns zero rows.

Safer Alternative

Use NOT EXISTS:

SELECT e.employee_id

FROM employees e

WHERE NOT EXISTS (

    SELECT 1

    FROM departments d

    WHERE d.department_id = e.department_id

);

12. Can Multi-Row Subquery Return Multiple Columns?

Yes — but only when used with:

• IN (tuple comparison)
• EXISTS

Example

SELECT *

FROM employees

WHERE (department_id, job_id) IN (

    SELECT department_id, job_id

    FROM job_history

);

This compares column pairs.

13. Correlated Multi-Row Subquery

Executed once per outer row.

Example

SELECT e.employee_id

FROM employees e

WHERE salary > ANY (

    SELECT salary

    FROM employees

    WHERE department_id = e.department_id

);

Inner query runs for each employee row.

14. Performance Considerations

Good performance when:

• Proper indexes exist
• Using EXISTS for large datasets
• Avoiding NOT IN with NULL

Oracle optimizer may rewrite subqueries as joins internally.

Correlated multi-row subqueries may be slower if:

• No index on correlated columns
• Large table scans occur

15. Multi-Row Subquery in HAVING

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

HAVING AVG(salary) > ALL (

    SELECT AVG(salary)

    FROM employees

    GROUP BY department_id

);

Finds department with highest average salary.

16. Real-World Example

Requirement:

Find employees working in departments located in city 1700.

SELECT employee_id

FROM employees

WHERE department_id IN (

    SELECT department_id

    FROM departments

    WHERE location_id = 1700

);

Multi-row because multiple departments can exist in one location.

17. Multi-Row vs Single-Row Subquery

Feature	Single-Row	Multi-Row
Rows Returned	1	Multiple
Operators	=, >, <	IN, ANY, ALL, EXISTS
Error Risk	ORA-01427	No error
Typical Use	Aggregates	List comparison

18. Common Interview Questions

Q1: When do you use ANY vs ALL?

Use ANY when condition should match at least one value.
Use ALL when condition must satisfy every value.

Q2: Why is NOT IN dangerous?

Because NULL in subquery result causes no rows to return.

Q3: Which performs better — IN or EXISTS?

Depends on data size:

• Small static list → IN
• Large correlated subquery → EXISTS

Q4: Can multi-row subquery be used in SELECT clause?

No.
SELECT clause requires scalar (single value) subquery.

19. Best Practices

• Use EXISTS for large correlated queries
• Avoid NOT IN when NULL possible
• Add indexes on correlated columns
• Use ALL/ANY carefully
• Check execution plan
• Understand NULL behavior

20. Short Interview Answer

“A multi-row subquery returns more than one row and is used with operators like IN, ANY, ALL, and EXISTS. Using a single-row operator like = will cause ORA-01427 error. NOT IN should be used carefully because NULL values can affect results.”