AskTom: join example, new vs old syntax

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6585774577187

You Asked

Hi Tom,
I know in oracle9i we have the cross join and full outer join. Do they exist for 8i if so 
can you point me to the documentation. If they don't can you give an example/breakdown of 
how to rewrite in 8i.

Thanks in advance,
 

and we said…

to join in 8i, just use a where clause:

select * 
  from emp, dept 
 where emp.deptno = dept.deptno;

in 9i and up, you could

select ename, dname, emp.deptno, dept.deptno
  from SCOTT.EMP inner join SCOTT.DEPT
    on emp.deptno = dept.deptno


to outer join 8i, use a (+)

select * from emp, dept where emp.deptno = dept.deptno(+)

is the same as the 9i syntax of:

select ename, dname, emp.deptno, dept.deptno
  from SCOTT.EMP LEFT outer join SCOTT.DEPT
    on emp.deptno = dept.deptno

whereas 

select * from emp, dept where emp.deptno(+) = dept.deptno

is the same as:

select ename, dname, emp.deptno, dept.deptno
  from SCOTT.EMP RIGHT outer join SCOTT.DEPT
    on emp.deptno = dept.deptno


In 8i, a full outer join would be:

select * from emp, dept where emp.deptno = dept.deptno(+)
UNION ALL
select * from emp, dept where emp.deptno(+) = dept.deptno AND emp.deptno is null;

versus the 9i syntax (which does the same amount of work -- no magic there)

select *
  from SCOTT.EMP FULL outer join SCOTT.DEPT
    on emp.deptno = dept.deptno

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s