You can use the SELECT statement to produce reports that contain information from two or more tables. This is commonly referred to as a join. For example, you can join data from the STAFF and ORG tables to form a new table. To join two tables, specify the columns you want to be displayed in the SELECT clause, the table names in a FROM clause and the search condition in the WHERE clause. The WHERE clause is optional.
The next example associates the name of each manager with a department name. You need to select information from two tables since the employee information (STAFF table) and the departmental information (ORG table) are stored separately. The following query selects the NAME and DEPTNAME columns for STAFF and ORG tables, respectively. The search condition narrows down the selection to rows where the values in the MANAGER column are the same as the values in the ID column:
SELECT DEPTNAME, NAME
FROM ORG, STAFF
WHERE MANAGER = ID
Figure 3 demonstrates how columns in two different tables are compared. The boxed values indicate a match where the search condition has been satisfied.
Figure 3. Selecting from STAFF and ORG tables
|
The SELECT statement produces the following result:
DEPTNAME NAME
-------------- ---------
Mid Atlantic Sanders
South Atlantic Marenghi
New England Hanes
Great Lakes Plotz
Plains Fraye
Head Office Molinare
Pacific Lea
Mountain Quill
The result lists the name of each manager and his or her department.