Find max salary and name of employee, if multiple records than print all

I want to print name and salary amount of the employee which has highest salary, till now its okay but if there are multiple records than print all. There are two table given :-

EMPLOYEE TABLE :-

SALARY TABLE:-

my query is: -

SELECT E.NAME, S.AMOUNT 
FROM `salary` S, 
     employee E 
WHERE S.EMPLOYEE_ID = E.ID 
  and S.AMOUNT = (SELECT max(`AMOUNT`) 
                  FROM `salary`)

is there any better way to find out the solution ?

It is "with ties" functionality what you're trying to achieve. Unfortunately mySQL doesn't support that (in the docs there is nothing to add to the "LIMIT" part of the query), so you have no other option rather than looking for max salary first and filter records afterwards.

So, your solution is fine for that case.

Alternatively, if you're on version 8 and newer, you may move the subquery to the with clause

with max_sal as (
  select max(amount) ms from salary
)

SELECT E.NAME, S.AMOUNT 
FROM salary S
JOIN employee E
  ON S.EMPLOYEE_ID = E.ID
JOIN max_sal ms
  ON S.AMOUNT = ms.ms

or search for it in the join directly

SELECT E.NAME, S.AMOUNT 
  FROM salary S
  JOIN employee E
    ON S.EMPLOYEE_ID = E.ID
  JOIN (select max(amount) ms from salary) ms
    ON S.AMOUNT = ms.ms

But I'm sure it won't get you any better performance