Joins: Information From Several Tables

Use

In order to respond to some queries, the system must search several tables. The link between these tables is established on the basis of columns that appear in several tables. Statements such as this are called a Join.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

SELECT reservation.rno, customer.name, reservation.arrival, reservation.departure

FROM hotel.customer, hotel.reservation

WHERE customer.name = 'Porter'

AND customer.cno = reservation.cno

Displaying whether and when a reservation was made for the customer Porter

Result

`RNO`	`NAME`	`ARRIVAL`	`DEPARTURE`
`100`	`Porter`	`2004-11-13`	`2004-11-15`
`110`	`Porter`	`2004-12-24`	`2005-01-06`

The link between the two tables is established on the basis of the customer number, that is, only those rows that contain the name Porter and in which the customer numbers are identical are selected. =, <, <=, >, >= and <> are possible operators in the WHERE clause.

The overall result row comprises the desired columns in the relevant table. If columns in two different tables have the same name, the table name must be specified in the statement before the column name; the two names are then linked by a period (.).

SELECT customer.name, reservation.rno, hotel.name hotel_name

FROM hotel.customer, hotel.reservation, hotel.hotel

WHERE customer.name = 'Porter'

AND customer.cno = reservation.cno

AND reservation.hno = hotel.hno

Displaying all the hotels in which the customer Porter has made a reservation. For this purpose, the customer, reservation and hotel tables must be concatenated.

Result

`NAME`	`RNO`	`HOTEL_NAME`
`Porter`	`100`	`Midtown`
`Porter`	`110`	`Beach`

Another example for a join table:

SELECT hotel.name hotel_name, city.zip, city.name city_name

FROM hotel.hotel, hotel.city

WHERE city.state = 'CA' AND city.zip=hotel.zip

Displaying the cities in the state of CA for which hotels are specified in the hotel table

Result

`HOTEL_NAME`	`ZIP`	`CITY_NAME`
`Long Beach`	`90804`	`Long Beach`
`Star`	`90029`	`Hollywood`
`Indian Horse`	`92262`	`Palm Springs`

More Information

SQL Reference Manual, JOIN Predicate (join_predicate)

More examples for Data Query