How to rewrite this correlated query as a JOIN? I'm just getting into SQL and am encountering some difficulty wrapping my head around correlated queries. I'm still trying to master it but am wondering what would be the right syntax for rewriting this statement as a JOIN. Essentially I have an employee list by department and want to know the highest salary in each department. Here's the query as a correlated query:

SELECT dept, name, salary 
FROM employees AS a 
WHERE salary &gt;= ALL 
(
  SELECT salary 
  FROM employees b 
  WHERE a.dept = b.dept AND salary &gt; 0
)
One variant:

SELECT
  a.dept,
  a.name,
  b.salary
FROM employees AS a 
JOIN (
  SELECT
    MAX(c.salary) salary,
    c.dept
  FROM employees AS c
GROUP BY c.dept
) b
  ON a.dept = b.dept


If you are using TSQL you can replace JOIN with CROSS APPLY on move ON clause inside and replace it with WHERE