SubqueriesChapter 6ObjectivesAfter completing this lesson, you should be able to do thefollovving:•Describe the types of problems thatsubqueries can solve•Define subqueries•List the types of subqueries•Write single-row and multiple-rowsubqueries1
Using a Subquery to Solve a Problem"Who has a salary greater than Jones'?""Which employees have a salary greaterthan Jones' salary?""What is Jones' salary?"2
SubqueriesSELECTselect_listFROMtableWHEREexpr operator( SELECTselect_ListFROMtable);•The subquery (inner query) executesonce before the main query.•The result of the subquery is used bythe main query (outerquery).A subquery is a SELECT statement that is embedded in a clause of anotherSELECT statement. You can build powerful statements out of simple ones byusing subqueries. They can be very useful when you need to select rows from atable with a condition that depends on the data in the table itself.You can place the subquery in a number of SQL clauses•WHERE clauss•HAVING clause•FROM clauseln the syntax;operatorincludes a comparison operatör such as>,=, or INNote:Comparison operutors fall iııto two classes: single-row operators( > , = ,>= ,< ,< > ,<= )and multiple-row operators ( IN , ANY , ALL ).3
Using a SubquerySELECT enameFROM EMPWHEREsal>( SELECT salFROM empWHERE empno=7566);ENAMEFORDSCOTTKINGFORD4
SELECT empno, ename, jobFROM empWHERE sal > ALL( SELECT AVG(sal)FROM empGROUP BY deptno ) ;EMPNOENAMEJOB7566 JONESMANAGER7788 SCOTTANALYST7839 KINGPRESIDENT7902 FORDANALYST10
Guidelines for Using Subqueries•Enclose subqueries in parentheses.•Place subqueries on the right side of thecomparison operator.•Do not add an ORDER BY clause to asubquery.•Use single-row operators with single-row subqueries.•Use multiple-row operators withmultiple-row subqueries.