Home » SQL & PL/SQL » SQL & PL/SQL » Union All improvement
Union All improvement [message #302459] Mon, 25 February 2008 17:38 Go to next message
evergrean
Messages: 15
Registered: February 2008
Junior Member
In my Oracle 9i Schema I have this setup with two tables:
TableOne
pocOne    pocTwo
2         3
2         4
1         2

TableTwo
TableTwoId   Name
1            Jones
2            Smith
3            Edwards
4            Camden


I currently have this query where I need to find all the records that match the criteria (Smith) and was wondering how I can eliminate the Union All and put it in one SQL:

select * from TableTwo 
Inner Join TableOne 
on TableTwo.TableTwoId = TableOne.pocOne 
where Name = 'Smith' 
UNION ALL
select * from TableTwo 
Inner Join TableOne 
on TableTwo.TableTwoId = TableOne.pocTwo 
where Name = 'Smith'

[Updated on: Mon, 25 February 2008 17:43]

Report message to a moderator

Re: Union All improvement [message #302473 is a reply to message #302459] Mon, 25 February 2008 19:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
select * from TableTwo 
Inner Join TableOne 
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo) 
where Name = 'Smith' 


Be warned though - it may run slower

Ross Leishman
Re: Union All improvement [message #302474 is a reply to message #302459] Mon, 25 February 2008 19:31 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
how I can eliminate the Union All and put it in one SQL

The SQL given already is one query. Do you have any reason to change this?

The easiest way would be changing the TableOne design to (TableOneId, TableTwoId), where TableOneId would contain only two values (eg. 1, 2). It can be ensured by check constraint, or by a reference to another (new) table, currently containing two rows. Then it would be easy to query it using TableTwoId.
This design is recommended when there may be need to add column pocThree in future. However I do not know, if this is your case.

Other possibility is to join any two-row table (eg. the one mentioned in previous paragraph or made by any Row Generator Technique) with appropriate join condition.
SELECT * FROM TableTwo 
CROSS JOIN TableZero
INNER JOIN TableOne 
  ON CASE WHEN TableZero.Id = 1 THEN TableOne.pocOne
          ELSE TableOne.pocTwo
     END = TableTwo.TableTwoId
WHERE Name = 'Smith';
Re: Union All improvement [message #302724 is a reply to message #302459] Tue, 26 February 2008 18:06 Go to previous message
evergrean
Messages: 15
Registered: February 2008
Junior Member
Thank you.
Previous Topic: Full Outer Joins (and subqueries)
Next Topic: Adding ^M to each line in output file
Goto Forum:
  


Current Time: Sat Dec 03 05:57:42 CST 2016

Total time taken to generate the page: 0.28691 seconds