Team LiB   Previous Section   Next Section

3.1 Inner Joins

An inner join returns the rows that satisfy the join condition. Let's take an example to understand the concept of a join. Say you want to list the name and department name for each employee. To do this, you would use the following SQL statement: 

SELECT E.LNAME, D.NAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_ID = D.DEPT_ID;

LNAME      NAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

This example queries two tables, because the employee name is stored in the EMPLOYEE table, whereas the department name is stored in the DEPARTMENT table. Notice that the FROM clause lists two tables EMPLOYEE and DEPARTMENT, separated by a comma ( , ). If you need to join three or more tables, you have to specify all the tables in the FROM clause separated by commas. The SELECT list may include columns from any of the tables specified in the FROM clause.

Note the use of table aliases in this query. It is common practice to use table aliases while selecting data from multiple tables. Whenever there is an ambiguity in the column names, you must use a table alias (or the table name) to qualify any ambiguous column names. For example, the column name DEPT_ID appears in both the tables. Therefore, the table aliases E and D are used in the WHERE clause to ask Oracle to equate DEPT_ID column from EMPLOYEE table with the DEPT_ID column from the DEPARTMENT table. Note that the table aliases have been used with the columns in the SELECT clause as well, even though the column names are unambiguous. It is good practice to use table aliases everywhere in a query if you are using them at all.

3.1.1 Cartesian Product

If you don't specify the join condition while joining two tables, Oracle combines each row from the first table with each row of the second table. This type of result set is called as a Cartesian product. The number of rows in a Cartesian product is the product of the number of rows in each table. Here's an example of a Cartesian product: 

SELECT E.LNAME, D.NAME
FROM EMPLOYEE E, DEPARTMENT D;

LNAME      NAME
---------- --------------
SMITH      ACCOUNTING
ALLEN      ACCOUNTING
WARD       ACCOUNTING
JONES      ACCOUNTING
MARTIN     ACCOUNTING
BLAKE      ACCOUNTING
...
...
...
SCOTT      OPERATIONS
KING       OPERATIONS
TURNER     OPERATIONS
ADAMS      OPERATIONS
JAMES      OPERATIONS
FORD       OPERATIONS
MILLER     OPERATIONS

56 rows selected.

Note that since the query didn't specify a join condition, each row from the EMPLOYEE table is combined with each row from the DEPARTMENT table. Needless to say, this result set is of little use. More often than not a Cartesian product produces a result set containing misleading rows. Therefore, unless you are sure that you want a Cartesian product, don't forget to include the join condition when you specify more than one table in the FROM clause.

3.1.2 Join Condition

Usually when you perform a join, you specify a condition in the WHERE clause that relates the tables specified in the FROM clause. This condition is referred to as the join condition. The join condition specifies how the rows from one table will be combined with the rows of another table. Usually, the join condition is applied to the foreign key columns. In the first example in the previous section, the WHERE clause specifies the join condition by which the DEPT_ID column of the EMPLOYEE table is equated with the DEPT_ID column of the DEPARTMENT table: 

WHERE E.DEPT_ID = D.DEPT_ID

To perform the join, Oracle picks up one combination of rows from the two tables, and checks to see whether the join condition is true. If the join condition is true, Oracle includes this combination of rows in the result set. The process is repeated for all combinations of rows from the two tables. Some of the things that you should know about the join condition are discussed in the following list.

  • The columns specified in the join condition need not be specified in the SELECT list. In the following example, the join condition involves the DEPT_ID column from the EMPLOYEE and DEPARTMENT tables; however, the DEPT_ID column is not selected: 

    SELECT E.LNAME, D.NAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_ID = D.DEPT_ID;

  • Usually the join condition is specified on the foreign key columns of one table and the primary key or unique key columns of another table. However, you can specify other columns as well. Each join condition involves columns that relate two tables.

  • A join condition may involve more than one column. This is usually the case when a foreign key constraint consists of multiple columns.

  • The total number of join conditions is always equal to the total number of tables less one.

  • A join condition must involve columns with compatible datatypes. Note that the datatype of the columns involved in a join condition need to be compatible, not the same. Oracle performs automatic datatype conversion between the join columns, if required.

  • It is not necessary that a join condition involve the equal to (=) operator. A join condition may contain other operators as well. Joins involving other operators are discussed later in this section.

3.1.3 Equi-Join Versus Non-Equi-Join

The join condition determines whether the join is an equi-join or a non-equi-join. When a join condition relates two tables by equating the columns from the tables, it is an equi-join. When a join condition relates two tables by an operator other than equality, it is a non-equi-join. A query may contain equi-joins as well as non-equi-joins.

Equi-joins are the most common join type. For example, if you want to list all the parts supplied by all the suppliers, you can join the SUPPLIER table with the PART table by equating the SUPPLIER_ID from one table to that of the other: 

SELECT S.NAME SUPPLIER_NAME, P.NAME PART_NAME
FROM SUPPLIER S, PART P
WHERE S.SUPPLIER_ID = P.SUPPLIER_ID;

However, there are situations in which you need non-equi-joins to get the required information. For example, if you want to list the INVENTORY_CLASS of each PART, you need to execute the following query: 

SELECT P.NAME PART_NAME, C.CLASS INV_CLASS
FROM PART P, INVENTORY_CLASS C
WHERE P.UNIT_COST BETWEEN C.LOW_COST AND C.HIGH_COST;

Note the use of the BETWEEN operator while relating the UNIT_COST column from the PART table with the LOW_COST and HIGH_COST columns of the INVENTORY_CLASS table.

    Team LiB   Previous Section   Next Section