A new way of joining Oracle tables? Why would I want to change?
By John Adolph Palinski
Years ago, I first became interested in Oracle because of the ease in performing outer joins. I was an IBM DB2 developer. DB2 at that time required two unioned select statements to perform the outer join. Matched records were retrieved by the first select statement. The second select statement retrieved records that exist in the first table without a matching record in the second table. The Union operator then combined the result sets. I first jumped on the Oracle bandwagon because it gave me the (+) operator allowing me to avoid having to write the extra select statements.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
I have been very happy with Oracle’s method of joining since the day I did my first outer join. Occasionally I ran across a Microsoft Access Select statement. I shook my head and went back to my Oracle world wondering why anyone would want to use anything else. At some point I began teaching SQL. One of the first things I noticed was the number of times a student would accidentally execute a Cartesian join. I taught my students to always count the number of join conditions before executing the query. I’ve always wondered why Oracle couldn’t prompt the developer when a Cartesian join occurs. This would have saved me, my students, and thousands of other developers tons of time by eliminating the wait for a meaningless result set.
<o:p> </o:p>
Oracle9i was upgraded to support the ANSI SQL/92 standard. This feature allows Oracle to use syntax closer to the Microsoft Select statements I cringed at. When I first saw this syntax I wondered why Oracle changed. After all, Oracle has worked well for me. Why would I want to learn a new syntax and why did they add it?
<o:p> </o:p>
Since I teach SQL I had to look over the syntax. To my amazement there really were some benefits. One of the most important is that the new syntax can prevent the inadvertent execution of the Cartesian join discussed earlier. This can be a real time saver. This increased my interest, so I identified some additional benefits. These are:
<o:p> </o:p>
· In a complex Where clause, it can be difficult to differentiate the join condition from other conditions. The inability increases the difficulty in understanding the statement’s filtering logic. The ANSI standard join conditions are also evident since they are in the From clause not the Where clause.
<o:p> </o:p>
· The type of join is immediately evident since the join type is designated in the From clause. This eliminates the need to review and analyze conditions to determine the join type.
<o:p> </o:p>
· Oracle does not allow you to omit a join condition.
<o:p> </o:p>
· The new syntax allows you to perform a full outer join. A full outer join displays records from either table that do not have a matching record in the other table. Traditionally a full outer join requires two unioned Select statements.
<o:p> </o:p>
The new syntax
<o:p> </o:p>
Oracle9i’s ANSI SQL/92 join syntax has the following properties:
<o:p> </o:p>
· The join expressions are contained entirely in the From clause.
· Joins are described using one of two clauses. The clauses are:
<o:p> </o:p>
Join … Using
Join … On
<o:p> </o:p>
· Five types of joins are available. These are:
<o:p> </o:p>
Natural
Inner
Right outer
Left outer
Full outer
<o:p> </o:p>
The following sections illustrate these options. The Select statements use the Oracle supplied DEPT and EMP tables. These tables reside in the default Scott/Tiger ID Oracle creates with each install. There are two relations on these tables:
<o:p> </o:p>
· A department has one or more employees
· An employee has a manager that is also an employee (unary relationship)
<o:p> </o:p>
Natural join
<o:p> </o:p>
Natural joins occur when the primary/foreign key columns in the joined tables have the same name. The DEPT primary key column is DEPTNO. The corresponding EMP foreign key column is DEPTNO. Thus, the tables are joinable using a natural join. Natural joins have the following features:
<o:p> </o:p>
· They use the Join…Using clause
· You do not have to list the actual primary/foreign key columns since they have the same name.
· You do not have to qualify the columns with the tables.
· You do not have to add an operator. A natural join is an equi-join.
<o:p> </o:p>
Listing 1 illustrates this join. Notice that a Where clause is not needed and all of the conditions exist in the From Clause. Compare the statement to the following traditional statement. The ANSI SQL/92 standard is much more succinct and easy to interpret.
select dname, e1.ename employee
from dept, emp e1
where dept.deptno = emp.deptno
order by 1, 2;<o:p></o:p>
<o:p> </o:p>
Listing 1 – Performing a natural join based upon the DEPTNO column
<o:p> </o:p>
SQL> select dname, e1.ename employee<o:p></o:p>
2 from dept<o:p></o:p>
3 join emp e1 using (deptno)<o:p></o:p>
4 order by 1, 2;<o:p></o:p>
<o:p> </o:p>
DNAME EMPLOYEE <o:p></o:p>
-------------- ---------- <o:p></o:p>
ACCOUNTING <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place>CLARK</st1:place> <o:p></o:p>
ACCOUNTING KING <o:p></o:p>
ACCOUNTING MILLER <o:p></o:p>
RESEARCH <st1:place>ADAMS</st1:place> <o:p></o:p>
RESEARCH FORD <o:p></o:p>
RESEARCH JONES <o:p></o:p>
RESEARCH SCOTT <o:p></o:p>
RESEARCH SMITH <o:p></o:p>
SALES ALLEN <o:p></o:p>
SALES BLAKE <o:p></o:p>
SALES JAMES <o:p></o:p>
SALES MARTIN <o:p></o:p>
SALES TURNER <o:p></o:p>
SALES WARD <o:p></o:p>
<o:p> </o:p>
14 rows selected.<o:p></o:p>
<o:p> </o:p>
SQL>
<o:p> </o:p>
End Listing
<o:p> </o:p>
The Join…on clause
<o:p> </o:p>
The Join..on clause is used when the joined tables do not have matching column names. When this clause is used, you must add the join condition. Listing 2 illustrates this join. The Select statement now has a second join. The EMP table has a unary relationship to itself. Each employee has a manager, which is another employee. Joining the MGR and EMPNO columns produces a list of managers and their employees. This is exactly what the query in Listing 2 produces. Notice that the first join is still the natural join. The second join uses the Join..On clause since the joined columns do not have the same names. It was also necessary to qualify the columns. MGR and EMPNO exist in both tables. The qualification defines exactly how to join the records. <o:p></o:p>
<o:p> </o:p>
Listing 2 – Using the Join On syntax to add the department manager
<o:p> </o:p>
SQL> select dname, e1.ename employee, e2.ename manager<o:p></o:p>
2 from dept<o:p></o:p>
3 join emp e1 using (deptno)<o:p></o:p>
4 join emp e2 on (e1.mgr = e2.empno)<o:p></o:p>
5 order by 1, 2;<o:p></o:p>
<o:p> </o:p>
DNAME EMPLOYEE MANAGER <o:p></o:p>
-------------- ---------- ---------- <o:p></o:p>
ACCOUNTING <st1:place>CLARK</st1:place> KING <o:p></o:p>
ACCOUNTING MILLER <st1:place>CLARK</st1:place> <o:p></o:p>
RESEARCH <st1:place>ADAMS</st1:place> SCOTT <o:p></o:p>
RESEARCH FORD JONES <o:p></o:p>
RESEARCH JONES KING <o:p></o:p>
RESEARCH SCOTT JONES <o:p></o:p>
RESEARCH SMITH FORD <o:p></o:p>
SALES ALLEN BLAKE <o:p></o:p>
SALES BLAKE KING <o:p></o:p>
SALES JAMES BLAKE <o:p></o:p>
SALES MARTIN BLAKE <o:p></o:p>
SALES TURNER BLAKE <o:p></o:p>
SALES WARD BLAKE <o:p></o:p>
<o:p> </o:p>
13 rows selected.<o:p></o:p>
<o:p> </o:p>
SQL>
<o:p> </o:p>
End Listing
<o:p> </o:p>
The second join condition in Listing 2 is an inner join. An inner join occurs when a selected record from the first table must match a record in the second table. It is permissible but not necessary to use inner join keywords rather than join. This is more descriptive but returns the same results.
<o:p> </o:p>
Left and right outer joins
<o:p> </o:p>
One EMP record did not display in Listing 2. This was employee KING. KING does not have a manager and the join condition could not find a matching record. This requires an outer join. Outer joins cause records that do not have a matching counter part to display. Traditionally Oracle allowed the developer to use the (+) operator to designate the table that may not have the matching record. The traditional operator is placed on either the left condition or the right condition. The following is a traditional Select statement that displays employees, managers, and employees that do not have a manager:
<o:p> </o:p>
select dname, e1.ename employee
from dept, emp e1, emp e2
where dept.deptno = emp.deptno
and e1.mgr = e2.empno(+)
order by 1, 2;<o:p></o:p>
<o:p> </o:p>
The ANSI SQL/92 standard uses the keywords left outer and right outer to designate a one-sided outer join. Listing 3 illustrates a left outer join. The non-matched record that always displays is the one from table E1 using MGR. This is the left argument. The potentially missing record is from table E2 using EMPNO. This record supplies the manager’s name. This is different from the traditional approach. The (+) operator is located on the argument that does not have to exist. The left outer and right outer keywords on the other hand designate the argument that does have to exist.
<o:p> </o:p>
Listing 3 – Left Outer Join
<o:p> </o:p>
SQL> select dname, e1.ename employee, e2.ename manager<o:p></o:p>
2 from dept<o:p></o:p>
3 join emp e1 using (deptno)<o:p></o:p>
4 left outer join emp e2 on (e1.mgr = e2.empno)<o:p></o:p>
5 order by 1, 2;<o:p></o:p>
<o:p> </o:p>
DNAME EMPLOYEE MANAGER <o:p></o:p>
-------------- ---------- ---------- <o:p></o:p>
ACCOUNTING <st1:place>CLARK</st1:place> KING <o:p></o:p>
ACCOUNTING KING <o:p></o:p>
ACCOUNTING MILLER <st1:place>CLARK</st1:place> <o:p></o:p>
RESEARCH <st1:place>ADAMS</st1:place> SCOTT <o:p></o:p>
RESEARCH FORD JONES <o:p></o:p>
RESEARCH JONES KING <o:p></o:p>
RESEARCH SCOTT JONES <o:p></o:p>
RESEARCH SMITH FORD <o:p></o:p>
SALES ALLEN BLAKE <o:p></o:p>
SALES BLAKE KING <o:p></o:p>
SALES JAMES BLAKE <o:p></o:p>
<o:p> </o:p>
DNAME EMPLOYEE MANAGER <o:p></o:p>
-------------- ---------- ---------- <o:p></o:p>
SALES MARTIN BLAKE <o:p></o:p>
SALES TURNER BLAKE <o:p></o:p>
SALES WARD BLAKE <o:p></o:p>
<o:p> </o:p>
14 rows selected.<o:p></o:p>
<o:p> </o:p>
SQL>
The full outer join
<o:p> </o:p>
The full outer join is new to Oracle. It displays records from the left joined table and the right joined table that do not match. Before this Oracle enhancement developers had to create a second Select statement to create the proper result set. The first Select statement had the outer join operator on one side and the second on the other. A Union operator discards duplicate records and combines the two result sets.
<o:p> </o:p>
Listing 4 illustrates the full outer join. It is a modified version of the Listing 3 Select statement. Full outer replaces the keywords left outer. The result is the addition of employees that do not have employees. These employees display at the bottom of the list. Table E2 supplied the values. It supplied employee names for employees whose EMPNO value did not exist in an MGR column.
<o:p> </o:p>
Listing 4 – Full outer join is used to show employees that do not have managers and employees that do not have reporting employees<o:p></o:p>
<o:p> </o:p>
SQL> select dname, e1.ename employee, e2.ename manager<o:p></o:p>
2 from dept<o:p></o:p>
3 join emp e1 using (deptno)<o:p></o:p>
4 full outer join emp e2 on (e1.mgr = e2.empno)<o:p></o:p>
5 order by 1, 2;<o:p></o:p>
<o:p> </o:p>
DNAME EMPLOYEE MANAGER <o:p></o:p>
-------------- ---------- ---------- <o:p></o:p>
ACCOUNTING <st1:place>CLARK</st1:place> KING <o:p></o:p>
ACCOUNTING KING <o:p></o:p>
ACCOUNTING MILLER <st1:place>CLARK</st1:place> <o:p></o:p>
RESEARCH <st1:place>ADAMS</st1:place> SCOTT <o:p></o:p>
RESEARCH FORD JONES <o:p></o:p>
RESEARCH JONES KING <o:p></o:p>
RESEARCH SCOTT JONES <o:p></o:p>
RESEARCH SMITH FORD <o:p></o:p>
SALES ALLEN BLAKE <o:p></o:p>
SALES BLAKE KING <o:p></o:p>
SALES JAMES BLAKE <o:p></o:p>
SALES MARTIN BLAKE <o:p></o:p>
SALES TURNER BLAKE <o:p></o:p>
SALES WARD BLAKE <o:p></o:p>
TURNER <o:p></o:p>
WARD <o:p></o:p>
MARTIN <o:p></o:p>
ALLEN <o:p></o:p>
MILLER <o:p></o:p>
SMITH <o:p></o:p>
<st1:place>ADAMS</st1:place> <o:p></o:p>
JAMES <o:p></o:p>
<o:p> </o:p>
22 rows selected.<o:p></o:p>
<o:p> </o:p>
SQL>
<o:p> </o:p>
I hope you can see the benefits of the ANSI SQL/92. The syntax makes it much easier to decipher the Select statement logic. Filtering arguments are only contained in the Where clause. Join conditions no longer clutter this clause. If nothing the use of this syntax will help you avoid those accidental Cartesian products.
<o:p> </o:p>
About the Author
<o:p> </o:p>
John Adolph Palinski is an Oracle Certified Professional developer. He has been adjunct faculty at various institutions. He is the author of: “Oracle9i Developer: Developing Web Applications with Forms Builder”, Course Technology, 2003; “Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analyst”, Addison Wesley, 2003; and the “Oracle Database Construction Kit”, Que, 1997. He has also written numerous Oracle articles. He provides various pre-developed and custom Oracle training courses through his company Realistic Software Training. He is always on the search for interesting topics to write upon. He can be contacted at www.oracle-trainer.com<o:p></o:p>
