Subquery in Oracle SQL

Subquery in Oracle SQL: A Comprehensive Explanation

A subquery is a query embedded within another query (main or outer query) to fetch data needed for processing in the main query. Subqueries are often enclosed in parentheses and can appear in various clauses such as SELECT, FROM, WHERE, and HAVING.

---

Features of Subqueries

1. A subquery must be enclosed in parentheses.

2. The subquery executes first, and its result is used by the outer query.

3. Subqueries can return:

A single value.

A single row.

Multiple rows.

Multiple columns.

---

Types of Subqueries

1. Single-Row Subquery

Returns exactly one row and one column.

Typically used with comparison operators like =, <, >, >=, <=, or <>.

Example:

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Explanation: The subquery calculates the average salary of all employees. The outer query fetches details of employees earning more than the average.

---

1. Multi-Row Subquery

Returns multiple rows but only one column.

Used with operators like IN, ANY, or ALL.

Example:

SELECT employee_id, first_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

Explanation: The subquery retrieves department IDs in location 1700. The outer query fetches employees in those departments.

---

1. Multi-Column Subquery

Returns multiple rows and multiple columns.

Used with composite operators like IN or EXISTS.

Example:

SELECT employee_id, first_name
FROM employees
WHERE (department_id, job_id) IN (SELECT department_id, job_id FROM job_history);

Explanation: The subquery retrieves pairs of department_id and job_id from job_history. The outer query fetches employees matching those pairs.

---

1. Correlated Subquery

Depends on the outer query for its execution.

Evaluates once for every row processed by the outer query.

Example:

SELECT e1.employee_id, e1.first_name
FROM employees e1
WHERE salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

Explanation: For each employee in the outer query, the subquery calculates the average salary for that employee's department. The outer query fetches employees earning more than the departmental average.

---

1. Inline View (Subquery in the FROM Clause)

Used as a derived table to simplify complex queries.

Enables grouping, filtering, or joining subquery results.

Example:

SELECT department_id, COUNT(*)
FROM (SELECT department_id FROM employees WHERE salary > 10000) temp
GROUP BY department_id;

Explanation: The subquery retrieves department IDs of employees earning more than 10,000. The outer query counts employees in those departments.

---

1. Scalar Subquery

Returns a single value (scalar).

Often used in the SELECT clause or as a computed column.

Example:

SELECT employee_id,
first_name,
(SELECT department_name FROM departments WHERE department_id = e.department_id) AS dept_name
FROM employees e;

Explanation: The subquery retrieves the department_name for each employee based on their department_id.

---

Subquery Placement in SQL Clauses

A. In the WHERE Clause

SELECT employee_id, first_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Filters rows based on the result of the subquery.

---

B. In the FROM Clause

SELECT dept_name, total_salary
FROM (SELECT department_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id) sub,
departments
WHERE sub.department_id = departments.department_id;

Treats the subquery as a temporary table.

---

C. In the SELECT Clause

SELECT employee_id,
first_name,
(SELECT department_name FROM departments WHERE department_id = e.department_id) AS dept_name
FROM employees e;

Adds a computed column based on the subquery result.

---

D. In the HAVING Clause

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Filters groups based on the subquery result.

---

Operators Used in Subqueries

1. Single-row Operators

=, <, >, >=, <=, <>

Used when the subquery returns a single value.

1. Multi-row Operators

IN, ANY, ALL, EXISTS

Used when the subquery returns multiple rows.

Examples:

IN: Match any value in the subquery result.

SELECT employee_id FROM employees WHERE department_id IN (SELECT department_id FROM departments);

ANY: Compare with any value from the subquery.

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

ALL: Compare with all values from the subquery.

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

EXISTS: Checks for the existence of rows in the subquery.

SELECT employee_id FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE department_id = employees.department_id);

---

Advantages of Subqueries

1. Simplify complex queries by breaking them into manageable parts.

2. Enable dynamic filtering using data not directly available in the outer query.

3. Reuse logic without creating temporary tables or views.

---

Performance Considerations

Subqueries can impact performance, especially if they involve large datasets or multiple executions in correlated subqueries.

Use inline views or joins where possible for better optimization.

---

Subquery vs Joins

---

Summary

Subqueries are a powerful feature of SQL that allow embedding additional queries within the main query to handle complex logic. They are versatile and can be used in various SQL clauses, making them invaluable for data manipulation and retrieval. Proper usage, coupled with performance optimization, can greatly enhance query efficiency.