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

Single-Row Subqueries

• Return only one row
• Use single-row comparison operators

Executing Single-Row Subqueries :

SELECT ename, job, sal FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7698) AND sal > (SELECT sal FROM emp WHERE empno = 7900);

Using Group Functions in a Subquery :

SELECT ename, job, sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);

The HAVING Clause with Subqueries :

• The PostgreSQL server executes subqueries first
• The PostgreSQL server returns results into the HAVING clause of the main query

SELECT deptno, MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);

What Is Wrong with This Statement ?

SELECT empno, ename FROM emp WHERE sal = (SELECT MIN(sal) FROM emp GROUP BY deptno);

• ERROR : more than one row returned by a subquery used as an expression
• Single-row operator with multiple-row subquery