3.1 What Is a Join Query?

A join query extracts information from two or more tables or views. A join query differs from a regular query in at least the following two ways:

• The FROM clause of a join query refers to two or more tables or views.

• A condition is specified in the join query (known as join condition) that relates the rows of one table to the rows of another table.

The following example illustrates a simple join query:

SELECT department.location_id, department.name, location.regional_group

FROM department JOIN location

ON department.location_id = location.location_id;



LOCATION_ID NAME                 REGIONAL_GROUP

----------- -------------------- ---------------

        122 ACCOUNTING           NEW YORK

        124 RESEARCH             DALLAS

        167 OPERATIONS           BOSTON

This example queries two tables. The department name is stored in the department table, whereas each department's region is stored in the location table. Notice the JOIN keyword between the two tables names in the FROM clause. The SELECT list may include columns from any of the tables specified in the FROM clause. The clause starting with the keyword ON specifies the join condition.

The syntax shown in the preceding example is the standard SQL join syntax supported from Oracle9i onwards. The Appendix A describes an older syntax that you should avoid using, but will often encounter in older code.