( fhuse | 2020. 10. 12., h – 15:43 )

MySQLhez nem értek, de left join felesleges a where miatt, szerintem

én valami ilyesmivel is próbálkoznék (persze nagyban függ milyen execution plant ad hozzá)

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`
FROM `employees`
INNER JOIN (select emp_no
                  , title 
                  , row_number() over(partition by `emp_no` order by `from_date` desc) as pos  
            from `titles` 
            WHERE `titles`.`title`='Staff' 
            ) as t 
ON
	(t.`emp_no` = `employees`.`emp_no`) AND
	(t.pos =1)

INNER JOIN (select emp_no
                  , `salary`
                 , row_number() over(partition by `emp_no` order by `from_date` desc) as pos 
          from `salaries` 
         WHERE `salary`>='120000' 
          ) as s 
ON
	(s.`emp_no` = `employees`.`emp_no`) AND
        (s.pos =1)
LIMIT 0, 20