Section 9 Quote

HANDS ON SECTION Definition: A Subquery is a “query within a query” -- something like a nested query. An example follows: ?HANDS-ON: Type, save, and test this program. --Subquery1.sql --Find a list of all employee who earn a salary equal to the minimum salary --in the Company SELECT emp_ssn, emp_last_name, emp_salary FROM employee WHERE emp_salary = (SELECT MIN(emp_salary) FROM employee); /* Subquery coded inside the WHERE clause */ The highlighted part is called a Subquery or an Inner query. The un-highlighted part is called an Outer query. A Subquery runs “inside out”. In other words, a subquery runs first. Only after it completes, then the Outer query runs next. After its run is complete, a Subquery generates a result for the Outer query. The Outer query uses the result provided to it by the Subquery as its input value. In the example above, the Subquery runs first and generates a minimum salary result of $25,000.00. The Outer query then uses this number to find and list all the employees who make this salary. After use, the result provided by the subquery is discarded. The nature and data type of what the Outer query needs, and what the inner Subquery provides must be compatible. The result from a Subquery can be a single scalar value (e.g., minimum salary 25000), a result set that contains a single column with multiple rows (e.g., a STATE field containing the values “IL”, “KY”, “TN”), or a result set that contains 2 or more columns. The table names used in the FROM clause in the Outer and inner Subquery may the same, or they may be different based on data requirement. In the example above, both queries use the same table employee. A Subquery can be coded inside the SELECT, FROM, W