Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Table Joins

It's often the case that the data you want to return from a query are spread across more than one table. Commonly this occurs as a result of master-detail relationships such as the one between the project and project_hours tables. The project table contains one row describing each project, whereas, for each project, project_hours contains many rows detailing time charged to that project. To link such master and detail data together in the results from a SELECT statement, you use a relational operation called a join.

Inner Joins

Imagine that you need to generate a detailed listing of time charged to each of your projects. You want that listing to include project name, the date on which work was performed, and the number of hours involved. What makes this request interesting is that you need to list two tables in your FROM clause. Project names come from the project table, while dates and hours come from the project_hours table. How do you SELECT from two tables at once? The answer is to use a JOIN clause such as the one in Example 4-25.

Example 4-25. Selecting related rows from two tables

SELECT p.project_name,  ph. employee_id, 
       ph.time_log_date, ph.hours_logged
FROM project p JOIN project_hours ph
     ON p.project_id = ph.project_id;

The JOIN clause, which is a subclause of the FROM clause, is new in Oracle9i Database and specifies that data are to be drawn from not one table, but from two, in this case from the project and project_hours tables. The ON clause, subsidiary to JOIN, ...