What's the meaning of this Oracle SQL code?-CodePudding

I've been learning Oracle SQL recently and I came across the following code:

SELECT * 
FROM employees outer_emps
WHERE (2-1) = (
  SELECT COUNT(*)
  FROM employees inner_emps
  WHERE inner_emps.salary > outer_emps.salary
);

Could someone please help me understand the syntax?

CodePudding user response：

It returns employees whose salary is the 2nd highest.

For example:

SQL> select ename, sal from emp order by sal;

ENAME             SAL
---------- ----------
SMITH             840
JAMES             950
ADAMS            1100
WARD             1250
MARTIN           1250
MILLER           1300
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
SCOTT            3000   --> SCOTT and FORD have the 2nd highest salary
FORD             3000   -->
KING             5000   --> KING has the highest salary

14 rows selected.

SQL> select ename, sal
  2  from emp outer_emps
  3  where 1 = (select count(*) from emp inner_emps
  4             where inner_emps.sal > outer_emps.sal);

ENAME             SAL
---------- ----------
SCOTT            3000
FORD             3000

SQL>

If you used <, you'd get the 2nd lowest salary (that's James because SMITH's salary is 840 - the lowest - and JAMES follows with 950):

SQL> select ename, sal
  2  from emp outer_emps
  3  where 1 = (select count(*) from emp inner_emps
  4             where inner_emps.sal < outer_emps.sal);

ENAME             SAL
---------- ----------
JAMES             950

SQL>

I'd probably do it as follows; that's easier to understand:

SQL> with temp as
  2    (select ename, sal,
  3       rank() over (order by sal desc) rnk
  4     from emp
  5    )
  6  select ename,sal from temp
  7  where rnk = 2;

ENAME             SAL
---------- ----------
SCOTT            3000
FORD             3000

SQL>