Subquery FAQS

1. What is a subquery in Oracle SQL?

A subquery is a query embedded inside another query (also known as the outer query). It is used to return intermediate results that help the outer query compute its results. Subqueries can be placed in the SELECT, FROM, WHERE, or HAVING clauses of a query.

2. What are the types of subqueries?

There are three primary types of subqueries:

• Single-Row Subquery: Returns only one row and one column.
• Multiple-Row Subquery: Returns multiple rows but only one column.
• Multiple-Column Subquery: Returns multiple rows and multiple columns (also known as a tuple subquery).

3. Can subqueries be used in the SELECT clause?

Yes, subqueries can be used in the SELECT clause to calculate or retrieve derived values for each row in the result set. For example, you can calculate aggregates like SUM(), AVG(), or other values.

Example:

SELECT department_id,

       (SELECT AVG(salary) FROM employees e WHERE e.department_id = d.department_id) AS avg_salary

FROM departments d;

4. What is a correlated subquery?

A correlated subquery is a subquery that references columns from the outer query. Unlike non-correlated subqueries, which can be run independently, correlated subqueries are executed once for each row processed by 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);

5. What are the differences between EXISTS and IN in subqueries?

• EXISTS: Checks for the existence of any rows returned by the subquery. It is generally more efficient than IN when the subquery involves large datasets.
• IN: Compares the outer query's column to a set of values returned by the subquery. It is typically used when you need to match against a list of values.

6. How do I handle "subquery returns more than one row" error?

This error occurs when a subquery that is expected to return a single value returns more than one row. To fix this, ensure that:

• The subquery should only return a single row (use LIMIT, TOP, or aggregate functions like MAX(), MIN(), etc.).
• If the outer query expects multiple rows, use a multiple-row comparison operator like IN instead of =.

Example:

SELECT employee_id, first_name

FROM employees

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

In this case, if the subquery returns more than one department_id, it will cause an error.

7. How do I use a subquery in the FROM clause?

Subqueries can be used in the FROM clause to create a derived table (also called an 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;

8. Can I use aggregate functions in subqueries?

Yes, you can use aggregate functions (like COUNT(), SUM(), AVG(), MAX(), MIN()) in subqueries. These functions are often used to perform calculations over a subset of data and then return the result 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);

9. What is the difference between ANY and ALL in subqueries?

• ANY: Compares a value to a set of values returned by a subquery. The condition is true if the outer query's value matches any of the subquery values.
• ALL: Compares a value to all values returned by a subquery. The condition is true if the outer query's value matches every value from the subquery.

Example with ANY:

SELECT employee_id, salary

FROM employees

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

Example with ALL:

SELECT employee_id, salary

FROM employees

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

10. What are the performance considerations when using subqueries?

Subqueries can be computationally expensive, especially when:

• The subquery returns a large number of rows.
• The subquery is correlated (executed once for each row of the outer query).

To optimize subquery performance:

• Consider converting correlated subqueries into JOIN operations when possible.
• Use indexes to speed up subquery execution.
• Limit the number of rows returned by the subquery using LIMIT, TOP, or appropriate filtering.

11. Can a subquery return multiple columns?

Yes, a subquery can return multiple columns (also called a tuple subquery). This is often used in WHERE clauses to compare sets of values 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);

12. Can a subquery be used in the HAVING clause?

Yes, subqueries can be used in the HAVING clause to filter groups based on an aggregate value calculated by the subquery.

Example:

SELECT department_id, COUNT(*) AS employee_count

FROM employees

GROUP BY department_id

HAVING COUNT(*) > (SELECT AVG(employee_count) FROM (SELECT COUNT(*) AS employee_count FROM employees GROUP BY department_id));

13. What happens if a subquery is empty?

If a subquery returns no results, it may affect the outer query’s logic:

• With IN: If the subquery returns no rows, the condition becomes false and no rows are returned.
• With EXISTS: If the subquery returns no rows, the EXISTS condition will evaluate as false.
• With =: If the subquery returns no rows, an error will occur when trying to compare a value to NULL. To avoid this, you should use operators like IN or EXISTS.