Using order in IN state
Let's say we have this silly query:
Select * From Emp Where Id In (Select Id From Emp)
Make a slight modification inside IN
by adding a sentence Order By
.
Select * From Mail Where Id In (Select Id From Mail Order By Id)
Then we get the following error:
ORA-00907: missing right parentheses
Oracle assumes that the IN clause will end after the From table is declared. As a result, the correct parenthesis is expected, but instead we give the order. Why can't we add ordering by the inner IN clause?
FYI : I am not asking for an equivalent request. After all, this is an example. I just don't understand why this error occurs.
source to share
Let's consider the condition x in (select something from somewhere)
. It returns true if x
equal to any of those something
returned from the request, regardless of whether it is the first, second, last, or anything in between. The return procedure is something
immaterial. Adding a clause order by
to a query often comes with significant performance gains, so I guess this limitation was introduced to prevent adding a clause that does not affect the correctness of the query on the one hand, and can be quite costly on the other.
source to share
It doesn't make sense to sort the values inside the IN clause . Think like this:
IN (a, b, c)
coincides with
IN (c, b, a)
IS ONLY AS
IN (b, c, a)
Internally, Oracle enforces the OR condition , so it is equivalent to:
WHERE id = a OR id = b OR id = c
Will it make sense to order conditions?
The order goes at its own expense. So when there is no need to sort, just don't do it. Again, Oracle applies the same rule.
When it comes to query performance, the optimizer must choose the best possible execution plan, i.e. with the lowest cost to achieve the desired result. ORDER BY is useless in this case, and Oracle has done a good job of not using it at all.
For documentation ,
Type of Condition Operation
----------------- ----------
IN Equal-to-any-member-of test. Equivalent to =ANY.
So when you need to check the value ANY for membership in a list of values, there is no need for an ordered list, only a random match does the job.
source to share
If you look at the Oracle SQL link (syntax diagrams) you will find the reason. ORDER BY is part of the "select" statement, while the clause IN
uses the lover level "subquery" operator. Your problem is related to the nature of the Oracle SQL grammar.
PS: there can be more getchas in the "subqueries", for example, several UNION
, MINUS
and then you can use ONLY one clause ORDER BY
as this only applies to the result of the operation UNION
. This won't work either:
select * from dual order by 1
union all
select * from dual order by 1;
source to share