In subqueries, regardless of whether one row or multiple rows are returned, each of these rows contains only one column worth of data to compare to the main query level. The main query can be set up to handle multiple columns in each row returned, too.
For example
select deptno,ename,job,sal, from emp
where (deptno, sal) in (select deptno, max(sal)
from emp group by deptno);
Some points to note are:
- We must enclose the multiple column requested in the main query in parenthesis, otherwise the query result in an “invalid relational operator” error.
- The column reference in both the main query’s where clause and the subquery must match positionally- i.e. since deptno is selected first in the main query so it must be select first in the subquery also
