Your message has been sent.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Beginning with database version 10g, Oracle has provided a new syntax for retrieving data from multiple tables. While Oracle's syntax has not yet gained widespread acceptance, we will see that it offers some advantages, not least of which is its simpler syntax. Many consider Oracle's join syntax to be more intuitive and readable than the ANSI standard.
In this article by Steve Ries, author of OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide (1ZO-051) we shall join tables using the new Oracle join syntax.
(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.
eBook Price: $29.99
Book Price: $49.99
(For more resources on Oracle, see here.)
Joining on explicit columns with JOIN USING
One of the criticisms of Oracle's NATURAL JOIN syntax stems from its ambiguity: a NATURAL JOIN allows columns to be joined without any specification as to what column will be used. While this allows for a more "natural" syntax, the ambiguity leads to the production of SQL code that lacks the specificity required in some coding standards. In short, if the column being joined is not explicitly stated, the code can be more difficult to interpret, often leading to a greater number of human errors. To combat this problem, the Oracle join syntax also includes the ability to perform a table join using a column that is explicitly defined. An example of this is shown as follows:
Although it differs somewhat from the NATURAL JOIN syntax, JOIN USING is similarly straightforward. The FROM clause specifies the tables to be joined, in our example employee and message, separated by a JOIN clause. It is then followed by the USING clause that specifies, in parentheses, the common column to use for the join. Thus, in this statement, we join the employee and message tables using the employee_id column.
Consider this example of a real-world requirement from our Companylink database: Display all the URLs for websites and blogs in the Companylink system, along with the website description; however, do not display Gary Moore's site (gmoore), and sort the results by their website URL. To begin, we examine the website and blog tables and find that they share a common column—blog_id. This ties the blog table to the website table, since any of the blogs on Companylink must first have a website associated with them. Next, we can exclude Gary Moore's site by adding a WHERE clause to restrict output. Finally, we use an ORDER BY to sort the output.
Our resulting statement is shown in the following example:
We are using the not like operator to reject any rows that contain the string 'gmoore', or Gary Moore. Again, using the Oracle join syntax allows us to use the WHERE clause for row restriction, instead of setting common columns equal. Although both syntaxes will work, Oracle's syntax could be considered cleaner since it separates the functionalities of the join and the row restriction into different clauses.
Constructing fully-specified joins using JOIN ON
Our final type of join, using the Oracle syntax, removes all of the ambiguity that characterizes the two previous types. Statements that utilize the JOIN ON clause are very close syntactically to ANSI joins. The main difference, again, is that the JOIN ON clause, like the previous joins that use the Oracle syntax, do not require a WHERE clause in order to perform the join. An example of using JOIN ON is shown in the following screenshot:
Notice the similarities to an ANSI-compliant join. In the FROM clause, both tables to be joined are specified. However, in the Oracle syntax we're using, we utilize the JOIN clause instead of simply separating the tables with a comma (,). Likewise, we specify the column to use for the join, branch_id, and denote this with table notation. However, instead of using a WHERE clause to do this, we can use the ON clause in conjunction with the JOIN clause, and surround the common columns with optional parentheses. The result is a join that still permits the WHERE clause to be used exclusively for row restriction.
Although the previous example uses table notation to specify our columns, the JOIN ON syntax fully supports the use of alias notation as well. An example of this is shown as follows. Here, we simply rewrite the previous example to use aliases.
If we wished to do so for clarity, we could prefix each of the columns in the SELECT statement with its appropriate alias to further clarify our code. Again, the JOIN ON syntax removes the ability to refer to columns ambiguously. If we modify our SELECT to include an unqualified column, branch_id, as shown in the next example, we receive the ambiguous column error that we've seen previously.
Writing n-1 join conditions using Oracle syntax
As with the ANSI syntax, multi-table joins using n-1 join conditions can be done with the Oracle syntax. We conclude the article by looking at two examples.
Creating multi-table natural joins
The syntax for using a natural join with multiple tables is similar to that of the twotable natural join we've seen previously. Again, it makes use of the NATURAL JOIN clause to ambiguously join tables based on common columns with the same name. An example is shown in the following screenshot:
In order to construct this join, we simply use the NATURAL JOIN syntax twice—once to join the employee table with employee_award, then a second time to join employee_award and award. Oracle locates the common columns between the tables and correctly performs the join. Again, this frees up the WHERE clause for additional restrictions on the rows displayed. Also note that the order of the NATURAL JOIN clauses is not important. Even if we attempt to natural join employee to award and then award to employee_award, the result is the same. Oracle is smart enough to parse the statement correctly.
Building multi-table joins with JOIN USING
For less ambiguity, we can also utilize the JOIN USING clause to specify the columns being used for the join. The syntax is similar to a two-table JOIN ON. We simply add additional clauses, as shown in the next example. Here, we join the three tables employee, website, and blog using two JOIN USING clauses.
SQL in the real world
As it stands in today's development world, your organization's coding standards may preclude the use of Oracle's relatively new join syntax. That does not mean it should be ignored. A good case can be made for the simplicity and readability of the Oracle syntax. Regardless, Oracle certification candidates must be extremely comfortable with the Oracle join syntax. It is covered extensively on the examination.
With the Oracle syntax, we've looked at various join techniques using the NATURAL JOIN, JOIN USING, and JOIN ON clauses.
- Getting Started with Oracle GoldenGate [Article]
- An Overview of Oracle Advanced Pricing [Article]
- Oracle GoldenGate 11g: Performance Tuning [Article]
- Oracle Database 11g: Using ANSI Standard Joins [Article]
- Oracle Web Services Manager: Authentication and Authorization [Article]
eBook Price: $29.99
Book Price: $49.99
About the Author :
Steve Ries has been an Oracle DBA for 16 years, specializing in all aspects of database administration, including security, performance tuning and backup and recovery. He is a specialist in Oracle Real Application Clusters (RAC) and has administered Oracle clustered environments in every version of Oracle since the creation of Oracle Parallel Server. Steve is the author of OCA Oracle Database 11g: SQL Fundamentals I: A Real-World Certification Guide, Packt Publishing. He holds five Oracle certifications as well as a Security+ certification. He currently consults for the Dept. of Defense, US Marine Corps, and holds a high-level security clearance. Additionally, Steve has been an adjunct instructor of Oracle technologies at Johnson County Community College for 9 years, where he teaches classes that prepare students for the Oracle certification exams. He was also a speaker at the 2011 and 2012 Oracle Open World conferences. Steve is a two-time, award-winning technical paper writer and the creator of the alt.oracle blog.