Learn Correlated & Non-Correlated Subqueries in SELECT, FROM, WHERE
Master SQL subqueries in this beginner-friendly guide. Learn how to use subqueries in SELECT, FROM, and WHERE clauses, and understand correlated vs non-correlated subqueries. Practice finding employees earning more than average and customers with highest purchases.
1. Introduction
A subquery (or inner query) is a query inside another SQL query.
- Subqueries are used to fetch intermediate results for filtering, aggregation, or calculations.
- They can appear in SELECT, FROM, or WHERE clauses.
Key Points:
- Subqueries improve query flexibility.
- Can be single-row, multi-row, or multi-column.
- Can be correlated (depends on outer query) or non-correlated (independent).
2. Subqueries in SELECT, FROM, WHERE
2.1 Subquery in WHERE Clause
- Filters rows based on a condition calculated by the subquery.
Example:
-- Employees earning more than average salary
SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
2.2 Subquery in FROM Clause
- Treats the subquery result as a temporary table.
Example:
-- Average salary per department using subquery
SELECT Department, AvgSalary
FROM (SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department) AS DeptAvg
WHERE AvgSalary > 50000;
2.3 Subquery in SELECT Clause
- Calculates a value for each row in the outer query.
Example:
-- Display employee name with department average salary
SELECT Name,
(SELECT AVG(Salary)
FROM Employees e2
WHERE e2.Department = e1.Department) AS DeptAvgSalary
FROM Employees e1;
3. Correlated vs Non-Correlated Subqueries
| TypeDescriptionExample | ||
| Non-Correlated | Independent subquery | (SELECT AVG(Salary) FROM Employees) |
| Correlated | Depends on outer query row | (SELECT AVG(Salary) FROM Employees e2 WHERE e2.Department = e1.Department) |
Key:
- Correlated subqueries execute once per row of outer query.
- Non-correlated subqueries execute once independently.
4. Practical Exercises
- List employees earning more than the average salary.
- Find customers with the highest purchase amount.
- Retrieve products priced above the category average.
- Display employees whose salary is above department average.
- Count orders where the order quantity exceeds the average order quantity.
5. Tips for Beginners
- Start with non-correlated subqueries to understand the concept.
- Use aliases for subquery tables for clarity.
- Use IN, ANY, or ALL operators with subqueries for advanced filtering.
- Combine subqueries with joins for complex queries.
Next Step: After mastering subqueries, the next module is Set Operations, where you’ll learn UNION, UNION ALL, INTERSECT, and EXCEPT/MINUS for combining query results.
