The inner query is executed first producing a query result The main query block

The inner query is executed first producing a query

This preview shows page 272 - 277 out of 546 pages.

The inner query is executed first, producing a query result. The main query block is then processed and uses the values that were returned by the inner query to complete its search condition. In fact, the main query appears to the Oracle server as follows: SELECT last_name, salary, department_id FROM employees WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);
Image of page 272
Oracle Database 10 g : SQL Fundamentals I 6-15 6-15 Copyright © 2004, Oracle. All rights reserved. SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; Using the ANY Operator in Multiple-Row Subqueries 9000, 6000, 4200 Multiple-Row Subqueries (continued) The ANY operator (and its synonym, the SOME operator) compares a value to each value returned by a subquery. The slide example displays employees who are not IT programmers and whose salary is less than that of any IT programmer. The maximum salary that a programmer earns is $9,000. < ANY means less than the maximum. > ANY means more than the minimum. = ANY is equivalent to IN .
Image of page 273
Oracle Database 10 g : SQL Fundamentals I 6-16 6-16 Copyright © 2004, Oracle. All rights reserved. SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; Using the ALL Operator in Multiple-Row Subqueries 9000, 6000, 4200 Multiple-Row Subqueries (continued) The ALL operator compares a value to every value returned by a subquery. The slide example displays employees whose salary is less than the salary of all employees with a job ID of IT_PROG and whose job is not IT_PROG . >ALL means more than the maximum, and <ALL means less than the minimum. The NOT operator can be used with IN , ANY , and ALL operators.
Image of page 274
Oracle Database 10 g : SQL Fundamentals I 6-17 6-17 Copyright © 2004, Oracle. All rights reserved. SELECT emp.last_name FROM employees emp WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr); no rows selected Null Values in a Subquery Returning Nulls in the Resulting Set of a Subquery The SQL statement in the slide attempts to display all the employees who do not have any subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value, and hence the entire query returns no rows. The reason is that all conditions that compare a null value result in a null. So whenever null values are likely to be part of the results set of a subquery, do not use the NOT IN operator. The NOT IN operator is equivalent to <> ALL . Notice that the null value as part of the results set of a subquery is not a problem if you use the IN operator. The IN operator is equivalent to =ANY . For example, to display the employees who have subordinates, use the following SQL statement: SELECT emp.last_name FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);
Image of page 275
Oracle Database 10 g : SQL Fundamentals I 6-18 Returning Nulls in the Resulting Set of a Subquery (continued) Alternatively, a WHERE
Image of page 276
Image of page 277

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture