usecase:

/ this gets executed

SELECT empid FROM employees WHERE deptid IN (10,20,30,40 );

// this gets executed

SELECT deptid FROM department WHERE description LIKE '%application%' 
  ORDER BY createddate DESC 

but the below query throws error:

SELECT empid  FROM employees WHERE deptid IN (SELECT deptid FROM department WHERE description LIKE '%application%' 
  ORDER BY createddate DESC);

 

Solution

The problem is placing ORDER BY in the WHERE clause subquery. SQL syntax does not allow you to order elements of the subquery in the WHERE clause, because it does not change the result of the query overall

This article well explains many of the concepts – http://oraclequirks.blogspot.com/2008/01/ora-00907-missing-right-parenthesis.html

“ORA-00907: missing right parenthesis Clearly when one gets a message like this, the first reaction is probably to verify what parenthesis has been left out, but unfortunately there are no missing parentheses at all in this statement.

To cut it short, the untold syntax quirk is summarized as follows: don’t use ORDER BY inside an IN subquery.

Now, one may object that indeed it doesn’t make sense to use the ORDER BY inside an IN clause, which is true, because Oracle doesn’t care about the row order inside an IN clause:”

I tried the SQL statement with WHERE clause and ‘=’ instead of ‘IN’ and it still threw the error:’missing right parenthesis‘.

conclusion 1 :

“Don’t use ORDER BY in the WHERE clause subquery” or “Subqueries in the where clause are not allowed to use ORDER BY in Oracle”

Conclusion 2

This case-study also shows the scenario where we should go for JOIN rather than select subquery