select running by itself but not in subquery

Question

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"

I'm not an Oracle expert, but generally speaking you can't use `ORDER BY` in a derived-table expression. — Dai, Dec 04 '21 at 20:28
@Dai why is that ? In this context it makes sense I think, is that a more general rule ? — Johnny, Dec 04 '21 at 20:30
@Johnny check this :https://stackoverflow.com/questions/49269761/ora-00907-missing-right-parenthesis-in-subquery-when-using-order-by-and-rownum — Ergest Basha, Dec 04 '21 at 20:31
IN clause is a set operation,. There is no sort order and has no impact on the parent query. — OldProgrammer, Dec 04 '21 at 20:37
@OldProgrammer I don't get what you say. I sort for having the 5 biggest salaries. Then I check if that salary is amongst the 5 biggest. — Johnny, Dec 04 '21 at 20:42
`where` clause is applied before `order by`, so ordering has no impact on filtering — astentx, Dec 04 '21 at 20:54
rownum does not do what you think it does. use FETCH FIRST 5 ROWS ONLY — OldProgrammer, Dec 04 '21 at 21:50
Avoid using `ROWNUM`. That was the old way of limiting rows in Oracle; it was confusing and error prone. Use `FETCH FIRST 5 ROWS ONLY` instead, right after `ORDER BY`. — The Impaler, Dec 04 '21 at 21:59

MT0 · Accepted Answer · 2021-12-04T22:22:02.537

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.

Regarding the footnote: rows may also be read in a different order if some data from the table is already buffered (from earlier reads). Oracle may choose to read from the buffer - whatever happens to be in it; one has no control over what rows will be found there, and it may change from one execution to the next. — , Dec 05 '21 at 01:06
Also regarding the footnote - the optimiser might pick an execution plan that uses an index for the subquery, which again might change the order in which it happens to retrieve rows. Or the data might get moved or compressed by some scheduled task. I've seen production failures where a query relied on the "default" ordering, which worked fine until it didn't. — William Robertson, Dec 05 '21 at 12:02

select running by itself but not in subquery

1 Answers1