UNION & MINUS - strange work together at different situation
Mike
I have two tables: A and B, they both have the same fields. Table A
contains some records. Table B has the same records and some
additional records.
If I use such code:
select * from A
minus
select * from B
union
select * from B
minus
select * from A
I get correct result - some string (difference bitween B and A)
But if I use such code
select * from B
minus
select * from A
union
select * from A
minus
select * from B
I get no records as a result. What can be a problem here? Why if last
part return null string UNION does not work ?
Thanks
Mike
Sybrand Bakker
No doubt this is an issue of MINUS having a higher arithmetic priority
than UNION or vice versa. Try using parentheses as should do as often
as possible to avoid ambiguous statements like the 2 above.
Hth
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Mike Liu
This is because all set operators have equal precedence, they are
evaluated from the left to right if there are no parentheses. In your
case, B is a superset of A, so
'select * from A
minus
select * from B
union
select * from B
minus
select * from A'
is the same as
'select * from B minus select * from A',
which gives you what you expected. However
'select * from B
minus
select * from A
union
select * from A
minus
select * from B'
is actually the same as
'select * from B minus select * from B'
which gives nothing. You need to use parentheses.
Regards,
Mike
Mike Liu
This is because all set operators have equal precedence, they are
evaluated from the left to right if there are no parentheses. In your
case, B is a superset of A, so
'select * from A
minus
select * from B
union
select * from B
minus
select * from A'
is the same as
'select * from B minus select * from A',
which gives you what you expected. However
'select * from B
minus
select * from A
union
select * from A
minus
select * from B'
is actually the same as
'select * from B minus select * from B'
which gives nothing. You need to use parentheses.
Regards,
Mike
========= WAS CANCELLED BY =======:
From: mike...@hotmail.com (Mike Liu)
Control: cancel <2262aa8e.02012...@posting.google.com>
Subject: cmsg cancel <2262aa8e.02012...@posting.google.com>
Date: Mon, 28 Jan 2002 01:15:32 GMT
Message-ID: <cancel.2262aa8e.0...@posting.google.com>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1
Path: news.uni-stuttgart.de!dns.phoenix-ag.de!newsfeed01.sul.t-online.de!newsfeed00.sul.t-online.de!t-online.de!newsfeed.gamma.ru!Gamma.RU!newsfeed.simtel.ru!Simtel.RU!out.nntp.be!propagator-SanJose!in.nntp.be!news-in-sanjose!sjc-feed.news.verio.net!sea-feed.news.verio.net!news.verio.net!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40723021
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers.
Sybrand Bakker
No doubt this is an issue of MINUS having a higher arithmetic priority
than UNION or vice versa. Try using parentheses as should do as often
as possible to avoid ambiguous statements like the 2 above.
Hth
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
========= WAS CANCELLED BY =======:
From: Sybrand Bakker <pos...@sybrandb.demon.nl>
Control: cancel <mqs15u0ra27qi6a94...@4ax.com>
Subject: cmsg cancel <mqs15u0ra27qi6a94...@4ax.com>
Date: Mon, 28 Jan 2002 00:18:56 GMT
Message-ID: <cancel.mqs15u0ra27qi...@4ax.com>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1
Path: news.uni-stuttgart.de!cert.uni-stuttgart.de!news.belwue.de!news.uni-ulm.de!rz.uni-karlsruhe.de!blackbush.xlink.net!blackbush.de.kpnqwest.net!netnews.web.de!newsfeed01.sul.t-online.de!t-online.de!fr.clara.net!heighliner.fr.clara.net!news.tele.dk!small.news.tele.dk!171.64.14.106!newsfeed.stanford.edu!canoe.uoregon.edu!logbridge.uoregon.edu!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40724487