JOINs | Oracle PL[s]SQL by Example

The 1999 ANSI standard introduced complete JOIN syntax in the FROM clause. The prior method was to list the tables needed in the query in the FROM clause and then to define the joins between these tables in the WHERE clause. However, the conditions of the SQL statement are also listed in the WHERE clause. It was decided to enhance this syntax because listing of the joins and the conditions in the same WHERE clause can be confusing.

The 1999 ANSI join syntax includes cross joins, equijoins , full outer joins, and natural joins.

CROSS JOINs

The CROSS JOIN syntax indicates that you are creating a Cartesian product from two tables. The result set of a Cartesian product is usually meaningless, but it can be used to generate a lot of rows if you need to do some testing. The advantage of the new syntax is that it flags a Cartesian product by having the CROSS JOIN in the FROM clause.

FOR EXAMPLE

Prior to Oracle 9 i , you would create a Cartesian product with the following syntax:

 SELECT *   FROM instructor course

The new syntax is as follows :

 SELECT *   FROM instructor CROSS JOIN        course

The result set from this is 300. This is because the COURSE table has 30 rows and the INSTRUCTOR table has 10 rows. The CROSS JOIN will count all possible combinations resulting in the 300 rows.

EQUI JOINs

The EQUI JOIN syntax indicates the columns that comprise the JOINS between two tables. Prior to Oracle 9 i , you would indicate a join condition in the WHERE clause by stating which two columns are part of the foreign key constraint.

FOR EXAMPLE

Prior to Oracle 9 i , you would join the STUDENT table to the ZIPCODE table as follows:

 SELECT s.first_name, s.last_name, z.zip, z.city, z.state   FROM student s, zipcode z  WHERE s.zip = z.zip

The new syntax is as follows:

 SELECT s.first_name, s.last_name, zip, z.city, z.state   FROM student s JOIN        zipcode z USING (zip)

The reason for this syntax is that the join condition between the two tables is immediately obvious when looking at the tables listed in the FROM clause. This example is very short, but generally your SQL statements are very long, and it can be time consuming to find the join conditions in the WHERE clause.

Notice that the ZIP column did not have an alias. In the new JOIN syntax, the column that is referenced in the JOIN does not have a qualifier. In the old syntax, if you did not use an alias for column ZIP, as in this example,

 SELECT s.first_name, s.last_name, zip, z.city, z.state   FROM student s, zipcode z  WHERE s.zip = z.zip

Oracle would generate the following error:

  ORA-00918: column ambiguously defined

In the new JOIN syntax, if you use a qualifier, as in this example,

 SELECT s.first_name, s.last_name, z.zip, z.city, z.state   FROM student s JOIN        zipcode z USING (zip)

Oracle generates the following error:

  ORA-25154: column part of USING clause cannot have qualifier

The new JOIN syntax also allows you to define the join condition using both sides of the join. This is done with the ON syntax. When using the ON syntax for a JOIN you must use the qualifier. This is also useful when the two sides of the join do not have the same name .

The ON syntax can also be used for three-way joins (or more).

FOR EXAMPLE

 SELECT s.section_no, c.course_no, c.description,        i.first_name, i.last_name   FROM course c   JOIN section s     ON (s.course_no = c.course_no)   JOIN instructor i     ON (i.instructor_id = s.instructor_id)

The syntax for a multiple-table join becomes more complex. Notice that one table is mentioned at a time. The first JOIN lists columns from the first two tables in the ON section. Once the third table has been indicated, the second JOIN lists columns from the second and third tables in the ON clause.

NATURAL JOINs

The NATURAL JOIN is another part of the ANSI 1999 syntax that can be used when joining two tables based on columns that have the same name and datatype. The NATURAL JOIN can only be used when all the columns that have the same name in both tables comprise the join condition between these tables. You cannot use this syntax when the two columns have the same name but a different datatype. Another benefit of this join is that if you use the SELECT * syntax, the columns that appear in both tables will only appear once in the result set.

FOR EXAMPLE

 SELECT *   FROM instructor NATURAL JOIN zipcode

The join that will be used here is not only on the ZIP column of both tables, but the CREATE_BY, CREATED_DATE, MODIFIED_BY, and MODIFIED_DATE columns as well.

The student schema does not support the NATURAL JOIN condition since we have created audit columns that have the same name in each table but are not used in the foreign keys constraints among the tables.

OUTER JOINs

INNER JOIN or EQUI JOIN is the result of joining two tables that contain rows where a match occurred on the join condition. It is possible to lose information through an INNER JOIN because only those rows that match on the join condition will appear in the final result set.

The result set of an OUTER JOIN will contain the same rows as the INNER JOIN plus rows corresponding to the rows from the source tables where there was no match. The OUTER JOIN has been supported by a number of versions of the Oracle SQL language. It had not been a part of the ANSI standard until the 1999 version.

Oracle's OUTER JOIN syntax has consisted of placing a (+) next to the columns of a table where you expect to find values that do not exist in the other table.

FOR EXAMPLE

 SELECT i.first_name, i.last_name, z.state   FROM instructor i, zipcode z  WHERE i.zip (+) = z.zip GROUP BY i.first_name, i.last_name, z.state

In this example, the result set will include all states that are in the ZIPCODE table. If there is no instructor for a state that exists in the ZIPCODE table, the values of FIRST_NAME and LAST_NAME will be blank (NULL). This syntax gets more confusing because it must be maintained if there are more conditions in a WHERE clause. This method can only be used on one side of the outer join at a time.

The new method of OUTER JOINS adopted in Oracle 9 i allows the case of an OUTER JOIN on either side or both sides at the same time (for example, if there were some instructors who had zipcodes that were not in the ZIPCODE table, and you wanted to see all the instructors and all the states in both of these tables). This task can be accomplished by using the new OUTER JOIN syntax only. This requires the aforementioned JOIN syntax with addition of new outer join attributes as well. The choice is LEFT/RIGHT/FULL OUTER JOIN. The same OUTER JOIN can now be modified as

 SELECT i.first_name, z.state   FROM instructor i RIGHT OUTER JOIN        zipcode z     ON i.zip = z.zip GROUP BY i.first_name, z.state

The RIGHT indicates that the values on the right side of the JOIN may not exist in the table on the LEFT side of the join. This can be replaced by the word FULL if there are some instructors who have zipcodes that are not in the ZIPCODE table.