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
  

Using Subqueries to Solve Queries

• Who has a sal greater than Scott’s ?

• 

Subquery Syntax :

• SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

• The subquery (inner query) executes once before the main query (outer query)
• The result of the subquery is used by the main query

Using a Subquery :

• SELECT ename FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = ‘Scott');

Guidelines for Using Subqueries :

1. Enclose subqueries in parentheses
2. Place subqueries on the right side of the comparison condition
3. The ORDER BY clause in the Subquery is not needed unless you are performing Top-N analysis
4. Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries

Types of Subqueries :

• Single-row subquery

• Multiple-row subquery