strange WHERE clause in sql query ; WHERE (select ...) = 3-CodePudding

Usually with WHERE clause in SQL queries, in WHERE we check from the table if a condition is met, but with the following code:

SELECT name
FROM teacher as T1
WHERE (
SELECT COUNT (DISTINCT T2.salary)
FROM teacher as T2
WHERE T2.salary > T1.salary
) = 3;

This following sub-query Sub is returning a number.

(SELECT COUNT (DISTINCT T2.salary)
FROM teacher as T2
WHERE T2.salary > T1.salary)

but I don't understand how we check WHERE Sub = 3, it doesn't make sense to me. Can you explain?

The entire query finds the 4th highest salary of teacher.

CodePudding user response：

You can use a subquery anywhere where you can use an expression, so why not on the left side of the equality operator?

But this way to solve the problem is rather ugly. Why not

SELECT name
FROM (SELECT DISTINCT ON (salary)
             name, salary
      FROM teacher) AS t
ORDER BY salary DESC
OFFSET 3 FETCH FIRST 1 ROWS ONLY;

CodePudding user response：

Perhaps more comprehensive with this query :

WITH T AS
(
SELECT name, COUNT(*) OVER(ORDER BY salary) AS N
FROM teacher as T1
)
SELECT name
FROM   T 
WHERE  N > 3