Correlation of queries

You can always do this by using aliases.
As an example see

SELECT name
 FROM employee AS e1
 WHERE salary > (SELECT avg(salary)
    FROM employee
    WHERE department = e1.department)

I actually got the query from this[^] site, and you can read more about correlated subqueries on that web page.