Equijoins - Oracle DBA

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.

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:

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:

Pre-Oracle9i Equijoin Syntax

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 .

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:

Pre-Oracle9i Equijoin Syntax

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 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 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:

Oracle9i Equijoin Syntax

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 data type 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:

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:

Join Using

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:

Join On

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:

Join Using with Three Tables

Since the join will use common column names between each pair of tables, Janice's query uses the JOIN ... USING clause as follows:

Join Using with Three Tables

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.

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:

Non-equijoins

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:

Pre-Oracle9i Non-equijoin Syntax

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.

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:

Oracle9i Non-equijoin Syntax


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics