Often, you want to retrieve rows from a table provided that values in a particular column are in another table. You can use IN as you did in Chapter 4. The nested SELECT statement provides the list that follows IN.
How It's Done
You might want the last names and telephones of employees who are department heads. To retrieve the information from the EMPLOYEE table, first determine the employees that are the heads of departments. You can do this through a subquery. You must enclose the subquery in parentheses. Enter:
select emp_lname, phone, dept_id
from employee
where emp_id in
(select dept_head_id
from department);
The subquery first retrieves all the employee IDs of department heads from the DEPARTMENT table. The outer SELECT statement then uses this list to retrieve the last name, phone, and department ID of these employees from the EMPLOYEE table.
The result looks like this:
EMP_LNAME PHONE DEPT_ID --------- ----- ------- Albertini 5083145366 2200 Alexander 5087394772 1110 Anderson 5083873664 6200 Baldwin 6173295757 6200 Bennett 5089487709 5000 Brooks 5089273644 3510 Carlson 6175553643 4600 Donelson 5084850432 3520 Griffin 5088449008 5200 Hamel 5083457789 1100 Jacobs <null> 5100 Johnson 5089253998 1120 Parker <null> 2210 Smith 6175563331 3530 Voltmer 6176635520 4500 15 rows processed
|
Copyright © 2014 CA.
All rights reserved.
|
|