Home » SQL & PL/SQL » SQL & PL/SQL » union keyword
union keyword [message #228808] Wed, 04 April 2007 05:09 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,



I have one doubt in union keyword in oracle.I have run the fallowing query

1)select to_char(userid) from ram_employees where rownum<2

union

select to_char(employeenumber) from ram_employees where rownum<2



Out put is as -:

436
jzhang2



2)select to_char(employeenumber) from ram_employees where rownum<2

union

select to_char(userid) from ram_employees where rownum<2

output is as :

436

jzhang2

I have just interchange the select statement in second query.

Now my question is that why oracle displays the employeenumber field value first after that userid field value In both the conditions.

Useird is primary key in ram employee tables



I have run the fallowing queries.

select rd,userid,employeenumber from (select userid,employeenumber,rownum rd from ram_employees) where userid='jzhang2'

rd userid EMPLOYEENUMBER

1 jzhang2 183400

2) select rd,userid,employeenumber from (select userid,employeenumber,rownum rd from ram_employees) where employeenumber=436

rd userid EMPLOYEENUMBER

404 dbrown 436

Why oracle is taking the userid in first record and employeenumber has taking in 404 record number


Regards,
Yash
Re: union keyword [message #228812 is a reply to message #228808] Wed, 04 April 2007 05:24 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

please format your posts
like
1
select to_char(userid) from ram_employees where rownum<2

union 

select to_char(employeenumber) from ram_employees where rownum<2



and so on

because it is highly unreadable.

regards,
shanth

[Updated on: Wed, 04 April 2007 05:25]

Report message to a moderator

Re: union keyword [message #228820 is a reply to message #228808] Wed, 04 April 2007 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As of your first question: Oracle will return data in no specific order unless you specify it by using the ORDER BY clause. If you don't do that, order of records is more or less random.

As of your second question: I must admit that I don't understand what the problem is (as you didn't provide sample data). What do you mean by saying that "oracle is taking the userid in first record and employeenumber has taking in 404 record number"?
Re: union keyword [message #228836 is a reply to message #228820] Wed, 04 April 2007 06:39 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Littlefoot,

If oracle will not return data in no specific order then there is no point of discussion ...I was confused why I am always getting employeenumber value in first place even I interchange the column ...


I am new in oracle, and I know that if some one ask some thing foolish question then you will replay very harsh. so i am also afraid but

When I ran the “select userid,employeenumber,rownum rd from ram_employees”..then i find oracle is

Taking userid in first record (based on rd) and second employeenumber will take (405) as per my understanding it should take both the record on first place only bcz i used rownum<2

usrid employeenumber rd
jzhang2,183400,1
jingfzha,183828,2
bwu2,195554,3
guyin,183196,4
yibzhang,155932,5
junwa,183599,6
xuawang,190485,7
taowan,156285,8
zhaoliu,195641,9
jimeng,187681,10
yajwu,195636,11
rulin,195548,12
rahuang,23920,13
lzhao2,196534,14
qiuzheng,195476,15



--Yash
Re: union keyword [message #228847 is a reply to message #228836] Wed, 04 April 2007 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not the case.
As UNION guarantee uniqueness, the algorithm is more complex to remove duplicates and so you can get rows from both subqueries.
If you used UNION ALL you are likely to get both rows from first subquery but this is not guarantee.

Regards
Michel
Re: union keyword [message #228870 is a reply to message #228836] Wed, 04 April 2007 08:07 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
ammishra wrote on Wed, 04 April 2007 07:39

I am new in oracle, and I know that if some one ask some thing foolish question then you will replay very harsh. so i am also afraid but ...



Never be afraid to ask a question. You might be referring to a different thread where someone has received correct advice from about 4 or 5 different people and refused to accept it, which "may" have provoked a little bit of harsh words (and rightly so) Wink
Previous Topic: SQL Help Needed: Split & Replace - Update Column Value
Next Topic: moths and dates
Goto Forum:
  


Current Time: Thu Dec 08 01:56:03 CST 2016

Total time taken to generate the page: 0.22242 seconds