oracle查询选择,sql – 具有内部选择查询错误的Oracle选择查询

我收到sql错误并尝试解决,任何指针都会有帮助,

//这会被执行

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

//这会被执行

SELECT deptid FROM department WHERE description LIKE '%application%'

ORDER BY createddate DESC

但以下查询抛出错误：

SELECT empid FROM employees WHERE deptid IN (SELECT deptid FROM department WHERE description LIKE '%application%'

ORDER BY createddate DESC);

错误：

ORA-00907：缺少右括号

00907. 00000 – “缺少右括号”

更新：07/14：

使用@dasblinkenlight中的确切解决方案进行更新：

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

“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:”

我尝试使用WHERE子句和’=’而不是’IN’的sql语句,它仍然抛出错误：’缺少右括号’.

结论1：

“不要在WHERE子句子查询中使用ORDER BY”

或者“where子句中的子查询不允许在Oracle中使用ORDER BY”

结论2

本案例研究还显示了我们应该选择JOIN而不是选择子查询的场景