mdtendulkar
Active User
Joined: 29 Jul 2003
Posts: 237
Location: USA
|
|
|
|
Hello vamseepotti,
Inner join:
To request an inner join, execute a SELECT statement in which you specify the tables that you want to join in the FROM clause, and specify a WHERE clause or an ON clause to indicate the join condition.
Full Outer join:
The clause FULL OUTER JOIN includes unmatched rows from both tables. Missing values in a row of the result table contain nulls.
Left outer join:
The clause LEFT OUTER JOIN includes rows from the table that is specified before LEFT OUTER JOIN that have no matching values in the table that is specified after LEFT OUTER JOIN.
Right Outer join:
The clause RIGHT OUTER JOIN includes rows from the table that is specified after RIGHT OUTER JOIN that have no matching values in the table that is specified before RIGHT OUTER JOIN.
Example:
| Code: |
A join-condition specifies pairings of T1 and T2, where T1 and T2 are the left and right operand tables of its associated JOIN operator. For all possible combinations of rows T1 and T2, a row of T1 is paired with a row of T2 if the join-condition is true. When a row of T1 is joined with a row of T2, a row in the result consists of the values of that row of T1 concatenated with the values of that row of T2. The execution might involve the generation of a ?null row?. The null row of a table consists of a null value for each column of the table, regardless of whether the columns allow null values.
|
The following summarizes the results of the join operations:
1) The result of T1 INNER JOIN T2 consists of their paired rows.
2) The result of T1 LEFT OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2. All columns derived from T2 allow null values.
3) The result of T1 RIGHT OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T2, the concatenation of that row with the null row of T1. All columns derived from T1 allow null values.
4) The result of T1 FULL OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2, and for each unpaired row of T2, the concatenation of that row with the null row in T1. All columns of the result table allow null values.
A join operation is part of a FROM clause; therefore, for the purpose of predicting which rows will be returned from a SELECT statement containing a join operation, assume that the join operation is performed before the other clauses in the statement.
Union:
Using the UNION keyword, you can combine two or more SELECT statements to form a single result table. When DB2 encounters the UNION keyword, it processes each SELECT statement to form an interim result table, and then combines the interim result table of each statement. If you use UNION to combine two columns with the same name, the result table inherits that name.
You can use UNION to eliminate duplicates when merging lists of values obtained from several tables.
Union All:
If you want to keep duplicates in the result of a UNION, specify the optional keyword ALL after the UNION keyword.
Hope this helps,
Regards
Mayuresh Tendulkar |
|
