Optimizing query with subselect in two tables

Table employee has two columns:

• ID
• NAME

Table external_job also has two columns:

• ID
• SALARY

I have to get one person who got the maximum salary. The result must have three columns and one row:

• ID
• NAME
• SALARY

I made a query but the client asked me not to use a sub-select query.

How can I do in this case?

My query is:

select *
  from (select a.id,
               a.name,
               (select sum(salary)
                  from external_job b
                 where b.id = a.id) salary
          from employee a
         order by salary desc)
 where rownum = 1

Answer

Use order by and some method for limiting the results to one row. In standard SQL this is:

select ej.id, e.name, ej.salary
from employee e join
     external_job ej
     on ej.id = e.id
order by ej.salary
fetch first 1 row only;

Not all databases support fetch first. Some use limit or select top or even more arcane constructs.