Query regarding DB2 Joins

sowmya · New User Joined: 03 May 2005 Posts: 4

if we join two tables, ti and t2
in ti there are 3 columns and in t2 there are 4 colums what is the number of colums in the resultant table

ankyhunk · Posted: Mon May 09, 2005 12:41 pm

It depends on the condition & the type of the join.

sangeetha · New User Joined: 06 May 2005 Posts: 4

can u explain in more detail abt what the condition & type mean

ankyhunk · Posted: Mon May 09, 2005 1:43 pm

The columns specified in the select clause & the values on which the join has to take place. Also there are many types of joins. For details refer to DB2 manuals on this site.

ovreddy · Posted: Mon May 09, 2005 8:59 pm

Hi Sowmya,

Joins are used to extract data from more than one table. We must use a Join condition while joining two tables. The Join condition depends on your business rules that apply on Tables. I will take a simple situation and explain you in detail.

Consider two tables EMP and DEPT with the following structure
EMP ( EMPNO,ENAME,JOB,SAL,DEPTNO) -- Employee details
DEPT(DEPTNO,DNAME,LOC) -- Department details

I need a query which extracts EMPNO,ENAME,JOB,SAL,DNAME and LOC means complete information of an EMPLOYEE. Some columns are from EMP and some are from DEPT. So we need to Join these 2 tables using a Join Condition. The Condition is DEPTNO of EMP is equal to DEPTNO of DEPT. The complete query is as follows..

SELECT EMPNO,ENAME,JOB,SAL,DNAME,LOC FROM EMP,DEPT where emp.DEPTNO=DEPT.DEPTNO;

while accessing DEPTNO we need to specify fully qualified name(Table.Column). Because it is common in both the tables.

emp.DEPTNO=DEPT.DEPTNO - Join Condition

But Its better to go through a material on Joins for full pledged knowledge.

Thanks,
Reddy.

i413678 · Posted: Tue May 10, 2005 12:23 pm

Hi,

what ovreddy explained is inner join which gives only the matching rows from both the tables EMP and DEPT.

there are other joins which returns matching and non-matching rows from both the tables depends on join you select.

those are left outer join, right outer join and full outer join.

Cheers...........

pavankumar