Scalar Subqueries

A scalar subquery in Oracle is a type of subquery that returns a single value (single row, single column) and can be used in places where an expression or literal value is expected. Scalar subqueries are often used in SELECT, INSERT, UPDATE, and DELETE statements, and they can be very useful in SQL queries when you want to derive a single value from another query result.

Key Characteristics of Scalar Subqueries:

1. Single Value Result: A scalar subquery must return exactly one row and one column. If it returns more than one row, or more than one column, Oracle will throw an error.
2. Used as an Expression: Since it returns a single value, it can be used wherever a single value (literal or expression) is expected, such as in the SELECT list, WHERE clause, HAVING clause, or ORDER BY clause.
3. Nested Query: A scalar subquery is a query inside another SQL query. It is enclosed in parentheses.

Syntax of Scalar Subquery:

SELECT column_name(s)

FROM table_name

WHERE column_name = (SELECT single_column FROM another_table WHERE condition);

Where Scalar Subqueries Can Be Used:

1. In the SELECT Clause:
• You can use scalar subqueries in the SELECT list to retrieve a single value for each row returned by the outer query.

Example: Select employees and their department's highest salary.

SELECT employee_name,

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

FROM employees e;

Here, for each employee, a scalar subquery returns the highest salary in their department.

2. In the WHERE Clause:
• Scalar subqueries can be used in the WHERE clause to filter rows based on the result of a subquery that returns a single value.

Example: Select employees whose salary is higher than the average salary.

SELECT employee_name, salary

FROM employees

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

This subquery returns a single value (the average salary), and the outer query compares each employee's salary to it.

3. In the HAVING Clause:
• A scalar subquery can also be used in the HAVING clause to filter groups based on an aggregate value calculated by a subquery.

Example: Get departments having an average salary higher than a certain value.

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

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

The subquery in the HAVING clause returns a single value (the average salary across all employees) and compares the average salary of each department against this value.

4. In the ORDER BY Clause:
• Scalar subqueries can be used in the ORDER BY clause to order results based on a derived value.

Example: Order employees based on their salary compared to the department's average salary.

SELECT employee_name, salary, department_id

FROM employees

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

5. In the SET Clause (for UPDATE statement):
• Scalar subqueries can be used in the SET clause of an UPDATE statement to set a column value based on a single value returned by the subquery.

Example: Increase the salary of employees in a department to the highest salary in that department.

UPDATE employees e

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

WHERE e.department_id = 10;

6. In the INSERT Statement:
• Scalar subqueries can also be used in INSERT statements to insert a value that is the result of a subquery.

Example: Insert a new employee with the same salary as the highest salary in the department.

INSERT INTO employees (employee_name, salary, department_id)

VALUES ('John Doe',

        (SELECT MAX(salary) FROM employees WHERE department_id = 10),

        10);

Important Considerations for Scalar Subqueries:

1. Performance Impact:
• Scalar subqueries can sometimes lead to poor performance, especially if they are correlated or are evaluated repeatedly for every row of the outer query. This can be mitigated by restructuring the query, using JOINs, or converting the subquery to a WITH clause (Common Table Expression).
2. Handling No Results:
• If the scalar subquery does not return any rows, it will return a NULL value. This can be useful, but you need to handle it properly in your logic (e.g., using COALESCE() or NVL() to replace NULL with a default value).
3. Handling Multiple Rows:
• 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 avoid this, you can either:
• Ensure the subquery returns only a single row by using WHERE conditions.
• Use LIMIT or ROWNUM to restrict the subquery to one row.
• Alternatively, consider using a JOIN or IN if you're expecting multiple rows.
4. Correlated vs Non-Correlated Scalar Subqueries:
• A correlated subquery refers to a subquery that references columns from the outer query. Each row of the outer query will trigger a separate execution of the subquery.
• Example:

§  SELECT employee_name

§  FROM employees e

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

• A non-correlated subquery does not reference columns from the outer query and can be executed independently of the outer query.

Example of Scalar Subqueries in Different Scenarios:

Example 1: Subquery in the SELECT Clause

SELECT employee_name,

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

FROM employees e;

Example 2: Subquery in the WHERE Clause

SELECT employee_name

FROM employees

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

Example 3: Subquery in the HAVING Clause

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

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

Example 4: Subquery in the ORDER BY Clause

SELECT employee_name, salary, department_id

FROM employees

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

Best Practices:

• Avoid Nested Scalar Subqueries: Whenever possible, avoid using scalar subqueries that are nested inside other scalar subqueries, as it can lead to performance issues.
• Use Proper Indexing: Ensure that the columns used in scalar subqueries are indexed for better performance, especially when filtering based on values in subqueries.
• Test for NULL: Be mindful of NULL results from scalar subqueries. Always test for NULL or use NVL()/COALESCE() if you need a default value.