/ 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);
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”
This case-study also shows the scenario where we should go for JOIN rather than select subquery