Databases Reference
In-Depth Information
Since the join will use common column names between each pair of tables,
Janice's query uses the JOIN ... USING clause as follows:
select employee_id "Emp ID", last_name || ', ' ||
first_name "Name", city "City"
from employees
join departments using (department_id)
join locations using (location_id)
where department_id = 40;
Emp ID Name City
---------- -------------------------- --------------------
203 Mavris, Susan London
1 row selected.
The EMPLOYEES table is joined to DEPARTMENTS on the DEPARTMENT_ID col-
umn, and then the result of that join is joined with the LOCATIONS table on the
LOCATION_ID column. The result is filtered so that only the employees in depart-
ment 40 are on the report.
Non-equijoins
When joining two or more tables, you usually are joining on columns that have
the same value, such as department number or job ID. On occasion, however,
you might join two tables where the common columns are not equal. More spe-
cifically, a column's value in one table may fall within a range of values in
another table.
There is a table in the HR schema called JOBS, which lists each job in Scott's
company, along with the salary ranges for a given job. Janice will query this table
using both the pre-Oracle9
i
syntax and the Oracle9
i
syntax. The JOBS table is
structured as follows:
Name Null? Type
-------------------------- -------- -------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
Pre-Oracle9
i
Non-equijoin Syntax
Janice knows that the EMPLOYEES table has a salary column and a job ID column.
She wants to make sure that the salary for a given employee falls within the range
