Subqueries in Oracle SQL
Key Concepts
Subqueries in Oracle SQL are queries nested inside another query. They can be used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Understanding the following key concepts is essential for effectively using subqueries:
1. Simple Subqueries
A simple subquery is a subquery that returns a single value or a list of values. It can be used in the WHERE clause to filter rows based on the result of the subquery.
Example: Retrieving employees who earn more than the average salary:
SELECT EmployeeID, FirstName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
2. Multiple-Row Subqueries
A multiple-row subquery returns more than one row. It can be used with operators like IN, ANY, or ALL to compare a single value with a set of values returned by the subquery.
Example: Retrieving employees who work in departments with more than 10 employees:
SELECT EmployeeID, FirstName, DepartmentID
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE EmployeeCount > 10);
3. Correlated Subqueries
A correlated subquery is a subquery that depends on the outer query for its values. It is executed once for each row processed by the outer query.
Example: Retrieving departments where the average salary is higher than the average salary of all employees:
SELECT DepartmentID, DepartmentName
FROM Departments d
WHERE (SELECT AVG(Salary) FROM Employees e WHERE e.DepartmentID = d.DepartmentID) > (SELECT AVG(Salary) FROM Employees);
4. EXISTS and NOT EXISTS
The EXISTS operator checks for the existence of rows in a subquery. It returns true if the subquery returns one or more rows. The NOT EXISTS operator returns true if the subquery returns no rows.
Example: Retrieving employees who have made at least one sale:
SELECT EmployeeID, FirstName
FROM Employees e
WHERE EXISTS (SELECT 1 FROM Sales s WHERE s.EmployeeID = e.EmployeeID);
5. Scalar Subqueries
A scalar subquery returns a single value. It can be used in places where a single value is expected, such as in the SELECT clause or as part of an expression.
Example: Retrieving employees along with the total sales they have made:
SELECT EmployeeID, FirstName, (SELECT SUM(SalesAmount) FROM Sales s WHERE s.EmployeeID = e.EmployeeID) AS TotalSales
FROM Employees e;
6. Nested Subqueries
A nested subquery is a subquery that contains another subquery. This allows for complex queries that can perform multiple levels of filtering and aggregation.
Example: Retrieving departments where the average salary is higher than the average salary of all departments, and the department has more than 10 employees:
SELECT DepartmentID, DepartmentName
FROM Departments d
WHERE (SELECT AVG(Salary) FROM Employees e WHERE e.DepartmentID = d.DepartmentID) > (SELECT AVG(Salary) FROM Employees)
AND (SELECT COUNT(*) FROM Employees e WHERE e.DepartmentID = d.DepartmentID) > 10;
7. Subqueries in the FROM Clause
Subqueries can also be used in the FROM clause to create a derived table. This derived table can then be used in the main query.
Example: Retrieving the top 5 departments with the highest average salary:
SELECT DepartmentID, AvgSalary
FROM (SELECT DepartmentID, AVG(Salary) AS AvgSalary FROM Employees GROUP BY DepartmentID)
ORDER BY AvgSalary DESC
FETCH FIRST 5 ROWS ONLY;
8. Subqueries with Set Operators
Subqueries can be combined using set operators like UNION, INTERSECT, and MINUS to perform complex set operations.
Example: Retrieving employees who are either managers or have made more than $100,000 in sales:
SELECT EmployeeID, FirstName
FROM Employees
WHERE EmployeeID IN (SELECT ManagerID FROM Departments)
UNION
SELECT EmployeeID, FirstName
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Sales WHERE SalesAmount > 100000);