SQL Basics Practice Test

A correlated subquery is a subquery that references a column from the outer query, so it must be evaluated for each row of the outer query. Because the inner query depends on values from the current outer row, the database re-runs it for every row, using that row’s data to determine the condition. This is why you often see correlations in EXISTS or in comparisons that relate the outer row to related data. For example, a query that asks for employees whose salary is above the average salary in their own department uses the department value from the outer row inside the inner query, making the inner query depend on each outer row. In contrast, a non-correlated subquery does not reference outer columns and can be executed just once, producing a result that the outer query uses later. An inner query that uses an aggregate or doesn’t reference outer values isn’t by itself a correlation; the key idea is the dependency on the outer query’s current row.