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