sql - Is there a way to re-write this query without subqueries? -

i've got table of employees hr schema (oracle). how completed task (select employees minimal salary in departments, if belong of them)

    select d.employee_id,d.last_name,d.salary,d.department_id     [helpdatabase].[dbo].[employees] d,          [helpdatabase].[dbo].[employees] e     d.department_id not null        , e.department_id not null       , d.employee_id=e.employee_id       , d.salary=any (           select min(e.salary)            [helpdatabase].[dbo].[employees]            group e.department_id) 

query working correctly, i've been told there's way without using subquery.

supposing want find employees earn lowest salaries in departments:

yes, can done without subquery. idea can employees not exists employee in same department , lower salary:

select * emp not exists (   select *   emp less   less.department_id = emp.department_id , less.salary < emp.salary ); 

a not exists query can written anti-join. is: outer join lesser earning employees; remove results employees found such persons.

select * emp left join emp less on less.department_id = emp.department_id , less.salary < emp.salary less.employee_id null; 

you not this, though, because it's trick, , it's bit obfuscated query doing. not exists clearer here, asking "give me employees not exist collegues earn less".

(well, in case might use entirely else, such where (department_id, salary) in (select department_id, min(salary) ...) or rank() on partition department_id order salary.)

but anti joins solution when dbms proves weak on not exists queries. way write same thing.