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.

inner join

See equijoin.

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.

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.

Tip

To join together n tables, you need at least n-1 join conditions to avoid undesired Cartesian products, resulting from combining every row of one table with every row of one or more other tables. Cartesian products are discussed later in this chapter.

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      Accountant     111 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.

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 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!).

Warning

Use NATURAL JOIN only for ad hoc queries where you are very familiar with the column names of both tables. Adding a new column to a table that happens to have the same name as a column in another table will cause unexpected side effects with existing queries that use both tables in a NATURAL JOIN.

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 Relations        205 Higgins, Shelley           Accounting        206 Gietz, William             Accounting 106 rows selected. 

Tip

The JOIN ... ON clause is the only SQL:1999 equijoin clause that supports joining columns with different names.

Join Using with 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, 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.