Inner and outer joins

[caltog]

What are the different possibilities (if they are more than one) to perform an outer or an inner join between tables ?

(I know it's very easy question, but I've been trying so many things and I didn't get yet what i want...)

[vmcburney]

If you do a join within a transformer where one of the tables is a reference or lookup then by default you get an outer join to that table. You need to add a constraint to the transformer to turn it into an inner join, or you can add logic to your output rows to do certain field settings where there is no join to make it a smart outer join.

You can also join tables within a database input stage, this time the default is an inner join, you can make it an outer join using custom SQL or for Oracle you can put the outer join sign in the WHERE field.

Joining within the database stage usually provides faster processing, joining within the tranformer gives you a bit more control within the ETL environment to handle different join conditions.

Vincent McBurney
Data Integration Services
www.intramatix.com

[ariear]

Exactly,
But if you need a full outer join (a complte set) you'll have to check with each RDBMS or have a 2 lookups job in DataStage (Or another trick)

[ray.wurlod]

Server Jobs
If you've staged your table data into two sequential files, then the Merge stage can perform any kind of join.

Parallel Jobs
The Join stage can perform any kind of join involving two or more datasets.
The Merge stage combines a sorted master data set with one or more sorted update data sets.
The Lookup File Set stage can perform a lookup against a stored dataset.

Mainframe Jobs
The Join stage can perform inner or outer joins on data derived from two source stages.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[caltog]

Hi everybody,

thanks for your help and for your time