Home » SQL & PL/SQL » SQL & PL/SQL » combine the OR
combine the OR [message #204731] Tue, 21 November 2006 22:18 Go to next message
rkl1
Messages: 97
Registered: June 2005
Member
Dear All,

I got a situation like this:

select * from x100 where id >=(select max(id) from x101) or id < (select max(id) from x101)

I am just wondering, to get all the values, how to put it in one line: ofcourse, you may say: why not just go for select * from x100.

However, it is one of the requirement that I have to keep both the table in the query.

Any help, advice, comment would be gladly accepted and remembered with affection.
Re: combine the OR [message #204751 is a reply to message #204731] Tue, 21 November 2006 23:25 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

i don't understand what is your real need.

As per your query it returns all the rows naturally

In your query it is not necessary to write or conditions
You can modify as follows

select * from x100 where id <= (select max(id) from x101)

or

select * from x100 where id in (select distinct id from x101)
Re: combine the OR [message #204767 is a reply to message #204751] Wed, 22 November 2006 01:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
select * 
from x100 
cross join (select max(id) AS mx, min(id) AS mn from x101) mm
where id < aa.mn
or id > aa.mx


although it is not as efficient as your original query, which can use an index on x101.id to resolve the min/max.
The alternative above must perform a full table scan, because it uses both min and max in the same sub-query.

Stick with your query. It might not look elegant, but it will be most efficient (providing x101.id is indexed and the index is analyzed).

Ross Leishman
Re: combine the OR [message #204792 is a reply to message #204767] Wed, 22 November 2006 02:24 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
elegant?
How is "x >= y or x < y" elegant?
"x is not null" seems more elegant to me!

[Edit: Sorry Ross, misread your post! Thought you called the OPs query elegant]

But OP insists on using both tables in his query, without stating why that would be a requirement...

[Updated on: Wed, 22 November 2006 02:27]

Report message to a moderator

Previous Topic: composite primary key
Next Topic: How many 't' we have in "i want to get more"
Goto Forum:
  


Current Time: Tue Dec 06 04:13:06 CST 2016

Total time taken to generate the page: 0.10695 seconds