Oracle V/S ANSI Joins

If you talk about Database Management System now a day it is assumed that you are talking about Relational Database Management System because they are the norm. They have become the standard for information storage and retrieval. It is their power that has made these two business functions more easy and elegant like they were never before. For storing and retrieving data in Relational Database Management System the language used is Structured Query Language. It is shortly termed as SQL. SQL is a standard for successful information storage and retrieval in databases.

The standard of SQL is maintained by American National Standard Institute (ANSI). ANSI keeps track of the requirements and makes the necessary modifications and finally updates the standard. Despite of the standard each database vendor maintains its own version of SQL; so does the Oracle. However the basic functions remain the same. One of the areas in which Oracle SQL is different from ANSI SQL is the way it handles and manipulates the joins. The difference is mainly in the syntax rather in functionality. ANSI SQL puts all the conditions necessary for joins in the “From” clause of “Select” statement and “Where” clause is reserved for any optional conditions. On the other hand Oracle SQL uses “From” clause only to list the tables and aliases and all the conditions (Join Conditions + Optional Conditions) take place in the “Where” clause.

To clarify let us take the all familiar example of Employee-Department relationship. Suppose you want to list department name and all the corresponding employee’s information and for that purpose you intend to use INNER JOIN so that only matching records come in the result set. The syntax in ANSI SQL will be like this;

SQL>select a.dname,b.* from dept a inner join emp b on a.deptno=b.deptno;

The same result can be obtained in Oracle SQL by writing the statement as:

SQL> select a.dname,b.* from dept a,emp b where a.deptno=b.deptno;

Now if you want to see all the departments no matter if they have any employees or not then you will write following ANSI SQL statement;

SQL> select a.dname,b.* from dept a left outer join emp b on a.deptno=b.deptno;

To achieve the same result in Oracle SQL you will need the following statement;

SQL> select a.dname,b.* from dept a,emp b where a.deptno=b.deptno(+);

For RIGHT OUTER JOINS you will use the keyword RIGHT instead of LEFT in the above statement for ANSI SQL and place (+) on the other side for Oracle SQL. ANSI SQL also provides the FULL OUTER JOIN which returns the records from both tables that have not the matching records in the corresponding tables. Oracle does not support the FULL OUTER JOIN.

If you are new to Oracle then don’t panic that you need to learn the syntax of joins again. You can avoid the Oracle syntax joins fully while still working in Oracle. This is possible because Oracle has got full support for ANSI JOINS. You can write all ANSI style queries and they will work for you in Oracle. What if you are stuck to Oracle JOINS and you want to get the results of FULL OUTER JOIN. Here is the simple trick to do this. Just UNION the results of LEFT OUTER JOIN and RIGHT OUTER JOIN and that’s it. As UNION operator eliminates the duplications automatically so the result will be an exact replacement of ANSI SQL’s FULL OUTER JOIN.