Multiple-Row Subqueries

• Return more than one row
• Use multiple-row comparison operators

Using the ANY Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';

Using the ALL Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ALL (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';
Null Values in a Subquery :

SELECT emp.ename FROM emp emp WHERE emp.empno NOT IN (SELECT mgr.mgr FROM emp mgr);

• The SQL statement in the slide attempts to display all the emp who do not have any subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value, and hence the entire query returns no rows
• Here we have to use IS NOT NULL in the inner query