TOCPREVNEXTINDEX

AcuODBC User's Guide
Version 6.0

5.3.2 JOIN Statements

JOIN statements represent a special instance of the SELECT statement. They enable you to view data from multiple tables, by combining the tables along a common field.

AcuODBC supports a variety of JOIN statements, including the natural joins, inner joins (the default if no join type is specified), left (outer) joins, and right (outer) joins (by reversing a left join). Note that the word "outer" is optional and is provided for clarity; it has no effect on the join.


Note: When you list more than one table and do not include a condition with the ON or WHERE clause, the result is all possible pairs of rows, or the product of the number of rows in the tables. For example, if you join two tables that each have six rows and don't specify a condition, the resulting view will have 36 rows.

For additional information on JOIN statements, see any standard SQL text.

Inner joins

Inner joins include only those rows where matches are found. The syntax for an inner join is:

    SELECT <field-1>[, <field-2> . . .]   
    FROM <table-name-1>    
    [INNER] JOIN <table-name-2>   
    ON [<table-name-1>.]<field-name-1> = [<table-name-2>.]<field-name-2>   

(Note that the word "inner" is optional; it is provided here for clarity.)

For example, if you want a listing of pets and their owners, you can join the "pets" and "owners" tables as shown in the following command:

    SELECT patient_name, owner   
    FROM pets    
    INNER JOIN clients    
    ON pets.owner_id = clients.client_id   

Your application returns the following view:

You can join more than two tables. For example, to see a listing of pets, the treatments they received, and their owners, enter the following command:

    SELECT patient_name, explanation, owner   
    FROM (pets INNER JOIN codes    
    ON pets.treatment = codes.treatment)    
    INNER JOIN clients    
    ON pets.owner_id = clients.client_id   

Your application returns the following view:

Note that Copper is not included in this view, because the "pets" table does not list a treatment for her.

Outer joins

Left joins

Left joins include all rows from table-name-1, matched or not, plus the columns from matching rows of table-name-2. The syntax for left joins is:

    SELECT * FROM <table-name-1> LEFT JOIN <table-name-2>    
    ON <field-name-1>=<field-name-2>   

For example, you can perform a left join on the tables "pets" and "codes" to see a listing of pets and the treatments they received:

    SELECT patient_name, explanation   
    FROM pets    
    LEFT JOIN codes    
    ON pets.treatment = codes.treatment   
    ORDER BY patient_name   

The following view is a result of this command. Here you can see that Copper's treatment does not match any of the explanations in the "codes" table.

Right joins

To perform the equivalent of a right join, simply reverse the order of the tables in the query. Reverse the order of the tables in the previous query to form this new join statement:

    SELECT patient_name, explanation   
    FROM codes    
    LEFT JOIN pets    
    ON codes.treatment = pets.treatment   
    ORDER BY patient_name   

The following view is a result of this command. Here you can see that none of the patients has had any lab work. In addition, Copper is not listed among the patients because her treatment didn't match any of the treatment codes.

Self joins

You can also self-join a table, essentially putting two copies of the table side by side. To do this, your select-expression lists the table twice in the FROM clause. You cannot use the same table name twice, so you must assign it a different alias each time. This is similar to the column alias introduced in section 5.3.1, "SELECT Command." Use the aliases to refer to the table in the SELECT and WHERE clauses as if there were two tables.

The syntax for this type of select-expression is:

    SELECT <alias1.field-name> [, <alias2.fieldname>] [, ...]]   
    FROM [<table-name> <alias1>] [, <table-name> <alias2>] [, ...]]   

As in other SELECT statements, you can narrow the focus of the query by including a WHERE clause, as in the following example:

    SELECT a.treatment, a.explanation, b.treatment, b.explanation   
    FROM codes a, codes b   
    WHERE a.treatment = b.treatment   

The following view is a result of this query:


Acucorp, Inc.
Voice: (800) 262-6585 (U.S.A. and Canada)
Voice: (858) 689-4500
Fax: (858) 689-4550

Please share your comments on this manual
or on any Acucorp documentation with the
Acucorp Communications Department.
TOCPREVNEXTINDEX
1988-2003 Acucorp, Inc.
All rights reserved.