Subquery usage with ANY, ALL, IN and =, >, >=, <, <=, <>, !=, ^= and grouping

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.

Leave a comment

Your email address will not be published. Required fields are marked *