Databases Reference
In-Depth Information
103 Hunold 60 9000
104 Ernst 60 6000
108 Greenberg 100 12000
109 Faviet 100 9000
...
193 Everett 50 3900
201 Hartstein 20 13000
205 Higgins 110 12000
38 rows selected.
As Janice expected, this query still shows that Hunold and Ernst make more
than the average salary for department 60.
Multiple-Column Subqueries
There are times when you need to use a subquery that compares more than
just one column between the parent query and the subquery. This is known as a
clause is used to compare the outer
query's columns to the columns of the subquery.
multiple-column subquery
. Typically, the
IN
Multiple-column subqueries can be rewritten as a compound
clause with mul-
tiple logical operators. However, this approach is not as readable or maintainable as
a multiple-column subquery.
WHERE
The boss, King, wants to be able to identify employees who make the same
salaries as other employees with the same job. He wants to specify an employee
number and have the query return the other employees who have the same job
title and make the same salary. Janice immediately realizes that this could be
written as a multiple-column subquery. She decides to try out the query on one
of the stock clerks, Hazel Philtanker, who has an employee number of 136:
multiple-column subquery
A subquery in which more than one col-
umn is selected for comparison to the
main query using the same number of
columns.
select employee_id, last_name, job_id, salary
from employees
where (job_id, salary) in
(select job_id, salary from employees
where employee_id = 136);
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- --------------- ---------- ----------
128 Markle ST_CLERK 2200
136 Philtanker ST_CLERK 2200
2 rows selected.
