Join Stage is a Processing stage IBM Info Sphere Data Stage. This Join Stage is Used to Join Two Input Table data and Produce to new Out Put data Table is Called Join Stage. This Join Stage is Three Types. There are Inner Join, Outer Join & Full Outer Join. IBM Info Sphere Data Stage Join Stage Using main purpose is Combination of Two Equal or Not Equal Tables to generate New Out Put Table data. More Information about Join Stage Data stage information is Given Below. Join Stage Examples, Join Stage Flow Data, Join Stage Real time Scenario's foe Please Click on Below Link.
Join Stage real Time Examples & Scenario's for Click Here
What is Join Stage in IBM Info Sphere Data Stage ?
JOIN Queries:
Join is a query which combines the data from multiple tables
Types of joins:
1. Cartezion join
2. Equi join
3. Non equi join
4.Self join or inner join
5.Outer join
Left outer join
Right outer join
Employee Table Data:
SQL> select * from emp;
EMPNO ENAME JOB MGR DEPTNO
---------- ---------- ---------- ---------- ----------
111 bhaskar analyst 444 10
222 prabhakar clerk 333 20
333 pradeep manager 111 10
444 srujana engineer 222 40
Department Table Data
SQL> select * from dept;
DEPTNO DNAME LOC
------ ---------- ----------
10 marketing hyderabad
20 finance banglore
30 hr bombay
Examples:
Cartezion join:
If we combine a data from multiple tables with out applying any condition then each record in the first table will join with all records in the second table
SQL>select * from emp,dept
SQL>select empno,ename,job ,dname,loc from emp e,dept d
If we combine a data from multiple tables by applying equal no of conditions on multiple tables then each record in the first table will join with one row in the second table.
This kind of join can be called as Equi join
SQL>select e.empno,e.ename,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno
Inner join:
This will display all the records that have matched.
Ex:
SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);
Left outer join:
This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.
Ex:
SQL> select empno,ename,job,dname,loc from emp e left outer join dept d
on(e.deptno=d.deptno);
Or
SQL> select empno,ename,job,dname,loc from emp e,dept d where
e.deptno=d.deptno(+);
Right outer join:
This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.
Ex:
SQL> select empno,ename,job,dname,loc from emp e right outer join dept d
on(e.deptno=d.deptno);
Or
SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) =
d.deptno;
Full outer join
This will display the all matching records and the non-matching records from both tables.
Ex:
SQL> select empno,ename,job,dname,loc from emp e full outer join dept d
on(e.deptno=d.deptno);
Join Stage:
These topics describe Join stages, which are used to join data from two input tables and produce one output table. You can use the Join stage to perform inner joins, outer joins, or full joins.
1.An inner join returns only those rows that have matching column values in both input tables. The unmatched rows are discarded.
2.An outer join returns all rows from the outer table even if there are no matches. You define which of the two tables is the outer table.
3.A full join returns all rows that match the join condition, plus the unmatched rows from both input tables.
Unmatched rows returned in outer joins or full joins have NULL values in the columns of the other link
1.Join stages have two input links and one output link.
2. The two input links must come from source stages. The joined data can be output to another processing stage or a passive stage
Join stage Properties:
1.Join Keys
2.Options
1.Join Keys
- Key=?
Type: Input Column
Name of input column you want to join on. Columns with the same name must appear in both input data sets and have compatible data types.
Case sensitive=True/False
Type: List
Whether this join column is case sensitive or not.
2.Options
- Join type= F,I,L,R
F->Full outer join
I->Inner join
L->Left outer join
R->Right outer join
Type: List
Type of join operation to perform.
No comments:
Post a Comment