The following statement returns employees who have more years of work experience than their department managers:
SELECT workers.name, workers.yrs_exp, workers.dept_no,
managers.name, managers.yrs_exp
FROM employee AS workers, employee AS managers
WHERE managers.dept_no = workers.dept_no
AND UPPER (managers.jobtitle) IN ('MANAGER' OR 'VICE PRES')
AND workers.yrs_exp > managers.yrs_exp;
The FROM clause in the preceding statement enables the employee table to be processed as though it were two identical tables: one named workers and the other named managers.
As in a normal join operation, the WHERE clause defines the conditions of the join, establishing dept_no as the column whose values are common to both tables.
The statement is processed by first selecting managers rows that contain a value of either ‘MANAGER’ or ‘VICE PRES’ in the jobtitle column. These rows are then joined to the workers rows using a merge join operation with this join condition:
- A workers row must contain a dept_no value that matches the dept_no value in a managers row.
- The matching workers row must also contain a yrs_exp value that is greater than the yrs_exp value in the managers row.
The following result is returned:
| name ---- |
yrs_exp ------ |
dept_no ------- |
name ---- |
yrs_exp ------ |
|---|---|---|---|---|
| Greene W | 15 | 100 | Jones M | 13 |
| Carter J | 20 | 200 | Watson L | 8 |
| Aguilar J | 11 | 600 | Regan R | 10 |
| Leidner P | 13 | 300 | Phan A | 12 |
| Ressel S | 25 | 300 | Phan A | 12 |