CODEWITHSUNDEEP
sql
benscabbia
benscabbia

Reputation: 18072

Are these SQL subqueries equivalent?

I have this schema: 

Hotel(hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest(guestNo, GuestName, GuestAddress)

I need to list all guests currently staying at the Grosvenor Hotel. This is what I came up with: 

SELECT * FROM Guest
WHERE guestNo IN
    (SELECT guestNo FROM Booking
     WHERE dateFrom <= CURRENT_DATE AND
     dateTo >= CURRENT_DATE AND
     hotelNo =
         (SELECT hotelNo FROM Hotel
         WHERE hotelName = ‘Grosvenor Hotel’));

I then checked the answer sheet which provided an almost identical solution except rather than IN it used =. I.e. 

SELECT * FROM Guest
WHERE guestNo =
    (SELECT gues....

Surely the = would cause a single value to be returned and since subquery would return a set of guestNo, the IN, should be correct? Is the answer provided incorrect or am I wrong?

Upvotes: 0

Views: 73

Answers (2)

Barranka
Barranka

Reputation: 21047

The equality comparison = can be used when you are comparing one value to another one. However, if you want to check if a value is contained in a list of values or in a subquery, you must use IN.

One more thing: This can be more easily solved with proper relations, instead of nested queries:

select g.*
from Guest as g
    inner join booking as b on g.guestNo = b.guestNo
    inner join Hotel as h on b.hotelNo = h.hotelNo
where h.hotelName = 'Grosvenor Hotel'
  and dateFrom <= CURRENT_DATE
  and dateTo >= CURRENT_DATE;

Upvotes: 2

rba
rba

Reputation: 358

WHERE var1 IN ('value1', 'value2')

is equivalent to

WHERE var1 = 'value1' OR var1 = 'value2'

Upvotes: 1

Related Questions