Scalar Subqueries

A Scalar Subquery in Oracle SQL is a subquery that returns a single value (i.e., exactly one row and exactly one column). Scalar subqueries are often used when you need to return a single value for use in a larger query, such as in a SELECT, INSERT, UPDATE, or DELETE statement.

Key Characteristics of Scalar Subqueries:

1.     Returns a Single Value:
The result of a scalar subquery must be a single value. This could be a simple value, like an integer or a string, or the result of an aggregate function (e.g., MAX(), SUM(), AVG(), etc.).

2.     Used in Single-Value Contexts:
Scalar subqueries are typically used in contexts where a single value is required, such as:

• In the SELECT clause.
• In the WHERE clause for comparisons.
• In the SET clause of an UPDATE statement.
• In the VALUES clause of an INSERT statement.

3.     Conditions:

o   The subquery must return exactly one row and one column. If the subquery returns more than one row or column, it will result in an error:

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

o   If the subquery returns no rows, the value will be treated as NULL.

Usage of Scalar Subqueries

1. Scalar Subqueries in the SELECT Clause:

Scalar subqueries are commonly used in the SELECT clause to compute or retrieve a single value for each row in the outer query.

Example: Retrieve the salary of each employee, along with the maximum salary in the company:

SELECT employee_id, name, salary,

       (SELECT MAX(salary) FROM employees) AS max_salary

FROM employees;

In this query:

• The subquery (SELECT MAX(salary) FROM employees) returns a single value, the maximum salary in the entire employees table.
• The outer query fetches employee details along with the maximum salary (which is the same for all rows returned).

2. Scalar Subqueries in the WHERE Clause:

Scalar subqueries are often used in the WHERE clause when you need to compare a column to a single value returned by the subquery.

Example: Find employees who have a salary greater than the highest salary in department 10:

SELECT employee_id, name, salary

FROM employees

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

In this case:

• The subquery (SELECT MAX(salary) FROM employees WHERE department_id = 10) returns the maximum salary in department 10.
• The outer query retrieves employees whose salary is greater than this value.

3. Scalar Subqueries in the UPDATE Clause:

Scalar subqueries can be used in the SET clause of an UPDATE statement to assign a value based on the result of the subquery.

Example: Increase the salary of all employees in department 20 by an amount equal to the average salary in that department:

UPDATE employees

SET salary = salary + (SELECT AVG(salary) FROM employees WHERE department_id = 20)

WHERE department_id = 20;

Here:

• The scalar subquery (SELECT AVG(salary) FROM employees WHERE department_id = 20) computes the average salary in department 20.
• The UPDATE query adds this average salary to each employee's existing salary in department 20.

4. Scalar Subqueries in the INSERT Clause:

You can also use scalar subqueries in the VALUES clause of an INSERT statement to insert values based on the result of a subquery.

Example: Insert a new employee with a salary that is 10% higher than the highest salary in the company:

INSERT INTO employees (employee_id, name, salary, department_id)

VALUES (1001, 'John Doe', 

        (SELECT MAX(salary) FROM employees) * 1.1, 

        30);

In this example:

• The scalar subquery (SELECT MAX(salary) FROM employees) returns the highest salary.
• The new salary is set to 10% more than this value, and a new employee is inserted into department 30.

Performance Considerations for Scalar Subqueries:

1.     Single-Row Subquery: Scalar subqueries should always return a single value. If they return more than one value, it can lead to errors (ORA-01427). Hence, ensure your subquery returns exactly one row and one column.

2.     Handling Empty Results: If the scalar subquery returns no rows, the result will be treated as NULL. You can handle this with COALESCE or NVL if you need to provide a default value for empty results.

Example:

SELECT employee_id, name,

       NVL((SELECT MAX(salary) FROM employees WHERE department_id = 10), 0) AS max_salary

FROM employees;

In this case, if the subquery returns no result (i.e., no employees in department 10), it will default the max_salary to 0.

3.     Avoiding Inefficient Scalar Subqueries:

• Be cautious about placing scalar subqueries in WHERE clauses that are evaluated repeatedly for each row. In some cases, this can lead to performance problems because the subquery is executed multiple times.
• If needed, you can restructure your query or use joins to avoid repeatedly executing a subquery.

4.     Using EXISTS or IN for Multiple Rows: If you expect the subquery to return multiple rows, and you need to perform a comparison for each row, use EXISTS or IN instead of a scalar subquery. These operators are designed for multi-row subqueries and help avoid errors like ORA-01427.

Example:

SELECT employee_id, name

FROM employees

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1000);

Error Handling in Scalar Subqueries:

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

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

This happens because the outer query is expecting a single value, but the subquery provides multiple values. To fix this, ensure your subquery returns exactly one row and one column.

Examples of Scalar Subqueries in Real-Life Scenarios:

1.     Subquery to Find Employees Earning More than the Average Salary: Find employees who earn more than the average salary in the company:

SELECT employee_id, name, salary

FROM employees

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

2.     Subquery to Find Employees Whose Salary is Greater Than the Minimum Salary in Their Department:

SELECT employee_id, name, salary

FROM employees e

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

3.     Subquery in INSERT to Set Default Values:

INSERT INTO employees (employee_id, name, salary)

VALUES (1002, 'Jane Smith', 

        (SELECT MAX(salary) FROM employees) + 5000);