This is actually an interesting question. I went running to my neglected
SQL for Smarties: Advanced SQL Programming (Joe Celko, 1995) and it tries to
explain why SQL-92 was enhanced with this new syntax.

First of all, in the SQL=89 standard, the only JOIN condition allowed is
equality. But each SQL vendor extended its syntax to allows outer joins.
Oracle has (+), Sybase has *= etc. For the vendors that used the = operator
to create the LEFT OUTER JOIN or the RIGHT OUTER JOIN (*=, =?), you would
get unpredictable results when combining OUTER JOINS with THETA JOINS (using
<, >, >=, <=, etc.). Oracle solved this by not putting the join condition
in the operator ... i.e., appending (+) to the column name in the join.

An example in the book is:

SELECT Suppliers.supno, supname, orderno, amt
FROM Suppliers, Orders
WHERE Suppliers.supno *= Orders.supno
AND Suppliers.quantity < 100;

SQL Server would not know to apply the "quantity < 100" before or after
applying the join condition. You would get different results. SQL-92
syntax takes away that ambiguity.

Oracle solved that. In Oracle, you could write the above as:

SELECT Suppliers.supno, supname, orderno, amt
FROM Suppliers, Orders
WHERE Suppliers.supno(+) = Orders.supno
*** AND Suppliers.quantity(+) < 100;

But this will give you all the ORDER rows even where the quantity was
greater than 100, and show those orders as NULLs.

SELECT Suppliers.supno, supname, orderno, amt
FROM Suppliers, Orders
WHERE Suppliers.supno(+) = Orders.supno
*** AND Suppliers.quantity < 100;

This applies the "< 100" before doing the join, and therefore takes
completely out of the picture the suppliers whose quantity did not meet the
criterium. Those whose quantity was greater than 100 are not represented as
null rows.

The new syntax not a big deal for Oracle, but it's nice to have a standard.
This takes away all ambiguity. And if your reports or select statements may
ever be ported to another vendor's SQL, it would more likely be compatible.


-----Original Message-----


select emp.ename, dept.dname
from emp
INNER JOIN dept
ON emp.deptno = dept.deptno

instead of ...

select emp.ename, dept.dname
from emp, dept
where emp.deptno = dept.deptno

Am just not used to the first one, but nonetheless I want to know maybe
there is some 'hidden' reasons for choosing to use the first one.


Oracle LazyDBA home page