Single-Row Subqueries

 In Oracle SQL, Single-Row Subqueries are a type of subquery that return only one row and one column as a result. These subqueries are commonly used when you need to perform operations like comparisons or assignments with a single value, such as a scalar value.

Key Points about Single-Row Subqueries

1. Definition: A Single-Row Subquery is a subquery that returns at most one row and one column. It is commonly used in a context where a single value is required, such as in comparison operators like =, <, >, <=, >=, or <>.
2. Characteristics:
• Returns a single row: The subquery must return only one row of results.
• Scalar result: Typically, a single column value is returned.
• No ambiguity: If the subquery returns more than one row, an error is raised (ORA-01427: single-row subquery returns more than one row).
• Used for comparison: Most often used in a condition where a comparison is made with a scalar value.
3. Usage Contexts:
• WHERE Clause: To filter data based on a single value returned by the subquery.
• SELECT Clause: To include a computed value based on a single-row subquery.
• HAVING Clause: To apply a condition to groups of data.
4. Example: Let's break down a simple example of a Single-Row Subquery:

SELECT employee_id, name, salary

FROM employees

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

• The subquery (SELECT AVG(salary) FROM employees) returns a single value (the average salary), and this value is compared against the salary column of the outer query.
• The subquery is executed once, and its result is used in the comparison for every row in the outer query.

5. Common Operators with Single-Row Subqueries:

Single-row subqueries are often used with the following comparison operators:

• = (equal to)
• !=, <> (not equal to)
• <, >, <=, >= (comparison operators)
• BETWEEN (range comparison)
• LIKE (pattern matching)
• IS NULL (check for null)

Example with = operator:

SELECT employee_id, name, salary

FROM employees

WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

In this example, the subquery returns one department ID that matches the Sales department, and that ID is used to filter the employees in the outer query.

6. Error Handling for Multiple Rows:

A Single-Row Subquery must return only one row. If it returns more than one row, Oracle will raise an error:

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

Example of Invalid Single-Row Subquery:

SELECT employee_id, name

FROM employees

WHERE department_id = (SELECT department_id FROM departments);

This will fail if the departments table has more than one row because the subquery returns multiple department_id values, violating the "single-row" requirement.

7. How to Handle Multiple Rows in a Subquery:

If you expect that the subquery might return more than one row, you have two options:

• Use IN: If you want to allow multiple rows in the subquery and match against a list of values.

·        SELECT employee_id, name

·        FROM employees

·        WHERE department_id IN (SELECT department_id FROM departments);

• Use EXISTS: If you only care whether any rows exist in the subquery.

·        SELECT employee_id, name

·        FROM employees e

·        WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id);

8. Nested Single-Row Subqueries:

A Single-Row Subquery can also be nested inside another subquery or even within expressions to create more complex queries.

Example:

SELECT employee_id, salary

FROM employees

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

In this case:

• The inner-most subquery retrieves the department_id for HR.
• The next subquery retrieves the maximum salary for that department.
• The outer query filters employees who earn more than the maximum salary in the HR department.

9. Using Single-Row Subqueries in the SELECT Clause:

Single-row subqueries are often used in the SELECT clause to calculate values that apply uniformly across all rows in the outer query.

Example:

SELECT employee_id, name, (SELECT MAX(salary) FROM employees) AS max_salary

FROM employees;

Here, the subquery (SELECT MAX(salary) FROM employees) returns the highest salary from the entire employees table, which is then displayed alongside each employee's data.

10. Performance Considerations:

• Efficient Execution: Since the subquery returns a single value, Oracle only needs to compute this value once for the entire query. This can often lead to better performance than correlated subqueries.
• Indexes: When used in filters, such as in the WHERE clause, proper indexing on columns involved in the subquery can improve performance.
• Avoiding Errors: Be mindful of subqueries that might return multiple rows when only a single value is expected. Always ensure that the subquery is designed to return a single value by using aggregate functions like MAX, MIN, or AVG.

11. Examples of Valid vs. Invalid Single-Row Subqueries:

Valid Example (Single-Row Subquery):

SELECT employee_id, name

FROM employees

WHERE salary = (SELECT MAX(salary) FROM employees);

Invalid Example (Multiple Rows Returned):

SELECT employee_id, name

FROM employees

WHERE department_id = (SELECT department_id FROM departments);

In the second example, if the subquery returns multiple department_id values, the query will fail because a single-row subquery is expected.

12. Conclusion:

Single-Row Subqueries are a powerful tool in SQL for retrieving single values to compare against rows in the outer query. They allow for concise and efficient queries when dealing with aggregate values, limits, or other computations that return one result. However, care must be taken to ensure that these subqueries return only one row; otherwise, an error will occur.