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.