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