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

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

[Oracle recommends to use ANSI joins](http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries006.htm#SQLRF52354): "*Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator*" — , Nov 11 '13 at 11:28
If you are interested/planning to migrate the current db to Azure, the non-ANSI style is coming to the breaking changes (means not supported by the latest version of database). You have to convert all queries into the ANSI standards before migrating to Azure Cloud. You can see more details by search Data Migrate Assistant (DMA). — Tahir Alvi, Feb 02 '21 at 07:12

score 7 · Accepted Answer · edited Apr 17 '19 at 21:24

7

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.

edited Apr 17 '19 at 21:24

Darshan

352
8
24

answered Nov 11 '13 at 11:27

mucio

7,014
1
21
33

2

+1 I almost exclusievely use the *Ansi join syntax* because its more readable (imho). – René Nyffenegger Nov 11 '13 at 13:58
HI mucio, suppose if we have added another table "Salgrade" in the above query based on what conditions we need to join them? can u please explain with a example on how to join 3 or more tables.. your explanation is good.. thats y i'm asking u in particular – Sai Nov 12 '13 at 05:37
in that case after the `ON` condition you will add the next join with its condition. I prepared a small demo here to play: http://sqlfiddle.com/#!4/7a147/10 – mucio Nov 12 '13 at 10:43
The non-ANSI notations can only express limited conditions. – philipxy Apr 18 '19 at 00:37

score 1 · Answer 2 · answered Nov 11 '13 at 11:26

1

The first is a legacy Oracle specific way of writing joins, the second is ANSI SQL-92+ standard and is the preferred one.

answered Nov 11 '13 at 11:26

jpw

44,361
6
66
86

score 1 · Answer 3 · edited May 23 '17 at 11:46

Extensively discussed many a times, including one by me.

Use explicit JOINs rather than implicit (regardless whether they are outer joins or not) is that it's much easier to accidently create a cartesian product with the implicit joins.

With explicit JOINs you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.

Basically (+) is severely limited compared to ANSI joins. Furthermore it is only available in Oracle whereas the ANSI join syntax is supported by all major DBMS

SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.

Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example. In the past there were some bugs with ANSI syntax but if you go with latest 11.2 or 12.1 that should be fixed already.

Using the JOIN operators ensure your SQL code is ANSI compliant, and thus would allow a front-end application to be more easily ported for other database platforms.

Join conditions have a very low selectivity on each table and a high selectivity on the tuples in the theoretical cross product. Conditions in the where statement usually have a much higher selectivity.

Oracle internally converts ANSI syntax to the (+) syntax, you can see this happening in the execution plan's Predicate Information section.

If you are using 11.2 I advise ANSI join. If you use 12C, there are some new bugs unearthed on OUTER JOINS.

I also remember some bugs in Oracle while using ANSI syntax, before 11.2 where it got fixed in 11.2.

In my opinion, I am not a big fan of ANSI syntax, though Oracle does confirm to the standards of ANSI, it is not totally bug free.

score 0 · Answer 4 · edited May 23 '17 at 12:25

0

please, read this article about joins. result of your example is not same, if you have data in B table and not in A table

edited May 23 '17 at 12:25

Community

1
1

answered Nov 11 '13 at 11:33

MartinB

456
1
3
5

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

4 Answers4

Linked