differnce between join and union

priya_gmain · New User Joined: 18 Sep 2006 Posts: 7

Hi

What is the differnce between DB2 join and union ?

jayanttibhe · New User Joined: 24 Sep 2005 Posts: 1

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.

Cheers!!! [/b]

prashanth1 · New User Joined: 27 Sep 2006 Posts: 47 Location: Hyderabad

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.

guptae · Posted: Fri Oct 06, 2006 12:02 pm

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

vibhor_thebest · New User Joined: 18 Dec 2006 Posts: 3 Location: noida

hi ekta,
can u plz provide any linmk to manual reference for the same topic.

priyesh.agrawal · Posted: Tue Dec 19, 2006 10:11 am