Mastering Oracle SQL, 2nd Edition

Chapter 3. Joins

Most things in life are not self-contained. There is not one shop where you will find all your requirements. This is valid for database tables as well. Quite often, you need information from more than one table. The SQL construct that combines data from two or more tables is called a join. This chapter takes you into the details of joins, their types, and their usage.

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.

Tip

The ...