This is a typical situation that can confuse an average student on the exam.
We all know that using a subquery that returns multiple rows if forbidden with parent query if parent query refer subquery with any of those =, >, >=, <, <=, <>, !=
If we use =, >, >=, <, <=, <>, != between parent query and a subquery, then parent query expects a single value only to be returned by the subquery.
Let’s have some examples:
1) This query below will produce all employee records with minimum salary
select * from employees where salary = (select min(salary) from employees );
2) This query below will produce all employee records with salary less than average salary
select * from employees where salary < (select avg(salary) from employees );
3) This query below will produce all employee records with salary greater than average salary
select * from employees where salary > (select avg(salary) from employees );
All these queries will work just fine and will produce a required results. But on the exam, you may see some little trick. A Subquery may be extended a little bit by adding grouping. See below
select * from employees where salary = (select min(salary) from employees group by department_id); select * from employees where salary < (select avg(salary) from employees group by department_id); select * from employees where salary > (select avg(salary) from employees group by department_id);
As you can see these three queries are almost the same, except they have a grouping inside them. And this is where a confusion coming. This is what I called a hidden trick that they normally use on the exam. In fact, by having grouping inside, subquery won’t return a single value anymore. It will return as many rows as a number of departments are. Therefore, all three queries with grouping inside subquery will produce an error ORA-01427: single-row subquery returns more than one row 01427. 00000 – “single-row subquery returns more than one row”
To avoid this error, you must use ANY or ALL or IN before the subquery :
select * from employees where salary IN (select min(salary) from employees group by department_id); select * from employees where salary < ALL (select avg(salary) from employees group by department_id); select * from employees where salary > ANY (select avg(salary) from employees group by department_id);
It looks easy, but many do not analyze the subquery as a whole and don’t pay attention to the grouping function exist within the subquery, and this is why they do mistakes.
Conclusion: If a subquery may return more than one row, the comparison operator must be able to accept multiple values. These operators are IN, NOT IN, ANY, and ALL.