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
 

 

differnce between join and union

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
priya_gmain

New User


Joined: 18 Sep 2006
Posts: 7

PostPosted: Mon Sep 18, 2006 2:46 pm    Post subject: differnce between join and union
Reply with quote

Hi

What is the differnce between DB2 join and union ?
Back to top
View user's profile Send private message

jayanttibhe

New User


Joined: 24 Sep 2005
Posts: 1

PostPosted: Tue Sep 19, 2006 4:20 pm    Post subject:
Reply with quote

Join is used to retrieve data from different tables using a single SQL statement.
Union is used to combine the results of two or more SQL queries.

icon_lol.gif Cheers!!! [/b]
Back to top
View user's profile Send private message
prashanth1

New User


Joined: 27 Sep 2006
Posts: 47
Location: Hyderabad

PostPosted: Fri Oct 06, 2006 11:38 am    Post subject:
Reply with quote

As of my knowledge both can be used to retreive the data from One or more than one table. But the difference between these two is their perforamance.

JOIN will take more time .


Pls correct me , if i wrong. icon_smile.gif
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Fri Oct 06, 2006 12:02 pm    Post subject:
Reply with quote

Hi prashanth,

There is big difference in JOIN & UNION.
JOIN
Sometimes the information you want to see is not in a single table. To form a row of the result table, you might want to retrieve some column values from one table and some column values from another table. You can retrieve and join column values from two or more tables into a single row. You can join tables using Operations Navigator, or using the JOIN statement.

Several different types of joins are supported by DB2 UDB for iSeries: inner join, left outer join, right outer join, left exception join, right exception join, and cross join.

An Inner Join returns only the rows from each table that have matching values in the join columns. Any rows that do not have a match between the tables will not appear in the result table.
A Left Outer Join returns values for all of the rows from the first table (the table on the left) and the values from the second table for the rows that match. Any rows that do not have a match in the second table will return the null value for all columns from the second table.
A Right Outer Join return values for all of the rows from the second table (the table on the right) and the values from the first table for the rows that match. Any rows that do not have a match in the first table will return the null value for all columns from the first table.
A Left Exception Join returns only the rows from the left table that do not have a match in the right table. Columns in the result table that come from the right table have the null value.
A Right Exception Join returns only the rows from the right table that do not have a match in the left table. Columns in the result table that come from the left table have the null value.


When you use UNION:

Any ORDER BY clause must appear after the last subselect that is part of the union. In this example, the results are sequenced on the basis of the first selected column, EMPNO. The ORDER BY clause specifies that the combined result table is to be in collated sequence.
A name may be specified on the ORDER BY clause if the result columns are named. A result column is named if the corresponding columns in each of the unioned select-statements have the same name. An AS clause can be used to assign a name to columns in the select list.
SELECT A + B AS X ...
UNION SELECT X ... ORDER BY X

If the result columns are unnamed, use numbers to order the result. The number refers to the position of the expression in the list of expressions you include in your subselects.

SELECT A + B ...
UNION SELECT X ... ORDER BY 1

I hope it is clear now
Back to top
View user's profile Send private message
vibhor_thebest
Warnings : 1

New User


Joined: 18 Dec 2006
Posts: 3
Location: noida

PostPosted: Tue Dec 19, 2006 6:50 am    Post subject:
Reply with quote

hi ekta,
can u plz provide any linmk to manual reference for the same topic.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Tue Dec 19, 2006 10:11 am    Post subject: Re: differnce between join and union
Reply with quote

Quote:
can u plz provide any link to manual reference for the same topic.
> JOIN
> UNION
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 -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am
No new posts Join Keys and DB2 Query jackare SYNCSORT 2 Sat Feb 13, 2016 5:57 am
No new posts Join Keys to compare two files senthamizh DFSORT/ICETOOL 2 Fri Feb 05, 2016 8:28 am


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