ANSI Join Vs Oracle Traditional Join

Sometimes you need to use data from more than one table. A join is used to view information from multiple tables. Hence, you can join tables together to view information from more than one table.

Prior to the Oracle9i release, the join syntax was different from the ANSI standards. ANSI join syntax does not offer any performance benefits over the Oracle-traditional join syntax that existed in prior releases.

The main strengths of ANSI join syntax, which I found while working, are

Clarity and Easiness: The use of the JOIN clause separates the relationship logic from the filter logic (the WHERE) and is thus cleaner and easier to understand.

For example, instead of using

SELECT d.dname, e.empno
FROM dept d, emp e
WHERE d.deptno = e.deptno;

You can use this

SELECT d.dname, e.empno
FROM dept d JOIN emp e
USING (deptno);

Or this

SELECT d.dname, e.empno
FROM dept d JOIN emp e
ON (d.deptno = e.deptno);

And instead of using traditional left outer join

SELECT e.ename, d.deptno, d.dname

FROM   emp e , dept d

WHERE  e.deptno=d.deptno(+);

You can use this

SELECT e.ename, d.deptno, d.dname

FROM   emp e FULL OUTER JOIN dept d

ON   (e.deptno = d.deptno) ;

Support for Full Outer Join: Oracle traditional join does not support full outer join. But you can do full outer join using ANSI ‘FULL OUTER JOIN’ clause.

SELECT e.last_name, d.department_id, d.department_name

FROM   employees e FULL OUTER JOIN departments d

ON   (e.department_id = d.department_id) ;

Best Practice: It's good practice as most developers and dba's will use ANSI-join nowadays and you should follow the standard. Certainly all modern query tools will generate ANSI-join.

ANSI join has the following weaknesses compared to Oracle traditional join:

·        People are far more familiar and comfortable with the traditional Oracle approach.

·        ANIS join is not supported in Oracle 8i or prior versions.