Using inner joins

An inner join produces a results table consisting of only those rows that correspond to the tables specified in the query. A query expression can specify inner joins in either its FROM clause or its WHERE clause.

The basic syntax for a join is:

Syntax

{
table_ref
[ INNER | LEFT [ OUTER ]] JOIN
table_ref ON search_condition}

The following example demonstrates a join that retrieves information from two tables relating customers and their order information.

SELECT order.ordernum, order.custnum, customer.name,
order.orderdate, order.shipdate
FROM order INNER JOIN customer
ON order.custnum = customer.custnum;

The statement produces the following results:

Ordernum CustNum Name           OrderDate   ShipDate1        53      Offside Hockey 2007-01-26 2007-01-31
2        81      Off The Wall   2006-10-05 2006-10-10
3        66      Hero Football 2006-09-23 2006-09-28
4        83      Swift Running 2007-01-17 2007-01-22
5        72      Pro Skates     2007-02-12 2007-02-17
. . .

In this section:

Employing a table alias