(For more resources on Oracle, see here.)

Using Cartesian joins with Cross join

The Cartesian product resulting from a Cartesian, or Cross join, is not always desirable. However, we can utilize it if necessary, using the Oracle join syntax as well. To do so, we use the CROSS JOIN clause, as shown in the following screenshot:

In this example, we select one column from each of the branch and blog tables— branch_name and blog_url, respectively. As we have noted with Cartesian joins, these tables have no relationship with each other; that is to say, they have no common columns.

The Oracle CROSS JOIN syntax is very similar to that of the ANSI-compliant join. The only real difference is the inclusion of the CROSS JOIN clause in place of a comma.

In the ANSI syntax, we make no distinction at all as to any relationship existing between the two tables. Thus, the ANSI syntax simply looks like a coding mistake, as if the coder simply forgot to add a WHERE clause. The Oracle syntax makes a purposeful inclusion of the CROSS JOIN clause to force the issue. With this clause included, we are explicitly stating that we are, in fact, attempting a Cartesian join between the two tables. Notice also that just as in the ANSI syntax, the resulting number of rows from the cross join is a times b rows, where a and b are the number of rows in the branch and blog tables, respectively. The branch table contains 13 rows and the blog table contains five rows, resulting in a 65 row result set.

Joining columns ambiguously using NATURAL JOIN

Since, as we’ve noted, cross joins rarely produce useful output, let’s proceed by looking at one of the more useful clauses in the Oracle join syntax, the NATURAL JOIN clause. Let’s say that we want to display employee name, starting date, and e-mail address information for a company e-mailer. To retrieve this information, we need to draw from two different tables. We can do this with a natural join, as shown in the following screenshot:

The output from this statement gives us the desired information. We can also see that unlike in a cross join, the rows from each table are properly joined together. No extraneous rows are produced. The striking fact about this statement is that it contains no WHERE clause. In ANSI-compliant joins, we used a WHERE clause to set common column values equal. This would instruct Oracle as to how to complete the join. In the previous example, no common columns are specified. How, then, does Oracle know how to complete the join? Let’s add to the complexity of the situation by adding the employee_id column to our statement, as shown in the following screenshot:

If we examine the two tables, we can see that the common column between the employee and email tables is the employee_id column. We can see this, but how does Oracle know it? It knows because the NATURAL JOIN clause allows for ambiguity in column names. A natural join with Oracle’s syntax is smart enough to be able to locate the common column between two tables, provided that one exists. When the statement is executed, Oracle recognizes the request for a natural join, examines the two tables and sees that there is a column, employee_id, with the same name in both tables. It then makes the assumption that the employee_id column is the target for your join and joins the tables appropriately. In a sense, we could say that a NATURAL JOIN is less strict, syntactically, than a similar join done with ANSI syntax.

Also notice that the first column we retrieve, employee_id, has no table definition. We have not explicitly noted whether we wish to display the employee_id column from the employee table or the email table. Were we to attempt a statement like this with an ANSI join, we would receive a column ambiguously defined error. But, again, since the NATURAL JOIN clause allows for column ambiguity, the statement retrieves the column as requested. In truth, with this syntax, Oracle makes the assumption that it actually does not matter which table the column comes from, since when the tables are joined, the values produced for the common column are actually the same. In that example, the values match up side by side. This is the essence of how a join works—by equivalently joining the values from common columns. Oracle uses this concept to allow for column ambiguity in natural joins.

What would happen if we attempted to use the NATURAL JOIN clause in a statement with two tables that did not have a common column? We see the results of such an attempt in the following example:

From the results, we see that a Cartesian product is formed. Oracle searches for a common column and, finding none, proceeds to join the two tables the only way it can—using a Cartesian, or Cross join. While it is true that Oracle’s natural join syntax is less strict, this can lead to unforeseen problems unless proper care is taken to ensure that the natural join is constructed in such a way as to utilize a common column.

One of the benefits of using the Oracle join syntax is that it frees up the use of a WHERE clause. Since the syntax does not require the WHERE clause to establish equivalence between common columns, as in the case of the ANSI syntax, our statements can use the WHERE clause to its more common use—restricting row output. An example of this, that also includes a sort, is shown in the following screenshot. It retrieves the name of each division and its associated branch, but limits the output to only rows that have a division_id less than 5. The output is then sorted alphabetically based on division_name.