| Usually, when you write a join query, you specify a condition that conveys a relationship between 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. This join condition is usually applied to the foreign key columns of one table and the primary or unique key columns of another table. In the previous example, the ON clause specifies the join condition by which the location_id column of the department table is equated with the location_id column of the location table: ON department.location_id = location.location_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. This process is repeated for all combinations of rows from the two tables. Some of the things that you should know about join conditions are discussed in the following list: The columns specified in a join condition need not be specified in the SELECT list. In the following example, the join condition involves the location_id column from the department and location tables; however, the location_id column is not selected: SELECT d.dept_id, d.name, l.regional_group FROM department d JOIN location l ON d.location_id = l.location_id;
Table Aliases It is a common practice to use table aliases when 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 name. For example: SELECT d.dept_id, d.name, l.regional_group FROM department d JOIN location l ON d.location_id = l.location_id;
In this example, the column name location_id appears in both the tables. Therefore, the table aliases d and l are used in the ON clause to ask Oracle to equate the location_id column from the department table with the location_id column from the location table. The table aliases have been used with the columns in the SELECT clause as well, even though those column names are unambiguous. It is a good practice to use table aliases everywhere in a query if you are using them at all. |
Usually a 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 join on other columns as well. A join condition involves columns that relate two tables in some logical way. 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 in a query is always equal to the total number of tables less one. The data types of the columns involved in a join condition need to be compatible, but not necessarily the same. Oracle performs implicit data type 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 chapter in Section 3.3.4.
3.2.1 The USING Clause In this chapter's first example, the join condition was specified in the ON clause, which contained an expression defining the relationship between the two tables. Specifying the join condition can be simplified if the following conditions hold true: The join depends on an equality condition between two columns, or between sets of two columns, to relate the rows from the two tables. The names of the join columns are identical in both the tables.
If these two conditions are satisfied, you can apply the USING clause to specify the join condition. Earlier, you saw the following example of a join query: SELECT department.location_id, department.name, location.regional_group FROM department JOIN location ON department.location_id = location.location_id;
The column involved in the join condition (location_id) is named identically in both the tables, and its value must be the same in both tables. Therefore, this join query can be rewritten as: SELECT location_id, department.name, location.regional_group FROM department JOIN location USING (location_id);
The USING clause affects the semantics of the SELECT clause. The USING clause tells Oracle that the tables in the join have identical names for the column in the USING clause. Oracle then merges those two columns, and recognizes only one such column with the given name. If you include a join column in the SELECT list, Oracle doesn't allow you to qualify that column with a table name (or table alias). If you attempt to qualify a join column name in the SELECT list using either an alias or a table name, you will get an error: SELECT department.location_id, department.name, location.regional_group FROM department JOIN location USING (location_id); SELECT department.location_id, department.name, location.regional_group * ERROR at line 1: ORA-25154: column part of USING clause cannot have qualifier
This is why our USING query did not alias the location_id column in the SELECT list. 3.2.2 Conditions Involving Multiple Columns Quite often you will encounter a join condition that involves multiple columns from each table. If a join condition consists of multiple columns, you need to specify all the predicates in the ON clause. For example, if tables A and B are joined based on columns c1 and c2, the join condition would be: SELECT . . . FROM A JOIN B ON A.c1 = B.c1 AND A.c2 = B.c2;
If the column names are identical in the two tables, you can use the USING clause and specify all the columns in one USING clause, separated by commas. The previous join condition can be rewritten as: SELECT . . . FROM A JOIN B USING (c1, c2);
3.2.3 The Natural Join Clause A natural join between two tables relates the rows from the two tables based on all pairs of columns, one column from each table, with matching names. You don't specify a join condition. The following example illustrates a natural join: SELECT department.name, location.regional_group FROM department NATURAL JOIN location; NAME REGIONAL_GROUP -------------------- --------------- ACCOUNTING NEW YORK RESEARCH DALLAS OPERATIONS BOSTON
In this example, the two tables department and location have the same name for the column location_id. Therefore, the join takes place by equating the location_id from the department table to the location_id from the location table. The preceding query is equivalent to the following queries: SELECT department.name, location.regional_group FROM department JOIN location ON department.location_id = location.location_id; SELECT department.name, location.regional_group FROM department JOIN location USING (location_id);
While using a natural join, you are not allowed to qualify the common columns with table names or aliases (similar to the effect of the USING clause). For example, if you want to include the location_id column in the SELECT list, and you specify department.location_id, you will get an error: SELECT department.location_id, department.name, location.regional_group FROM department NATURAL JOIN location; SELECT department.location_id, department.name, location.regional_group * ERROR at line 1: ORA-25155: column used in NATURAL join cannot have qualifier
You need to remove the department qualifier so the location_id column can include it in the SELECT list: SELECT location_id, department.name, location.regional_group FROM department NATURAL JOIN location; LOCATION_ID NAME REGIONAL_GROUP ----------- -------------------- --------------- 122 ACCOUNTING NEW YORK 124 RESEARCH DALLAS 167 OPERATIONS BOSTON
Implicit specification of join conditions can have some unwanted side affects. Let's take the example of join between the supplier and part tables to illustrate this: DESC supplier Name Null? Type ----------------------------------------- -------- -------------- SUPPLIER_ID NOT NULL NUMBER(5) NAME NOT NULL VARCHAR2(30) DESC part Name Null? Type ----------------------------------------- -------- -------------- PART_NBR NOT NULL VARCHAR2(20) NAME NOT NULL VARCHAR2(30) SUPPLIER_ID NOT NULL NUMBER(5) STATUS NOT NULL VARCHAR2(20) INVENTORY_QTY NUMBER(6) UNIT_COST NUMBER(8,2) RESUPPLY_DATE DATE
An inner join between these two tables, generates the following result: SELECT supplier.supplier_id, part.part_nbr FROM supplier JOIN part ON supplier.supplier_id = part.supplier_id; SUPPLIER_ID PART_NBR ----------- ----------- 1 AI5-4557 2 TZ50828 3 EI-T5-001
The following example illustrates a natural join between these two tables: SELECT supplier_id, part.part_nbr FROM supplier NATURAL JOIN part; no rows selected
No output. What happened? The reason lies in the fact that, aside from supplier_id, these two tables have another pair of columns with a common name. That column is name. So, when you ask for a natural join between the supplier and the part tables, the join takes place not only by equating the supplier_id column of the two tables, but the name column from the two tables is equated as well. Since, no supplier name is the same as a part name from that same supplier, no rows are returned by the query. The equivalent inner join of the preceding natural join is: SELECT supplier.supplier_id, part.part_nbr FROM supplier JOIN part ON supplier.supplier_id = part.supplier_id AND supplier.name = part.name;
or, expressed via the USING clause: SELECT supplier_id, part.part_nbr FROM supplier JOIN part USING (supplier_id, name);
By looking at the inner join queries we've just presented, you can very well understand why the natural join between the supplier and part tables didn't return any rows. You must be aware of this potential for error when using natural joins. To avoid such problems, we recommend explicitly specifying join conditions, using either the ON or the USING clauses. | 