# Think about join order

For some queries, join order can make the difference between a table scan (expensive) and an index scan (cheap).

Here's an example:

    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.

Parent topic: Always create indexes
Related concepts
Create useful indexes
Make sure indexes are being used, and rebuild them
Think about index order
Decide whether a descending index would be useful