Doubt in UNION concept



IBM's flagship relational database management system

Doubt in UNION concept

Postby gauthamnagpur18 » Thu Oct 04, 2012 1:37 am

Hi,

I am having doubt in UNION concept . Suppose there are three sub-queries .

select * from a where some cond
union
select * from b where some cond
union
select * from c where some cond

If first query gives you 1 result set ,second & third query gives you none ..

What should be the output after UNION?

As per my understanding , output after UNION should be 1 result set which was retrieved from first query..

Please help me out :roll:
gauthamnagpur18
 
Posts: 93
Joined: Sat Oct 23, 2010 1:28 pm
Location: Chennai, India
Has thanked: 3 times
Been thanked: 3 times

Re: Doubt in UNION concept

 

Re: Doubt in UNION concept

Postby NicC » Thu Oct 04, 2012 2:11 am

have you tried something out to try and verify your thoughts?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2760
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 110 times

Re: Doubt in UNION concept

Postby gauthamnagpur18 » Thu Oct 04, 2012 9:11 am

Hi NicC ,

Ya , I tried .. I am not getting any output after union when only first query is having the result set & second & third query is having none . But when I tried changing the where condition of second query( to get result set of second query) , I got result set after UNION .

Conclusion : If we are doing A union B union C , atleast two of the sub queries should have result set for UNION result .

Thanks,
Gautham
gauthamnagpur18
 
Posts: 93
Joined: Sat Oct 23, 2010 1:28 pm
Location: Chennai, India
Has thanked: 3 times
Been thanked: 3 times

Re: Doubt in UNION concept

Postby enrico-sorichetti » Thu Oct 04, 2012 10:51 am

Conclusion : If we are doing A union B union C , atleast two of the sub queries should have result set for UNION result .

unfortunately Your conclusion is wrong!

The UNION operator is used to combine the result-set of two or more SELECT statements.

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

The UNION operator selects only distinct values
that' s why it appears that only rows from the first <select> are returned

To allow for duplicate values, and return them use UNION ALL.
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2


The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2708
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 139 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post