Subquery and Co-related subquery

What is a Subquery?

A subquery is a query embedded within another SQL query. It’s often placed within the SELECT, FROM, or WHERE clause of a main (outer) query. Subqueries allow you to retrieve data based on the results of another query, making them useful for filtering, aggregating, or performing calculations that help refine the results of the outer query.

For example:

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

Here, the subquery (SELECT AVG(salary) FROM employees) calculates the average salary, and the outer query then finds employees with salaries greater than this average.

Normal (Non-Correlated) Subquery:

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

Explanation: The subquery calculates the average salary once. The outer query then uses this value to filter employees with a higher salary.

Correlated Subquery:

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

Explanation: The subquery calculates the average salary for each department (using e1.department_id from the outer query). For each employee in the outer query, the inner query re-runs to calculate the department-specific average salary, creating a row-specific comparison.

Summary

Normal subqueries are independent of the outer query and run only once.

Correlated subqueries depend on each row of the outer query, executing repeatedly and adjusting based on the outer row values.