"select..where exists" in Oracle

I'm having difficulty getting my select query to work; I've simplified the problem to the following. If I query:

select room_size from rooms
where 30 > room_size;

Oracle returns 4 out of my 6 rooms' sizes correctly:

 ROOM_SIZE
----------
	 4
	 2
	 8
	16

Now if I revise this to a new query:

select room_size from rooms
where exists (select 1 from rooms
              where 30 > room_size);

I expect to get the same result, but Oracle returns:

 ROOM_SIZE
----------
	 4
	 2
	 8
	16
	32
	64

All 6 rooms in my database. What am I missing?

-- David Sirkin, October 3, 1999

Answers

The where clause of you top level query, ie:

where exists (select 1 from rooms
                       where 30 > room_size);

can be translated into english as: "If there are any rooms that hold more than 30 people.", thus the whole query becomes: "Give me each row in rooms for which it is the case that there are any rooms that hold more than 30 people"

I don't know what you are really trying to do, but I suggest that a where exists (select 1 ...) ought to have some reference to the outer query in order to mean anything.

Hope that this helps some. You might consider a less "simplified" question, as it is possible that your real question does not have this problem, that it crept in during the simplification.

-- Richard Tibbetts, October 3, 1999

Okay, I've moved (somewhat) past the previous problem, but am still formulating my query incorrectly. Here's the full query from "new.tcl:"

select room_id, room_name, room_size, av_facilities from rooms
    where $reserve_size <= room_size
    and not exists (select 1 from rooms, room_reservations
        where rooms.room_id = room_reservations.room_id
        and (   to_date('$start_time', 'YYYY-MM-DD HH24:MI:SS')
                between start_time and end_time
             or to_date('$end_time', 'YYYY-MM-DD HH24:MI:SS')
                between start_time and end_time
             or (to_date('$start_time', 'YYYY-MM-DD HH24:MI:SS') < start_time
                and to_date('$end_time', 'YYYY-MM-DD HH24:MI:SS') > end_time)
        )
    )
    order by room_size;

This works fine when there are no conflicts. But when say, one room does conflict, rather than retrieving all the rooms that don't conflict, the query retrieves no rooms at all. I suspect this is due to improper use of the "select 1 from...", but I don't know what would correct the mistake.

-- David Sirkin, October 3, 1999

Selecting available rooms

You are looking for all available rooms for a desired time slot, right? Try this:

 select room_id, room_name, room_size, av_facilities from rooms 
    where ( ... room constraints here ...)
          and rooms.room_id not in 
             (select rooms.room_id from rooms, room_reservations
                 where rooms.room_id = room_reservations.room_id
                     and ( ... time constraints here ...)
             )
    order by room_size;

-- Andy Quigley, January 12, 2000

Looks like I'm almost five years late on the answer, but this page is still in Google, so I thought I'd shed some light. I believe you are making two separate references to the rooms table in your second query. I believe your second query is translating into this:

select ROOMS1.room_size from rooms ROOMS1
where exists (select 1 from rooms ROOMS2
              where 30 > ROOMS2.room_size);

Your exists clause says, "Hey, for all those rooms, do we have any with a size less than 30?" The reply is, "Yes, we have four rows." Your select statment returns the set {1,1,1,1} for those four rows EVERY TIME, and the exists statement looks at that set and says, "Yep, there's stuff in the set." So... If you reference your main variable from the subquery you may get the results you're looking for... Maybe something like this:

select ROOMS1.room_size from rooms ROOMS1
where exists (select 1 from rooms ROOMS2
              where 30 > ROOMS1.room_size);

-- Chris H, December 2, 2004

friend, i think ur first query in that u have defined 30> room_size. according to me, it should be room_size>30. second thing.. i didn't tried the where exists (select 1) type.. but tried where not exists (select 1) type query.. if u want u can mail me for that.. ok... have a good day.. prem prakash

-- prem prakash, May 23, 2009

exists returns TRUE whenever the subquery gives a row count > 1.

In this case once the where clause gets the true condition from EXISTS, it will select all rows from the table.

Use IN clause instead of EXISTS

-- Sundeep Singh, May 25, 2009

Friends,

If you are using EXISTS (in your case), then there should be a link between Inner and Outer queries. Meaning, outer query will return ALL ROWS if inner query's resultset has atleast 1 row. In that case, EXISTS issues "WHERE 1=1" condition to the outer query, which causes returning all the rows.

Try this query, instead.

select room_size from rooms a where exists (select 1 from rooms b where 30>room_size and a.room_size=b.room_size);

Hope this helps.

-- Syed Ahamed Basheer, June 14, 2009

select room_size from rooms a where exists (select 1 from rooms where 30 > a.room_size);

try this u will get

-- veeranjaneyulu gogineni, November 27, 2009