Single-Row Subqueries

A single-row subquery (also called a scalar subquery) in Oracle is a type of subquery that returns only one row and one column. This subquery can be used in contexts where a single value is required. Single-row subqueries are typically used with comparison operators like =, <, >, <=, >=, and <>. They are called "single-row" because they return only a single value.

Here is a detailed explanation of the various aspects of single-row subqueries:

 

1. Syntax of a Single-Row Subquery

A single-row subquery is typically embedded within a query as a part of the SELECT, INSERT, UPDATE, or DELETE statements. It can appear in places where an expression or a value is expected.

SELECT column1

FROM table1

WHERE column2 = (SELECT column3 FROM table2 WHERE condition);

·        In this example, the subquery (SELECT column3 FROM table2 WHERE condition) returns a single value, which is then compared to column2 of table1.

 

2. Key Characteristics of Single-Row Subqueries

·        Returns one row: A single-row subquery returns only a single row. If it returns more than one row, Oracle will raise an error.

·        Scalar value: The result of a single-row subquery is a scalar value (one column, one row).

·        Comparison Operators: Typically used with comparison operators such as =, >, <, >=, <=, or <>.

 

3. Using Single-Row Subqueries in SQL Statements

A. In a SELECT Statement

A subquery can be used to return a single value and apply it in the SELECT clause or the WHERE clause.

Example 1: Using a subquery in the WHERE clause

SELECT employee_name

FROM employees

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

In this case, the subquery returns the highest salary from the employees table, and the main query retrieves the name of the employee(s) who earn this salary.

Example 2: Using a subquery in the SELECT clause

SELECT employee_name,

       (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department

FROM employees;

Here, the subquery retrieves the department name for each employee based on their department ID.

 

B. In an UPDATE Statement

A single-row subquery can also be used in the SET clause of an UPDATE statement.

UPDATE employees

SET salary = (SELECT MAX(salary) FROM employees)

WHERE employee_id = 100;

In this case, the UPDATE statement sets the salary of the employee with employee_id 100 to the highest salary found in the employees table.

 

C. In an INSERT Statement

A subquery can be used in the VALUES clause of an INSERT statement to insert values derived from another query.

INSERT INTO employees (employee_name, salary)

VALUES ('John Doe', (SELECT MAX(salary) FROM employees));

Here, the subquery fetches the highest salary in the employees table, and that value is inserted as the salary for the new employee.

 

4. Restrictions on Single-Row Subqueries

·        Single Row: The subquery must return exactly one row. If the subquery returns no rows or more than one row, Oracle will raise an error.

o   If the subquery returns no rows, the comparison will result in a false condition, and the query will return no rows.

o   If the subquery returns multiple rows, Oracle will return an error such as: ORA-01427: single-row subquery returns more than one row.

Example of invalid query:

SELECT employee_name

FROM employees

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

If more than one employee has a salary in department 10, this query will fail because the subquery will return multiple rows.

 

5. Handling No Row or Multiple Rows in Subqueries

You can handle cases where the subquery might return no rows or multiple rows by using specific operators or functions.

A. Using IS NULL or COALESCE

If you expect the subquery to possibly return no row, you can use the IS NULL condition or the COALESCE function to handle it.

SELECT employee_name

FROM employees

WHERE salary = (SELECT salary FROM employees WHERE department_id = 1000) OR salary IS NULL;

Here, if the subquery returns no rows (i.e., no employee in department 1000), the salary IS NULL part will be used, avoiding an error.

B. Using LIMIT or ROWNUM

If you suspect the subquery may return multiple rows but you only want the first row, you can limit the result using ROWNUM (for older versions of Oracle) or FETCH FIRST (in newer versions of Oracle).

SELECT employee_name

FROM employees

WHERE salary = (SELECT salary FROM employees WHERE department_id = 10 AND ROWNUM = 1);

 

6. Example Queries with Single-Row Subqueries

Example 1: Subquery with the Equal (=) Operator

SELECT employee_name

FROM employees

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

This query returns the names of all employees who work in the 'IT' department. The subquery returns the department_id for the 'IT' department.

Example 2: Subquery with Greater Than (>) Operator

SELECT employee_name, salary

FROM employees

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

This query retrieves the names and salaries of employees whose salary is greater than the average salary in the employees table.

Example 3: Subquery with NOT Equal (<>) Operator

SELECT employee_name

FROM employees

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

This query returns the names of employees who do not belong to the 'Sales' department.

 

7. Performance Considerations

·        Subquery Efficiency: A single-row subquery is typically efficient when dealing with small result sets. However, if the subquery involves a large dataset or complex calculations, it might slow down the overall query.

·        Indexes: If the columns used in the subquery’s WHERE clause are indexed, the query will generally perform faster.

·        Correlated Subqueries: Unlike non-correlated subqueries (like single-row subqueries), correlated subqueries reference columns from the outer query and may require multiple executions for each row of the outer query.

 

8. Conclusion

Single-row subqueries are a powerful tool in Oracle SQL, allowing you to extract single scalar values for comparisons or calculations. Understanding when and how to use them will help in writing more efficient and readable SQL queries. Always ensure that the subquery returns exactly one row to avoid errors, and be mindful of the performance implications when dealing with large datasets or complex subqueries.