The following statement asks whether there is a reservation for the customer 'Porter' and, if so, for what date.
To answer this question, two tables must be searched. The customer name is stored in the table 'customer', the reservation date in the table 'reservation'. The connection between these tables is established by the customer number which occurs in both tables. Such a statement is called a join.
SELECT reservation.rno, customer.name, reservation.arrival,
departure
FROM customer, reservation
WHERE customer.name = 'Porter' AND
customer.cno = reservation.cno
Syntax note:
Joining the two customer numbers in the WHERE clause establishes the necessary bridge. Possible operators are '=' (equal to), '<' (less than), '<=' (less than or equal to), '>' (greater than), '>=' (greater than or equal to) and '<>' (not equal to).
The whole query can be read in the following way:
Find all pairs of rows from 'customer' and 'reservation', where the customer number is the same in both halves of rows and the name in the customer table is 'Porter'. Select the reservation number, the customer name, and the traveling dates from the concatenated row.
The next example joins the two tables without any restriction to a particular person. Therefore the previously found person is contained in this result.
SELECT reservation.rno, customer.cno, name,
reservation.arrival, departure
FROM customer, reservation
WHERE customer.cno = reservation.cno
If columns in two different tables have the same name, the table name must be specified in front of the column name. The two names are connected by a dot. To improve the legibility of statements, it is recommended to place the table name also in front of unique column names and to connect the names by a dot.