[net.database] outer joins

roth@im4u.UUCP (08/22/85)

It has been rumored that ORACLE supports outer join and
"it was as easy as adding a plus sign '+'
to the relation that you knew might not have a qualifying tuple". 

First can anyone provide me with a example of how ORACLE employs this, and
second, are there any other languages or proposals for languages which
support outer join.  I am specifically interested in SQL-like languages.

Thanks for your help.

Mark Roth

tiberio@seismo.CSS.GOV (Mike Tiberio) (08/23/85)

I just dug out the ORACLE manual page my buddy sent to me 
when I asked him about outer joins. Example given:

> select emp.*, dname, loc
> from dept, emp
> where dept.deptno = emp.deptno(+)
> and empno is null

OR:

> select empno, ename, job, sal, emp.deptno, dname, loc
> from emp, dept
> where loc in ('Chicago', 'D.C.')
> and dept.deptno = emp.deptno(+)

What they have to say about it is:

> The outer join notation (+) is used to indicate that the table which may be
> missing entries shall be treated as though it had an additional row which will
> be joined to any row of the other table which would be otherwise discarded. In
> the example above, emp is the table which perhaps is missing one or two
> departments, so it is augmented with (+) so that all the dept rows will be
> selected. Some will be aligned with real rows of emp, others with a null row.

The above was taken (and reproduced) without permission from page 64 of some
ORACLE documentation section labled 'SQL statements'.

It is interesting to note that this method of outer join prints null values
(" ",0's) for the missing emp rows. Since INGRES has no such (+) operator
the method given in RTI's INGRES Applicatioon Notes, which requires the
addition of the null row to the lacking relation, essentially allows (forces)
the user to choose what 'null' values get returned. This could be useful
if in your database definition you have null values other than zero and
white space. In our databases here at the Center for Seismic Studies
we use underscore for character nulls and -1 for most numeric nulls. Of
cource ORACLE users could forgo the (+) operator and use the same method
RTI suggests.


seismo!tiberio

dsp@ptsfa.UUCP (David St. Pierre) (08/25/85)

I'll borrow the example from the ORACLE manual:

"List all departments that have employees, plus those departments
that do not have employees"

SELECT dept.deptno, dname, job, ename
	from	dept, emp
	where	dept.deptno = emp.deptno(+)
	order	by dept.deptno;


Also:

"List all departments that do not have any employees"

SELECT distinct dept.deptno, dname, koc
	from	dept, emp
	where	dept.deptno = emp.deptno(+)
	  and	empno is null;
-- 
David St. Pierre		{ihnp4,dual,qantel}!ptsfa!dsp