Databases Reference
In-Depth Information
Here is an example of a join involving three tables, assuming for the moment
that salesperson names are unique:
List the names of the products of which salesperson Adams has sold more
than 2,000 units.
The salesperson name data appears only in the SALESPERSON table, and
the product name data appears only in the PRODUCT table. The SALES table
shows the linkage between the two, including the quantities sold. And so the
SELECT statement is:
SELECT PRODNAME FROM SALESPERSON, PRODUCT, SALES
WHERE SALESPERSON.SPNUM=SALES.SPNUM
AND SALES.PRODNUM=PRODUCT.PRODNUM
AND SPNAME='Adams' AND QUANTITY>2000
As described earlier, the SELECT statement joins two tables, SALESPERSON and
SALES, then joins this result to a third table, PRODUCT. The result is:
PRODUCTNAME
Hammer
Saw
Both of these are examples of inner joins. In an
inner join,
only qualifying
results are returned from the joined tables. There are two other types of joins
you can run: outer joins and cross joins. An
outer join
returns qualifying rows
from one table and all rows from the other (outer) table. A
cross join
returns
all possible rows, whether or not they meet the qualifying join logic, in every
possible combination. This is known as a
Cartesian product
. However, before
we show these, we need to introduce a different join syntax.
7.2.2 Using Different Join Syntaxes
Let's look at a variation on our first join example.
SELECT SPNAME, CUSTNAME FROM SALESPERSON, CUSTOMER
WHERE SALESPERSON.SPNUM=CUSTOMER.SPNUM
This is an inner join. The result is limited to rows where the SPNUM meets the
qualifying logic in both tables, giving you the following result:
SPNAME
CUSTNAME
Baker
Main St. Hardware
Adams
Jane's Stores
Baker
ABC Home Stores
Baker
Acme Hardware Store
Carlyle
Fred's Tool Stores
Carlyle
XYZ Stores
Baker
City Hardware
