Using Subqueries in Oracle SQL
Subqueries are queries embedded within another query, often used to retrieve data that will be used in the main query. They are powerful tools for performing complex data retrieval and manipulation tasks. Understanding subqueries is crucial for mastering advanced SQL techniques.
1. Single-Row Subqueries
Single-row subqueries return a single row of data. They are typically used with comparison operators like =, >, <, >=, <=, and <>. These subqueries are useful when you need to compare a value from the main query with a single value from the subquery.
Example:
Suppose you want to find the employee who has the highest salary. You can use a single-row subquery to achieve this:
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
2. Multi-Row Subqueries
Multi-row subqueries return multiple rows of data. They are used with multi-row operators like IN, ANY, and ALL. These subqueries are useful when you need to compare a value from the main query with multiple values from the subquery.
Example:
Suppose you want to find all employees who work in departments located in 'New York'. You can use a multi-row subquery to achieve this:
SELECT FirstName, LastName
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');
3. Correlated Subqueries
Correlated subqueries are subqueries that depend on the outer query for their values. They are executed once for each row processed by the outer query. These subqueries are useful when the subquery needs to refer to a column from the outer query.
Example:
Suppose you want to find all employees whose salary is greater than the average salary of their department. You can use a correlated subquery to achieve this:
SELECT FirstName, LastName, Salary
FROM Employees e
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID);
4. Nested Subqueries
Nested subqueries are subqueries that are embedded within another subquery. They are useful for performing complex data retrieval tasks that require multiple levels of data filtering or aggregation.
Example:
Suppose you want to find the department with the highest average salary. You can use a nested subquery to achieve this:
SELECT DepartmentName
FROM Departments
WHERE DepartmentID = (
SELECT DepartmentID
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) = (
SELECT MAX(AVG(Salary))
FROM Employees
GROUP BY DepartmentID
)
);
Understanding and mastering subqueries is essential for performing complex data retrieval and manipulation tasks in Oracle SQL. By using single-row, multi-row, correlated, and nested subqueries, you can efficiently solve a wide range of data-related problems.