Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Joins and Union and Union ALL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vamseepotti

New User


Joined: 21 Oct 2003
Posts: 45

PostPosted: Sun Feb 15, 2004 11:18 pm    Post subject: Joins and Union and Union ALL
Reply with quote

Could any body explain about inner join and outer join?Union and UnionALL? with correct definations

Vamsee
Back to top
View user's profile Send private message

mdtendulkar

Active User


Joined: 29 Jul 2003
Posts: 238
Location: USA

PostPosted: Mon Feb 16, 2004 10:08 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Illegal use of keyword fetch. Token w... HABBIE DB2 2 Fri Aug 28, 2015 5:20 pm
No new posts Need help in Joins rajendra kalepu JCL & VSAM 9 Wed Oct 30, 2013 10:11 pm
No new posts Can we Update cursor with group by an... vyasricha DB2 5 Sat May 26, 2012 11:05 am
No new posts Sort Joins umanaga DFSORT/ICETOOL 7 Fri May 25, 2012 4:56 pm
No new posts Union or join query GuyC DB2 2 Tue May 10, 2011 1:25 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us