Since Oracle started supporting ANSI joins in 9i, the Oracle community was divided into two groups: one loved it and tried to convince everyone to use it, and the other hated it and has never switched. As you probably understand from the title, I’m in the second group, and this is why.
When using a simple natural join, both ANSI and Oracle proprietary are quite simple. When working with outer join the Oracle proprietary one is a bit tricky (where do I put the + ??) while the ANSI is much more intuitive. And when trying to do a full outer join, well, Oracle proprietary simply doesn’t have the syntax for that.
It’s seems that the ANSI joins syntax is quite cool, so why don’t I like it? The simple answer is that it confuses me. The fact that tables appear all over the SQL as well as predicates simply makes it difficult for me to understand the query. Let me explain using an example.
In this example we have 3 tables: parent, child1 and child2:
create table parent (id number, name varchar2(10)); create table child1 (id number, p_id number, name varchar2(10)); create table child2 (id number, p_id number, name varchar2(10)); insert into parent values(1,'p1'); insert into parent values(2,'p2'); insert into parent values(3,'p3'); insert into parent values(4,'p4'); insert into child1 values(1,1,'c1'); insert into child1 values(2,1,'c2'); insert into child1 values(3,2,'c3'); insert into child2 values(1,1,'k1'); insert into child2 values(2,2,'k2'); insert into child2 values(3,3,'k3'); insert into child2 values(4,4,'k4'); commit;
A simple Oracle proprietary join between the tables will look like this:
select * from parent p, child1 c1, child2 c2 where p.id=c1.p_id and p.id=c2.p_id;
I like the fact that the tables are grouped together. Even with a much ore complex query (with SET operators, inline views, WITH clause, etc.) you can count on the fact that the tables will appear after a FROM keyword and one after the other (in the worst case of inline views, you simply skip the parentheses and you’ll find the next table).
In my opinion, the ANSI syntax is more messy:
select * from parent p join child1 c1 on p.id=c1.p_id join child2 c2 on p.id=c2.p_id;
There are bunch of stuff in between the table names. Also, this join syntax forces the order of the tables as it seems that the parsing is ordered. In this case, I can’t have the PARENT last since the first CHILD table needs it. I don’t consider this as a problem, but still something to know:
SQL> select *
2 from
3 child1 c1
4 join
5 child2 c2 on p.id=c2.p_id
6 join
7 parent p on p.id=c1.p_id
child2 c2 on p.id=c2.p_id
*
ERROR at line 5:
ORA-00904: "P"."ID": invalid identifier
Another interesting case is outer join. Let’s take all the parents and their children (if exist) from CHILD1 (I’m querying the CHILD first because of the next example, but it doesn’t really matter here):
SQL> select *
2 from
3 child1 c1,
4 parent p
5 where p.id=c1.p_id(+)
ID P_ID NAME ID NAME
---------- ---------- ---------- ---------- ----------
1 1 c1 1 p1
2 1 c2 1 p1
3 2 c3 2 p2
4 p4
3 p3
SQL> select *
2 from
3 child1 c1
4 right outer join parent p on p.id=c1.p_id
ID P_ID NAME ID NAME
---------- ---------- ---------- ---------- ----------
1 1 c1 1 p1
2 1 c2 1 p1
3 2 c3 2 p2
4 p4
3 p3
Now let’s look at this final example, I would like to see the same outer join results, but only for the parent with the name p3:
SQL> select *
2 from
3 child1 c1,
4 parent p
5 where p.id=c1.p_id(+)
6 and p.name='p3'
ID P_ID NAME ID NAME
---------- ---------- ---------- ---------- ----------
3 p3
SQL> select *
2 from
3 child1 c1
4 right outer join parent p on p.id=c1.p_id
5 where p.name='p3'
ID P_ID NAME ID NAME
---------- ---------- ---------- ---------- ----------
3 p3
So far so good, but see what happens if I put the predicate in the wrong place:
SQL> select *
2 from
3 child1 c1
4 right outer join parent p on p.id=c1.p_id
5 and p.name='p3'
ID P_ID NAME ID NAME
---------- ---------- ---------- ---------- ----------
2 p2
1 p1
4 p4
3 p3
This is messed up!
So, why don’t I like the ANSI syntax? First, I think it’s confusing. It’s much easier (in my opinion) to have all the tables in one place and all the predicates (join predicates included) in one place. Second, the fact that you can write predicate in a few places might lead to errors and wrong query (while here it’s simple to see, with long queries it’s much harder to find these problems).
So there you go, I said it, I don’t like the ANSI join syntax (even though full outer join option is cool!)
I am with you. It is so hard to understand – may be I am old schooled.
Hi.
I tend to do new stuff using ANSI syntax. There is a lot of old stuff on my site, especially scripts, that don’t use it.
One thing I like is predicates placed in the JOIN condition are handled correctly with respect to the out join. As long as you remember that regular predicate go in the WHERE and those related to an outer join go in the JOIN life is good. 🙂
I think formatting of the statement goes a long way to make things look more “normal”, but I am aware there are few old geezers like me who use ANSI syntax. 🙂
I think the biggest push for me was when I started to use multiple engines for development. It’s easier to commit to one method, rather than trying to switch between the two. 🙂
Cheers
Tim…
It will get better, I recently discovered you can mix the two syntaxes in 1 query:
select e.ename, e.job, d.dname, j.job_desc
from emp e
inner join dept d on d.deptno = e.deptno , jobdesc j
where e.job = j.job;
Imagine 7 ansi-inner-joins, some on multiple fields, plus some 10 tables added, just comma-glued to the end of the last ansi-join.
On first inspection, the additional tables, e.g. “jobdesc j”, had also scrolled out of view to the right…. go figure.
Tim is Correct: Formatting is a good first help.
But I’m with Amitzil: ansi-join is often just Confusing.
And it hinders me in spotting the total SQL scope: list-of-tables, list-of-join-fields And list-of-relevant-Filters.
Old-skool from-clauses give you .a better overview of what is in the SQL.
(but I wont go all fanatic about it…)
OK, first, Tim is always right 🙂
About formatting, yes, it’s important, and yes it helps. However, in many (many) cases I’ve seen queries being generated by the application, and believe me, formatting is not something they care about.
I do agree with Tim about multiple engines, this is a real reason to switch (even though the old fashion joins would still work, only the Oracle outer join won’t).
For now, I’m still staying with the non-ANSI.
I partially agree with you reasoning, though personally I do tend to like ANSI join syntax.
However, there is another reason to not like ANSI when used with Oracle; ANSI joins are first transformed to Oracle syntax as part of the optimization, and sometimes some odd things take place in the transformation.
Following is one example from Jonathan Lewis:
https://jonathanlewis.wordpress.com/2017/01/10/join-elimination-12-2/
Take a look at the Footnote
I’ll second that!
ANSI syntax is a great way to separate joins from filters, and not only that – you easily see how each table is being joined.
After I encounter non trivial Oracle syntax SQL which joins tables in some complicated way, the first thing I do I try do find out:
– which of conditions are joins
– if it is a join then between which tables
– which conditions are just filters.
By doing that i always rewrite query using ANSI syntax because next time after half year when I investigate the same query I do not want to go through the same analysis again.
Completely agree, finding all tables in one FROM place is handy. Another argument against ANSI is that outer join is TOO easy to write, I’ve seen thousends of SQL just copy-paste with Right join, nobody could answer WHY or if it is neccesary… Old-good (+) makeS developer THINK 🙂
Hi Liron,
In the end it’s a matter of personal preference, but to comment on one point you made:
You find it easier to understand if all tables are at the same place. In my opinion it’s more readable if the table you join and the join predicates are at the same place. They belong together so I think it’s more readable if they are grouped together. E.g. if I see that an additional table is joined, I wan’t to know what the join conditions are. Grouping thes together, avoids searching for the predicates in the where clause. This is especially useful if the query is large and complex. Secondly, this also clearly distinguishes between join predicates and other predicates (think of outer joins).
And I can also just back up what Tim said. Formatting goes goes a long way. Whether you use Oracle Joins or ANSI Joins, you can let them look very messy if you want 🙂
Thomas
I’m an ANSI convert, I’m afraid (especially now that most of the ANSI to Oracle conversion optimiser bugs have been patched).
Aside from the better outer join syntax, I very much like that the join conditions are separated from the filter conditions. This makes it easier to check that tables are correctly joined (i.e. FKs to PKs) and, by grouping the filter conditions together, it allows the viewer (including the creator) to get to the crux of the query.
Oh… this ” ancient war” ))
But nevertheless, each of the syntaxes is often used precisely as of subjective factors.
I believe that ASNI is still more convenient for reading, structured queries.
But, proprietary syntax also has its advantages.
And we can not say that to use one or another kind of syntax-the only right way)
I am completely with Amitzil, still find the Oracle-style join much more readable, maybe because it was the first one learned and used for so many years 🙂
It is the same thing as with any other human language, for example, Chinese is the easiest language for a native Chinese, because this is what he learned first 🙂
Regarding the full join, once Oracle does have the ability to perform it, then maybe they could have also allowed it to be coded using the old syntax, by putting the (+) on both sides, which is currently not allowed.
ANSI joins are hard to read and just do nothing other than confuse if you have any long type of query. Old school joins are easy and clean to read. Forget about ANSI joins.