select ht.hotel_id, ha.stay_date, ht.depart_time from hotels ht, Hotelavailability ha where ht.hotel_id = ha.hotel_id and ht.room_number = ha.room_number and ht.bed_type = 'KING' and ht.smoking_room = 'NO' order by ha.stay_date
If Derby chooses Hotels as the outer table, it can use the index on Hotels to retrieve qualifying rows. Then it need only look up data in HotelAvailability three times, once for each qualifying row. And to retrieve the appropriate rows from HotelAvailability, it can use an index for HotelAvailability's hotel_id column instead of scanning the entire table.
If Derby chooses the other order, with HotelAvailability as the outer table, it will have to probe the Hotels table for every row, not just three rows, because there are no qualifications on the HotelAvailability table.
For more information about join order, see Joins and performance.
Derby usually chooses a good join order. However, as with index use, you should make sure. Analyze the way Derby is executing your application's queries. See Analyzing statement execution for information on how to do this.