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 specifically, 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-Oracle9i syntax and the Oracle9i 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-Oracle9i 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 specified for the job assigned to that employee. The first employee she checks is the boss’s daughter, Janette King, who has an employee ID of 156. The query below does a non-equijoin on the EMPLOYEES and JOBS tables to accomplish the salary range comparison:

select e.job_id "Empl Job", e.salary, j.job_id "Job",   j.min_salary, j.max_salary from employees e, jobs j where e.salary between j.min_salary and j.max_salary and e.employee_id = 156; Empl Job       SALARY Job        MIN_SALARY MAX_SALARY ---------- ---------- ---------- ---------- ---------- SA_REP          10000 FI_MGR           8200      16000 SA_REP          10000 AC_MGR           8200      16000 SA_REP          10000 SA_MAN          10000      20000 SA_REP          10000 SA_REP           6000      12000 SA_REP          10000 PU_MAN           8000      15000 SA_REP          10000 IT_PROG          4000      10000 SA_REP          10000 MK_MAN           9000      15000 SA_REP          10000 PR_REP           4500      10500 8 rows selected.

What does this query output tell Janice? First of all, it appears that there is no nepotism going on at the company, as Janette’s salary falls within the normal range for a sales representative, albeit near the high end of the range. It also is apparent that her salary is in the range for seven other positions at the company.

Oracle9i Non-equijoin Syntax

Janice wants to see if the non-equijoin query is any easier to perform using the newer Oracle9i syntax. She realizes that since she is doing a non-equijoin, she is not able to use the NATURAL JOIN or the JOIN ... USING syntax, since both of those formats assume equality between the implicit or explicit columns. It seems like the JOIN ... ON syntax will work, though, since she can specify a condition between two columns in that syntax. The query looks very similar to the previous query, but as with all Oracle9i joins, the join conditions are moved from the WHERE clause to the FROM clause:

select e.job_id "Empl Job", e.salary, j.job_id "Job",   j.min_salary, j.max_salary from employees e join jobs j on   e.salary between j.min_salary and j.max_salary where employee_id = 156; Empl Job       SALARY Job        MIN_SALARY MAX_SALARY ---------- ---------- ---------- ---------- ---------- SA_REP          10000 FI_MGR           8200      16000 SA_REP          10000 AC_MGR           8200      16000 SA_REP          10000 SA_MAN          10000      20000 SA_REP          10000 SA_REP           6000      12000 SA_REP          10000 PU_MAN           8000      15000 SA_REP          10000 IT_PROG          4000      10000 SA_REP          10000 MK_MAN           9000      15000 SA_REP          10000 PR_REP           4500      10500 8 rows selected.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net