Subqueries in SQL

Single-row Subqueries:

• Subquery returns only one row

SQL> select min(sal) from emp;

MIN(SAL)
----------
800

• Use single-row comparison operators (=, >, >=, <, <=, <>)Example:

SQL> select ename, job, sal from emp
where sal>(select min(sal) from emp);

You will get an error, if you use single-row operator and if your subquery returns more than one row
SQL> select ename, job, sal from emp
where sal>(select min(sal) from emp group by deptno);
where sal>(select min(sal) from emp group by deptno)
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

Multiple-row Subqueries:

• Subquery returns multiple rows

SQL> select min(sal) from emp group by deptno;

MIN(SAL)
----------
950
800
1300

• Use multiple-row comparison operators (IN, ANY, ALL)

SQL> select ename, job, sal from emp
where sal select ename, job, sal from emp
where sal<=ALL(select min(sal) from emp group by deptno);
no rows selected

ALL: Compare value to every value returned by the subquery

HAVING clause with sub-queries
Subqueries are executed first and returns the results into the HAVING clause of main query
Ex:
SQL> select min(sal) from emp where deptno=20; (subquery)

MIN(SAL)
----------
800

SQL> select deptno, min(sal) from emp group by deptno
2 having min(sal)>(select min(sal) from emp where deptno=20);

DEPTNO MIN(SAL)
---------- ----------
30 950
10 1300