INTERSECT (Short Name: INT)
Top  Previous  Next

Use the INTERSECT command to form a new table by combining two tables that have one or more common columns.

intersec

Options

FORMING tblname3
Specifies the name of the new table.

tblname1
Specifies the name of the first table to intersect.

USING collist
Specifies which columns to include in the new table. The clause must include at least one common column. You can use the clause to order the columns in the new table and to determine which columns from the original tables will be in the new table.

If you do not include the clause, R:BASE uses all columns from both tables. Columns from tblname1 provide the order for rows and columns in the new table. R:BASE adds columns from tblname2 that are not in tblname1 to the right of the columns from tblname1.

The USING clause also determines which common columns are used for comparison. For example, if frstname and lastname are in both tables but only lastname is included in the USING clause, INTERSECT will compare the tables based only on lastname.

WITH tblname2
Specifies the name of the second table to intersect.

About the INTERSECT Command

A new table only contains the rows in which there were identical values in each of the common columns in both tables. If the common columns for a row in each table have the same value, R:BASE combines the two rows into one in the new table. If the common columns in each table have multiple matches, R:BASE adds a row for each match in the new table. For example, if tblname1 has two rows with an identification number of 100 and tblname2 has three rows with the same identification number, tblname3 will have six rows, one for each possible combination.

If you combine one or more NOTE columns with the INTERSECT command, R:BASE might truncate the last NOTE column. The maximum total row length is 4,092 characters.

In the new table, R:BASE does not update the value in the row for the autonumbered column. The autonumbered column becomes a regular column.

Including Computed Columns in a New Table

R:BASE uses the current values in computed columns to determine whether the values in common computed columns are identical. To include a computed column in a new table, include each column used in the computed column's definition in the USING clause, placing the columns before the computed column. Without such a USING clause, R:BASE makes the computed column a regular column, and displays a message suggesting you rename the column in the new table. If you do not rename the column, the new table will have a column that has the same name but a different data type from the original table. If you have columns with the same name but different data types, you will not be able use the BACKUP and UNLOAD commands.

Increasing the Speed of INTERSECT

You can increase the speed of the INTERSECT command by creating an index for the common column in tblname2. The INTERSECT command does not use an index in tblname1. Below are guidelines for indexing columns in tblname2:

·If one table has more rows than the other table, place the larger table second (tblname2) in the command and create an index for its common column.  
·If both tables have many rows, place the table with the shorter row length first (tblname1)and index the common column in the second table.  
·If the tables have more than one common column, index the common column that has the most unique values in the second table (tblname2).  

Examples

The following command forms the models table by combining the rows from the component and compused tables that contain identical values in a column common in both tables. Because the USING clause is not included, the models table contains all columns from both tables.

INTERSECT component WITH compused FORMING models

The following command lines create a table containing employee information on sales representatives and their sales bonuses. The repsales table only contains the rows that match in employeeand salesbonus tables.

INTERSECT employee WITH salesbonus FORMING repsales +
   USING emplname, empid, hiredate, netamount, transdate, bonus, bonuspct