What is difference between ANSI and non-ANSI joins, and which do you recommend? [duplicate]

Question

I have come across many websites to find the answer about which one is better, ANSI or non- ANSI syntax. What is the difference between these two queries?

select a.name,a.empno,b.loc
from tab a, tab b
where a.deptno=b.deptno(+);

and:

select a.name,a.empno,b.loc
from tab a 
left outer join tab b on a.deptno=b.deptno;

The result is same in both the cases. The second query is also longer. Which one is better?

suppose if we have added another table Salgrade in the above query based on what conditions we need to join them?? .. can any one assume one table and give me explanation

Answer 1

both syntaxes usually work without problems, but if you try to add a where condition you will see that with the second one is much simpler to understand which is the join condition and which is the where clause.

1)

  SELECT a.name,
         a.empno,
         b.loc 
    FROM tab a,
         tab b 
   WHERE a.deptno = b.deptno(+)
     AND a.empno = 190;

2)

         SELECT a.name,
                a.empno,
                b.loc 
           FROM tab a,
LEFT OUTER JOIN tab b 
             ON a.deptno = b.deptno
          WHERE a.empno = 190;

Also, it's much easier to recognize an outer join and do not forget to include the (+). Overall you can say it's just a question of taste, but the truth is that the second syntax is much more readable and less prone to errors.