Find all hotels in Chicago for which reservations exist. These hotels are displayed in the result table with their respective numbers and names.
SELECT hotel.hno, hotel.name, reservation.rno
FROM hotel, reservation
WHERE hotel.city = 'Chicago' AND
hotel.hno = reservation.hno
To have a list of all hotels in Chicago displayed, irrespective of the availability of one or more reservations, a so-called 'outer join' is used.
A result table is generated which - like the first example - shows all hotels in Chicago with the corresponding reservations, also containing the hotels for which no reservations have been made. The missing entries for the reservation numbers are set to the NULL value.
The outer join is denoted by the (+) operator.
SELECT hotel.hno, hotel.name, reservation.rno
FROM hotel, reservation
WHERE hotel.city = 'Chicago' AND
hotel.hno = reservation.hno (+)
