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