Subquery

A subquery (also known as a nested query or inner query) is a query that is embedded within another SQL query. Subqueries allow you to retrieve intermediate results that are then used by the outer query. Subqueries are essential tools in SQL for performing more complex queries, especially when a query needs to return a result based on some condition evaluated by another query.

In Oracle SQL, subqueries can be used in various parts of a query: in SELECT, FROM, WHERE, or HAVING clauses. Below, I'll break down key details on how subqueries work in Oracle, their types, and examples of how to use them.

1. Types of Subqueries

There are three main types of subqueries:

1. Single-Row Subquery (Scalar Subquery)

Description: This subquery returns exactly one row and one column.

Usage: Typically used in places where a single value is needed, such as the SELECT, WHERE, or HAVING clause.

Example:

SELECT employee_id, first_name, salary

FROM employees

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

• In this case, the subquery (SELECT AVG(salary) FROM employees) returns a single value (average salary), which is compared against each employee's salary.
1. Multiple-Row Subquery

Description: This subquery returns multiple rows, but typically only one column.

Usage: Used with operators that can compare multiple values, like IN, ANY, ALL.

Example:

SELECT employee_id, first_name, department_id

FROM employees

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

• Here, the subquery returns multiple department_id values, which are used in the IN operator.
2. Multiple-Column Subquery (Tuple Subquery)
• Description: This subquery returns multiple rows and multiple columns (a tuple of values).
• Usage: Typically used in cases where you need to compare a tuple of values (multiple columns) to a row in the outer query.

Example:

SELECT employee_id, first_name, department_id, job_id

FROM employees

WHERE (department_id, job_id) IN

      (SELECT department_id, job_id

       FROM jobs

       WHERE min_salary > 5000);

• The subquery returns two columns, department_id and job_id, and compares those tuples to the outer query's corresponding columns.

2. Subquery in SELECT Clause

A subquery can be used in the SELECT clause to calculate values for each row returned by the main query. This type of subquery is often used for complex calculations or aggregations.

Example: Using a subquery in the SELECT clause to calculate the total salary per department:

SELECT department_id,

       (SELECT SUM(salary) FROM employees e WHERE e.department_id = d.department_id) AS total_salary

FROM departments d;

• This subquery calculates the sum of salaries for each department and includes it as part of the result set.

 

3. Subquery in WHERE Clause

Subqueries are often used in the WHERE clause to filter results based on a condition that involves another table or a complex calculation. This is one of the most common uses of subqueries.

Example: Finding employees who earn more than the average salary:

SELECT employee_id, first_name, salary

FROM employees

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

• Other Operators for Use in WHERE Clause:

IN: Used when the subquery returns multiple rows.

SELECT employee_id, first_name

FROM employees

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

ANY: Compares a value with a set of values returned by the subquery.

SELECT employee_id, salary

FROM employees

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

ALL: Compares a value with all values returned by the subquery.

SELECT employee_id, salary

FROM employees

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

 

4. Subquery in FROM Clause

A subquery can be used in the FROM clause to create a derived table (also called a subquery in the FROM clause or inline view). This allows you to treat the result of a subquery as if it were a table.

Example:

SELECT department_id, AVG(salary) AS avg_salary

FROM (SELECT department_id, salary FROM employees WHERE salary > 5000)

GROUP BY department_id;

• In this example, the subquery (SELECT department_id, salary FROM employees WHERE salary > 5000) is used to filter out employees with salaries less than or equal to 5000, and then the outer query calculates the average salary by department.

 

5. Correlated Subqueries

A correlated subquery is a type of subquery that references columns from the outer query. Unlike a regular subquery, which can be executed independently, a correlated subquery must be executed once for each row of the outer query.

Example:

SELECT employee_id, first_name, salary

FROM employees e

WHERE salary > (SELECT AVG(salary)

                FROM employees

                WHERE department_id = e.department_id);

• In this example, the subquery references e.department_id from the outer query, so it must be re-evaluated for each row of employees.

 

6. EXISTS vs. IN in Subqueries

• IN: Used when the subquery returns a list of values. If the subquery returns a large set of data, IN can sometimes lead to performance issues.
• EXISTS: Used when you are checking for the existence of rows returned by the subquery. EXISTS is typically more efficient than IN when working with large datasets.

Example with IN:

SELECT employee_id, first_name

FROM employees

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

Example with EXISTS:

SELECT employee_id, first_name

FROM employees e

WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.location_id = 1700);

• In this case, EXISTS checks if there is at least one row that matches the condition for each row in the outer query.

7. Subquery with Aggregates

You can also use aggregates in subqueries. Aggregates like COUNT(), SUM(), AVG(), MIN(), and MAX() are often used in subqueries to calculate summary statistics for comparison in the outer query.

Example:

SELECT department_id, first_name, salary

FROM employees

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

• This subquery calculates the maximum salary for employees in department 10, and the outer query retrieves employees whose salary is higher than that value.

 

8. Performance Considerations

• Execution Order: Oracle first executes the subquery to retrieve the results and then applies the outer query. For correlated subqueries, the subquery is executed for each row of the outer query.
• Optimization: In cases where performance is a concern, it’s essential to optimize the subquery by using indexes, reducing the number of rows returned, or converting subqueries to joins if applicable.
• Using Joins vs. Subqueries: Sometimes, using a JOIN instead of a subquery can improve performance, especially when working with large datasets. For example, replacing a correlated subquery with a JOIN can reduce the number of queries that need to be executed.

 

9. Common Errors

• Too Many Rows: A common error occurs when a subquery returns more rows than the outer query can handle. For example, if a scalar subquery returns more than one value, Oracle will throw an error.
• Solution: Ensure that the subquery returns a single value if used in a context that requires it.
• Missing Parentheses: Make sure that the subquery is enclosed within parentheses, especially when used in the SELECT, WHERE, or HAVING clauses.