I searched a lot but I couldn´t find an explanation at all for my question. Let´s say I have a database with 100 employees and I want to write a query, that tells me the three employees, which earn the most. The query below works, but I have no idea why.
SELECT Name, salary
FROM employees e1
WHERE ( SELECT COUNT(*)
FROM employees
WHERE salary > e1.salary ) < 3;
As far as I know, a COUNT(*) function only returns the total number of rows in a table. How can it be, that this query still works and returns me the top three earners. I can´t wrap my head around it and the more I think about it, the more I get confused.
The where clause of your query uses a correlated subquery that dynamically counts how many employees have a salary higher than the current employee. The correlation is implemented by condition salary > e1.salary, where e1.salary refers to the outer row (the current employee). Only employees whose salary is outscored by 2 or less other employees are returned; others are filtered out. This is you get the top 3.
I find that expressing this with a window function is easier to understand:
select name, salary
from (
select
name,
salary,
rank() over(order by salary desc) rn
from employee
) t
where rn <= 3
Or, if you are running Oracle 12 or higher, you can use the fetch clause, which makes it straight forward:
select name, salary from employee order by salary fetch first 3 rows only;