Correlated Subqueries

Contact Us or call 1-877-932-8228
Correlated Subqueries

Correlated Subqueries

A correlated subquery has a join between the a subquery and the query that contains it. Table aliases are often required to clarify the relationship between tables in the inner and outer query - especially if they involve the same table. In terms of scope, the inner query can "see" an outer table's columns, but the inner query's columns are not visible to the outer query.

To discover the highest paid employee in each department, a correlated subquery is required.

Code Sample:

FROM employees e1 
WHERE e1.salary = (
	SELECT max(salary) 
	FROM employees e2 
	WHERE e1.department_id = e2.department_id

Both the inner and outer query reference the employees table. The outer query's employees table is aliased as e1, and the inner query's employees table is aliased as e2. Within the inner query there is a join on department_id.