【文件属性】：
文件名称：这个语句错在哪里？-SQL语言基础
文件大小：5.26MB
文件格式：PPT
更新时间：2021-04-25 19:38:22
SQL 基础
这个语句错在哪里？
*
ERROR:
ORA-01427: single-row subquery returns more than
one row
no rows selected
SQL> SELECT empno, ename
2 FROM emp
3 WHERE sal =
4 (SELECT MIN(sal)
5 FROM emp
6 GROUP BY deptno);
单行操作符用在多行子查询上
*
SELECT empno, ename FROM emp WHERE sal =
(SELECT MIN(sal) FROM emp GROUP BY deptno);
Errors with Subqueries
One common error with subqueries is more than one row returned for a single-row subquery.
In the SQL statement on the slide, the subquery contains a GROUP BY (deptno) clause, which implies that the subquery will return multiple rows, one for each group it finds. In this case, the result of the subquery will be 800, 1300, and 950.
The outer query takes the results of the subquery (800, 950, 1300) and uses these results in its WHERE clause. The WHERE clause contains an equal (=) operator, a single-row comparison operator expecting only one value. The = operator cannot accept more than one value from the subquery and hence generates the error.
To correct this error, change the = operator to IN.