Scalar Subqueries FAQS

1. What is a scalar subquery?

A scalar subquery is a type of subquery that returns a single value (i.e., one row and one column). It can be used anywhere in a SQL query where a single value is expected, such as in the SELECT, WHERE, HAVING, or ORDER BY clauses.

 

2. Where can scalar subqueries be used in SQL?

Scalar subqueries can be used in several places within a SQL query, including:

·        SELECT clause: To return a derived value for each row.

·        WHERE clause: To filter rows based on a single value derived from a subquery.

·        HAVING clause: To filter groups based on an aggregated value derived from a subquery.

·        ORDER BY clause: To sort rows based on a derived value.

·        SET clause (UPDATE): To assign a column value based on a subquery.

·        INSERT statement: To insert a value that is the result of a subquery.

 

3. What happens if the scalar subquery returns more than one row?

If a scalar subquery returns more than one row, Oracle will throw an error:
ORA-01427: single-row subquery returns more than one row.

To resolve this, you can:

·        Ensure that the subquery is properly restricted to return only one row (using conditions like LIMIT or ROWNUM).

·        Use IN or JOIN if expecting multiple values.

 

4. What happens if the scalar subquery returns no rows?

If a scalar subquery returns no rows, it will return NULL. In cases where NULL is not desired, you can use functions like COALESCE() or NVL() to replace the NULL value with a default value.

Example:

SELECT employee_name,

       NVL((SELECT MAX(salary) FROM employees WHERE department_id = e.department_id), 0) AS highest_salary

FROM employees e;

 

5. Can scalar subqueries be used in JOIN operations?

Yes, scalar subqueries can be used in JOIN operations, although they are typically used for filtering or selecting a value. However, scalar subqueries are generally more useful when you want to derive a single value to compare or calculate with.

Example:

SELECT e.employee_name,

       (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id) AS highest_salary

FROM employees e;

Here, the scalar subquery is not part of the JOIN, but it derives a value for each employee based on their department.

 

6. What is the difference between a correlated and non-correlated scalar subquery?

·        Correlated Subquery: A subquery that references columns from the outer query. It is evaluated once for each row in the outer query.

Example:

SELECT employee_name

FROM employees e

WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

In this case, the subquery references e.department_id from the outer query.

·        Non-Correlated Subquery: A subquery that does not reference any columns from the outer query. It is evaluated once, and the result is used for comparison across all rows in the outer query.

Example:

SELECT employee_name

FROM employees

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

The subquery does not reference any outer query columns.

 

7. Can scalar subqueries be used in the ORDER BY clause?

Yes, scalar subqueries can be used in the ORDER BY clause to sort the results based on a calculated value derived from a subquery.

Example:

SELECT employee_name, salary, department_id

FROM employees

ORDER BY salary - (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Here, the scalar subquery calculates the department's average salary and subtracts it from each employee's salary for sorting.

 

8. Can scalar subqueries be nested inside each other?

Yes, scalar subqueries can be nested, but it’s generally best to avoid excessive nesting, as it can impact query performance. For example:

SELECT employee_name

FROM employees

WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR'));

In this case, one subquery is inside another. However, such queries should be used cautiously, especially in complex environments.

 

9. What is the performance impact of scalar subqueries?

Scalar subqueries can sometimes lead to performance issues, especially if they are correlated. This is because the subquery is evaluated for each row of the outer query. Here are a few tips to optimize performance:

·        Indexes: Ensure the columns used in the subquery (such as those in WHERE or JOIN clauses) are indexed.

·        Avoid nested scalar subqueries: If possible, restructure your query to avoid nested subqueries.

·        Use WITH clause (Common Table Expressions): This allows you to materialize intermediate results, improving readability and performance.

 

10. What are some common errors related to scalar subqueries?

·        ORA-01427: single-row subquery returns more than one row: Occurs when the scalar subquery returns multiple rows instead of a single row.

·        ORA-01403: no data found: Occurs when the scalar subquery returns no rows, which can lead to NULL results in the outer query. You can handle this with NVL or COALESCE.

·        ORA-00936: missing expression: Can occur if you forget to enclose the subquery in parentheses or if the subquery is not returning a valid expression.

 

11. How can I handle scalar subqueries returning multiple values?

To handle multiple values returned by a subquery, you can:

·        Use IN instead of =: If you expect multiple rows, change the query structure to use IN instead of =.

Example:

SELECT employee_name

FROM employees

WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'NYC');

·        Use JOIN: Another alternative is to rewrite the query using a JOIN instead of a scalar subquery.

Example:

SELECT e.employee_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE d.location = 'NYC';

 

12. Can scalar subqueries be used with aggregate functions?

Yes, scalar subqueries are often used with aggregate functions to filter or compute values. For example:

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

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

In this example, the scalar subquery calculates the overall average salary, and the HAVING clause compares each department's average salary against it.