RSS Feed

Join Using Multiple Tables (more than 2)

Questions: I am looking for the resources/examples on using Oracle9i ANSI joins on multiple tables.

Most of the examples i found are using just two tables to explain the join. I'd appreciate if you could give the examples of writing complex multitable joins for Oracle9i.

I want to join tables A,B,C,D,E in such a way that tables C,D & E will have OUTER join with table A on a key column. I have used this type of join in Informix and now trying to convert it into Oracle9i.


and we said...

The same syntax you used in Informix, given that it was "ansi" style is supported in Oracle9i. There are not "advanced resources" on this cause you are making it harder then it is. It really is as straight forward as it looks.


Just use parents and keep nesting the joins:



ops$tkyte@ORA9I.WORLD> create table a ( x int );

Table created.

ops$tkyte@ORA9I.WORLD> create table b ( x int );

Table created.

ops$tkyte@ORA9I.WORLD> create table c ( x int );

Table created.

ops$tkyte@ORA9I.WORLD> create table d ( x int );

Table created.

ops$tkyte@ORA9I.WORLD> create table e ( x int );

Table created.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> insert into a values ( 1 );

1 row created.

ops$tkyte@ORA9I.WORLD> insert into b values ( 1 );

1 row created.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> select *
2 from (((( a inner join b on a.x = b.x ) left outer join c on a.x = c.x )
3 left outer join d on a.x = d.x ) left outer join e on a.x = e.x )