Join

A join occurs when two or more tables are connected by one or more columns in common, which creates a new table of results. The following figure shows a query that uses a subset of the items and stock tables to illustrate the concept of a join.

Figure 1. A join between two tables

This figure is described in the surrounding text.

The following query joins the customer and state tables.

Figure 2. Query

SELECT UNIQUE city, state, zipcode, sname
   FROM customer, state
   WHERE customer.state = state.code;

The result consists of specified rows and columns from both the customer and state tables.

Figure 3. Query result

city            state zipcode sname

Bartlesville    OK    74006   Oklahoma
Blue Island     NY    60406   New York
Brighton        MA    02135   Massachusetts
Cherry Hill     NJ    08002   New Jersey
Denver          CO    80219   Colorado
Jacksonville    FL    32256   Florida
Los Altos       CA    94022   California
Menlo Park      CA    94025   California
Mountain View   CA    94040   California
Mountain View   CA    94063   California
Oakland         CA    94609   California
Palo Alto       CA    94303   California
Palo Alto       CA    94304   California
Phoenix         AZ    85008   Arizona
Phoenix         AZ    85016   Arizona
Princeton       NJ    08540   New Jersey
Redwood City    CA    94026   California
Redwood City    CA    94062   California
Redwood City    CA    94063   California
San Francisco   CA    94117   California
Sunnyvale       CA    94085   California
Sunnyvale       CA    94086   California
Wilmington      DE    19898   Delaware