select running by itself but not in subquery-CodePudding

Ok so I'm stuck at something and I need your help. How can this run ok:

select salary from employees
            where job_id = 'AD_PRES' and rownum <= 5
            order by salary desc

and this not?

select * from employees
      where 
      salary in
(     
      select salary from employees
            where job_id = 'AD_PRES' and rownum <= 5
      order by salary desc
)

The error I get is: 00907. 00000 - "missing right parenthesis"

CodePudding user response：

In SQL, in general, table ordering is non-deterministic and an order cannot be guaranteed.

Your query:

select salary
from   employees
where  job_id = 'AD_PRES'
and    rownum <= 5
order by salary desc;

Will fetch rows from the table in a non-deterministic (random) order¹ and then it will apply the WHERE clause to only include rows where job_id = 'AD_PRES' and assign ROWNUM pseudo-column values to the rows in the order they are read and to stop after it has found 5 rows. AFTER that it will order those first (random) 5 rows in order of salary.

What you want is to apply the ORDER BY clause before filtering with ROWNUM:

SELECT salary
FROM   (
  SELECT salary
  FROM   employees
  WHERE  job_id = 'AD_PRES'
  ORDER BY salary DESC
)
WHERE  rownum <= 5

From Oracle 12, there is a simpler syntax:

SELECT salary
FROM   employees
WHERE  job_id = 'AD_PRES'
ORDER BY salary desc
FETCH FIRST 5 ROWS ONLY;

How can this run ok ... and this not?

As @OldProgrammer say, because IN is a set operation and applying an order to an unordered set does not make sense and the syntax does not allow for an ORDER BY clause to be used in that case. The "missing right parenthesis" error is raise because Oracle does not expect an ORDER BY clause in the IN list and, instead of that, does expect the sub-query to be closed with a right parenthesis

What you need to do is use a query without ORDER BY in the outer-most query of the sub-query or to use the new FETCH syntax.

select *
from   employees
where  salary in (
  SELECT salary
  FROM   (
    SELECT salary
    FROM   employees
    WHERE  job_id = 'AD_PRES'
    ORDER BY salary DESC
    -- You can use `ORDER BY` in a nested sub-query
  )
  WHERE  rownum <= 5
  -- No ORDER BY clause in the outer-most sub-query of an IN expression
)

or:

select *
from   employees
where  salary in (
  SELECT salary
  FROM   employees
  WHERE  job_id = 'AD_PRES'
  ORDER BY salary DESC
  FETCH FIRST 5 ROWS ONLY
)

db<>fiddle here

Due to the way it is implemented it may appear to be a deterministic (non-random) order as the rows are read from the data files in a consistent order. However, this is never guaranteed and there are circumstances when the order of rows will change; particularly on parallel systems or when a table has row movement enabled.