Use the JOIN command to form a new table by combining rows from two existing tables based on a comparison of the columns you specify.
Option
FORMING tblname3
Specifies the name of the new table.
TEMPORARY
Creates a temporary table that disappears when the database is disconnected.
tblname1
Specifies the name of the first table to join.
USING colname1
Specifies the columns to be compared; colname1 is a column in tblname1.
USING colname2
Specifies the columns to be compared; colname2 is a column in tblname2.
WHERE compare_op
States the condition used to compare columns. If omitted, the condition is = or EQ (equal). Use only the following comparison operators in a JOIN WHERE clause:
Comparison Operator |
Rows are included if... |
|
= |
EQ |
colname1 is equal to colname2 (the default) |
<> |
NE |
colname1 is not equal to colname2 |
> |
GT |
colname1 is greater than colname2 |
>= |
GE |
colname1 is greater than or equal to colname2 |
< |
LT |
colname1 is less than colname2 |
<= |
LE |
colname1 is less than or equal to colname2 |
WITH tblname2
Specifies the name of the second table to join.
About the JOIN Command
JOIN combines all columns from two tables into a new table. R:BASE adds rows to the new table (tblname3) that satisfy the comparison condition (compare_op) of a column from each of the existing tables (tblname1 and tblname2). You must specify the columns to be compared: colname1 is a column in tblname1 and colname2 is a column in tblname2.
The important characteristic of JOIN is the relationship between column values in the two tables. R:BASE compares the values of one column in the first table to the values of another column in the second table based on the operator you specify. The two tables do not need common columns to satisfy the comparison.
Column Names
If any columns in the existing tables have the same name, change the column name in one of the tables before you join them or before you use the new table. (R:BASE creates the new table and gives the two columns the same name.) If you want to use the columns with the same column name in the USING clause, change one of them before you join the tables. The column names can be different, but their data type and size must be the same.
When the comparison between the two specified columns is satisfied, R:BASE creates a row in the new table that is a combination of all columns from the row in tblname1 and its corresponding row in tblname2.
NOTE Columns
If you use JOIN to combine one or more columns that have a NOTE data type, R:BASE, starting with the last column that has a NOTE data type, truncates the column until the row length is within the 32,768-character limit.
Autonumbered Columns
In the new table, R:BASE does not update the values for the autonumbered column. The autonumbered column becomes a regular column.
Improving Performance with Indexing
When a WHERE clause is used, you can speed up JOIN by creating an index for the compared column in tblname2. JOIN does not use an index in tblname1. If one table has more rows than the other table, name the larger table second in the command and index its compared column. If both tables have many rows, place the table with the shorter row length first, then create an index for the compared column in the second table.
Example
The following command creates a new table named proddetail by combining rows from the prodlocation and product tables. The rows included in proddetail are based on a comparison of the values in the model column in prodlocation and the values in the model column in product. The WHERE clause specifies that the new table will contain only the rows in which the model value equals the model value. Because the equal sign (=) is the default operator, you could omit the WHERE =. All the columns in prodlocation and product are included in proddetail.
JOIN prodlocation USING model WITH product USING model +
FORMING proddetail WHERE =
