Scalar Subquery FAQS

1. What Is a Scalar Subquery?

A Scalar Subquery is a subquery that:

• Returns exactly one row
• Returns exactly one column
• Produces a single value

Because it returns a single value, it can be used anywhere a normal expression can be used.

2. Why Is It Called “Scalar”?

In SQL, a scalar value means a single value (number, string, date, etc.).
Since the subquery returns one value → it behaves like a column or constant.

3. Where Can a Scalar Subquery Be Used?

Scalar subqueries can be used in:

• SELECT clause
• WHERE clause
• HAVING clause
• ORDER BY clause
• UPDATE statements
• INSERT statements

4. Scalar Subquery in SELECT Clause (Most Common)

Example:

SELECT employee_id,

       salary,

       (SELECT MAX(salary) FROM employees) AS max_salary

FROM employees;

Explanation:

• Subquery returns one value (maximum salary).
• That value is displayed for every row.
• Subquery is executed once (non-correlated).

5. Scalar Subquery in WHERE Clause

SELECT employee_id, salary

FROM employees

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

);

The subquery returns a single value, so comparison works.

6. Scalar Subquery with Correlation

Scalar subqueries can also be correlated.

Example:

SELECT e.employee_id,

       e.salary,

       (SELECT AVG(salary)

        FROM employees

        WHERE department_id = e.department_id) AS dept_avg

FROM employees e;

• For each employee row, subquery calculates department average.
• Executed once per row.

7. What Happens If Scalar Subquery Returns Multiple Rows?

Oracle throws error:

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

Because scalar subquery must return exactly one row.

8. What Happens If It Returns No Rows?

If no rows are returned:

• Oracle treats result as NULL.

Example:

WHERE salary > (

    SELECT salary

    FROM employees

    WHERE employee_id = 99999

);

• Subquery returns NULL
• Condition becomes salary > NULL
• No rows returned

9. Scalar Subquery vs Single-Row Subquery

Feature	Scalar Subquery	Single-Row Subquery
Returns	One value	One row, one column
Used in SELECT	Yes	Not always
Must return one row	Yes	Yes

All scalar subqueries are single-row, but scalar specifically refers to usage as a value expression.

10. Scalar Subquery in UPDATE

Example:

UPDATE employees e

SET salary = (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

)

WHERE department_id = 10;

Salary updated to department average per row.

11. Scalar Subquery in INSERT

INSERT INTO bonus_table (employee_id, bonus_amount)

VALUES (

    101,

    (SELECT salary * 0.10

     FROM employees

     WHERE employee_id = 101)

);

Subquery returns one value for bonus calculation.

12. Can Scalar Subquery Return Multiple Columns?

❌ No.
It must return exactly:

• One column
• One row

Multiple columns → ORA-00913 (too many values).

13. Performance Considerations

Non-Correlated Scalar Subquery:

• Executed once → good performance

Correlated Scalar Subquery:

• Executed per row → may cause performance issues
• Needs proper indexing

14. Scalar Subquery vs JOIN

Scalar subquery example:

SELECT e.employee_id,

       (SELECT department_name

        FROM departments d

        WHERE d.department_id = e.department_id) AS dept_name

FROM employees e;

Equivalent JOIN:

SELECT e.employee_id, d.department_name

FROM employees e

JOIN departments d

ON e.department_id = d.department_id;

JOIN often faster for large datasets.

15. Real-World Business Example

Show each employee with company average salary:

SELECT employee_id,

       salary,

       (SELECT AVG(salary) FROM employees) AS company_avg

FROM employees;

AVG returns one value, used as column expression.

16. Common Interview Questions

Q1: What is a scalar subquery?
Returns exactly one value and can be used anywhere a single value expression is allowed.

Q2: Can it return multiple rows?
No. ORA-01427 error is raised.

Q3: What happens if no rows returned?
Result is treated as NULL.

Q4: Is scalar subquery always non-correlated?
No. Can be correlated or non-correlated.

17. Common Mistakes

• Returning multiple rows
• Forgetting NULL behavior
• Using correlated scalar subquery without index
• Using scalar subquery instead of JOIN unnecessarily

18. Best Practices

• Use aggregate functions to guarantee one row
• Ensure uniqueness when using primary key conditions
• Prefer JOIN for large datasets
• Add indexes for correlated scalar subqueries
• Check execution plan

19. Alternative: Analytic Function Instead of Scalar Subquery

Instead of:

SELECT e.employee_id,

       (SELECT AVG(salary)

        FROM employees

        WHERE department_id = e.department_id) dept_avg

FROM employees e;

Use analytic function:

SELECT employee_id,

       AVG(salary) OVER (PARTITION BY department_id) dept_avg

FROM employees;

Often more efficient.

20. Short Interview Answer

“A scalar subquery is a subquery that returns exactly one value and can be used wherever a single value expression is allowed, such as in SELECT, WHERE, or UPDATE clauses. If it returns multiple rows, Oracle raises ORA-01427 error. If no rows are returned, the result is treated as NULL.”