Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us