5.2. Equijoins
Equijoins are also commonly known as simple joins, or inner joins. Given two or more tables, an equijoin will return the results of these tables where a common column between any given pair of tables has the same value (an equal value). Equijoins are typically joins between foreign keys in one table to a primary key in another table.
equijoin
A join between two tables where rows are returned if one or more columns in common between the two tables are equal and not NULL.
5.2.1. Pre-Oracle9i Equijoin Syntax
The boss, King, gets his employee report with only the department ID on it, because the query used for the report is based on only the EMPLOYEES table. When the company was smaller, he knew automatically that department 100 was the Finance department, and so on. But now, with almost 30 departments in the company, he needs to see the department name in the report. That information is in the DEPARTMENTS table. Janice will join the two tables on the common column, DEPARTMENT_ID, and produce a report that is much more readable:
inner join
See equijoin.
select employee_id"Emp ID", last_name || ', ' ||
first_name"Name", department_name"Dept"
from employees e, departments d
where e.department_id = d.department_id;
Emp ID Name Dept
---------- ------------------------- --------------------
100 King, Steven Executive
101 Kochhar, Neena Executive
102 De Haan, Lex Executive
103 Hunold, Alexander IT
104 Ernst, Janice IT
105 Austin, David IT
...
201 Hartstein, Michael Marketing
202 Fay, Pat Marketing
203 Mavris, Susan Human Resources
204 Baer, Hermann Public Relations
205 Higgins, Shelley Accounting
206 Gietz, William Accounting
106 rows selected.Notice that table aliases are used. You've already seen quite a few column aliases in previous examples, and tables can be aliased also, either for clarity or for performance reasons. In this case, the aliases are necessary to identify which columns in which table are to be compared in this query. Typically, the column names match, but that is not a requirement for columns that are matched in a WHERE clause.
King tells Janice that the report looks good, but he also wants to see the full job description for each employee. Janice adds another table to the query and expands the WHERE clause. She also adds an ORDER BY clause to ensure that the report stays in employee ID order:
select employee_id"Emp ID", last_name"Name", department_name"Dept", job_title"Job" from employees e, departments d, jobs j where e.department_id = d.department_id
and e.job_id = j.job_id order by employee_id; Emp ID Name Dept Job ------ ---------- ---------- ----------------------------- 100 King Executive President 101 Kochhar Executive Administration Vice President 102 De Haan Executive Administration Vice President 103 Hunold IT Programmer 104 Ernst IT Programmer 105 Austin IT Programmer 106 Pataballa IT Programmer ... 205 Higgins Accounting Accounting Manager 206 Gietz Accounting Public Accountant 106 rows selected.
King is still not satisfied with the report because it's too long. He wants to see only information about the Finance and Purchasing department people on a regular basis. Janice updates the query one more time to add another WHERE condition to the query:
select e.employee_id"Emp ID",
e.last_name"Name", d.department_name"Dept",
j.job_title"Job"
from employees e, departments d, jobs j
where e.department_id = d.department_id
and e.job_id = j.job_id
and e.department_id in (30, 100)
order by e.employee_id;
Emp ID Name Dept Job
------- ------------ ------------ --------------------
108 Greenberg Finance Finance Manager
109 Faviet Finance Accountant
110 Chen Finance Accountant111 Sciarra Finance Accountant
112 Urman Finance Accountant
113 Popp Finance Accountant
114 Raphaely Purchasing Purchasing Manager
115 Khoo Purchasing Purchasing Clerk
116 Baida Purchasing Purchasing Clerk
117 Tobias Purchasing Purchasing Clerk
118 Himuro Purchasing Purchasing Clerk
119 Colmenares Purchasing Purchasing Clerk
12 rows selected.Janice already knew the department numbers to use with the IN operator.
5.2.2. Oracle9i Equijoin Syntax
The query that Janice wrote in the previous section works great. However, with all of the conditions specified in the WHERE clause, including both the table joins and the result filter, it gets cluttered fast. Most of the new options available in the Oracle9i and later syntax for joins will help make the query look cleaner, so that it is easier to read and understand. Equijoins can be constructed using the syntax NATURAL JOIN, JOIN USING, and JOIN ON.
Natural Join
Janice is quickly figuring out how to use the new Oracle9i syntax. She rewrites one of the first queries she wrote in this chapter, joining just the EMPLOYEES and DEPARTMENTS tables. She uses the NATURAL JOIN clause, since this method will implicitly join the two tables on columns with the same name:
select employee_id"Emp ID", last_name || ', ' ||
first_name"Name", department_name"Dept"
from employees natural join departments;
Emp ID Name Dept
---------- -------------------- --------------------
101 Kochhar, Neena Executive
102 De Haan, Lex Executive
104 Ernst, Janice IT
105 Austin, David IT
106 Pataballa, Valli IT
107 Lorentz, Diana IT
109 Faviet, Daniel Finance
...155 Tuvault, Oliver Sales
184 Sarchand, Nandita Shipping
185 Bull, Alexis Shipping
186 Dellinger, Julia Shipping
187 Cabrio, Anthony Shipping
202 Fay, Pat Marketing
206 Gietz, William Accounting
32 rows selected.Janice is scratching her head, because her first query returned 106 rows, while this one returns only 32. She realizes that the simplicity of the NATURAL JOIN method is a double-edged sword. NATURAL JOIN matches on all columns that have the same name and datatype between the tables. On closer inspection, it turns out that the EMPLOYEES and the DEPARTMENTS tables have both the DEPARTMENT_ID and MANAGER_ID columns in common. The query she wrote is effectively the same as writing this query in Oracle8i:
select employee_id"Emp ID", last_name || ', ' || first_name"Name", department_name"Dept" from employees e, departments d where e.manager_id = d.manager_id and e.department_id = d.department_id;
This is clearly not what she is looking for. It doesn't make much sense to join on the MANAGER_ID column because the MANAGER_ID column in the EMPLOYEES table is the MANAGER_ID of the employee, whereas the MANAGER_ID column in the DEPARTMENTS table is the manager of the department itself. The query does return the employees whose manager is a department manager, but this is not what King requested (yet!).
|
Join Using
Janice decides to scale back a bit and use another form of the Oracle9i join syntax that still saves some typing but is more explicit on which columns to join: JOIN ... USING. This form of an equijoin specifies the two tables to be joined and the column that is common between the tables. Janice's new query looks like this:
select employee_id"Emp ID", last_name || ', ' || first_name"Name", department_name"Dept"
from employees join departments using (department_id);
Emp ID Name Dept
---------- -------------------------- --------------------
100 King, Steven Executive
101 Kochhar, Neena Executive
102 De Haan, Lex Executive
103 Hunold, Alexander IT
104 Ernst, Janice IT
105 Austin, David IT
106 Pataballa, Valli IT
...
201 Hartstein, Michael Marketing
202 Fay, Pat Marketing
203 Mavris, Susan Human Resources
204 Baer, Hermann Public Relations
205 Higgins, Shelley Accounting
206 Gietz, William Accounting
106 rows selected.Join On
This particular form of an equijoin appears to be a good compromise between simplicity and accuracy, but Janice knows that she'll sooner or later use another form of an equijoin, the JOIN ... ON syntax. She rewrites the query once more as follows:
select employee_id"Emp ID", last_name || ', ' ||
first_name"Name", department_name"Dept"
from employees e join departments d
on e.department_id = d.department_id;
Emp ID Name Dept
---------- -------------------------- --------------------
100 King, Steven Executive
101 Kochhar, Neena Executive
102 De Haan, Lex Executive
103 Hunold, Alexander IT
...
203 Mavris, Susan Human Resources
204 Baer, Hermann Public Relations205 Higgins, Shelley Accounting
206 Gietz, William Accounting
106 rows selected.
|
Join Usingwith Three Tables
Later in the afternoon, one more request comes in from King: He wants to see a list of employees similar to the query Janice just ran, but instead of departments, he wants to see the city where the employee is working, and only employees in department 40, Human Resources. Looking at the EMPLOYEES table, the DEPARTMENTS table, and the LOCATIONS table, you can see that there is no direct route from EMPLOYEES to LOCATIONS. Janice must "go through" the DEPARTMENTS table to fulfill King's request. She must take the following route to get from EMPLOYEES to LOCATIONS:
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 column, 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 department 40 are on the report.
5.2.3. 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)
5.2.4. 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, because 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.
5.2.5. 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.