join Command

AIX Version 4.3 Commands Reference, Volume 3

join Command

Purpose

Joins the data fields of two files.

Syntax

join [ -a FileNumber | -v FileNumber ] [ -e String ] [ -o List ] [ -t Character ] [-1 Field ] [ -2 Field ] File1 File2

Description

The join command reads the files specified by the File1 and File2 parameters, joins lines in the files according to the flags, and writes the results to standard output. The File1 and File2 parameters must be text files. Both File1 and File2 must be sorted in the collating sequence of sort -b on the field that they are being joined by before invoking the join command.

One line appears in the output for each identical join field appearing in both files. The join field is the field in the input files examined by the join command to determine what will be included in the output. The output line consists of the join field, the rest of the line from the file specified by the File1 parameter, and the rest of the line from the file specified by the File2 parameter. Specify standard input in place of either the File1 or File2 parameter by substituting a - (dash) as the file name. Both input files cannot be specified with a - (dash).

Fields are normally separated by a space, a tab character, or a new-line character. In this case, the join command treats consecutive separators as one and discards leading separators.

Flags

-1 Field

Joins the two files using the field specified by the Field variable in the File1 input file. The value of the Field variable must be a positive decimal integer.

-2 Field

Joins the two files using the field specified by the Field variable in the File2 input file. The value of the Field variable must be a positive decimal integer.

-a FileNumber

Produces an output line for each line in the file specified by the FileNumber variable whose join fields do not match any line in the other input file. The output lines are produced in addition to the default output. The value of the FileNumber variable must be either 1 or 2, corresponding to the files specified by the File1 and File2 parameters, respectively. If this flag is specified with the -v flag, this flag is ignored.

-e String

Replaces empty output fields with the string specified by the String variable.

-o List

Constructs an output line to comprise the fields specified in the List variable. One of the following forms applies to the List variable:

FileNumber.Field	Where FileNumber is a file number and Field is a decimal-integer field number. Separate multiple fields with a , (comma) or space characters with quotation marks around the multiple fields.
0 (zero)	Represents the join field. The -o 0 flag essentially selects the union of the join fields.

-t Character

Uses the character specified by the Character parameter as the field separator character in the input and the output. Every appearance of the character in a line is significant. The default separator is a space. With default field separation, the collating sequence is that of the sort -b command. If you specify -t, the sequence is that of a plain sort. To specify a tab character, enclose it in single quotation marks.

-v FileNumber

Produces an output line for each line in the file specified by the FileNumber variable whose join fields do not match any line in the other input file. Default output is not produced. The value of the FileNumber variable must be either 1 or 2, corresponding to the files specified by File1 and File2 parameters, respectively. If this flag is specified with the -a flag, the -a flag is ignored.

Exit Status

This command returns the following exit values:

0	Successful completion.
>0	An error occurred.

Examples

Note: The vertical alignment shown in the following examples might not be consistent with your output.

To perform a simple join operation on two files where the first fields are the same, enter:

join phonedir names

If the phonedir file contains the following names:

Adams A.        555-6235
Dickerson B.    555-1842
Erwin G.        555-1234
Jackson J.      555-0256
Lewis B.        555-3237
Norwood M.      555-5341
Smartt D.       555-1540
Wright M.       555-1234
Xandy G.        555-5015

and the names file contains these names and department numbers:

Erwin           Dept. 389
Frost           Dept. 217
Nicholson       Dept. 311
Norwood         Dept. 454
Wright          Dept. 520
Xandy           Dept. 999

the join command displays:

Erwin G.        555-1234        Dept. 389
Norwood M.      555-5341        Dept. 454
Wright M.       555-1234        Dept. 520
Xandy G.        555-5015        Dept. 999

Each line consists of the join field (the last name), followed by the rest of the line found in the phonedir file and the rest of the line in the names file.

To display unmatched lines with the join command, enter:
```
join -a1 phonedir names
```
If the phonedir and names files are the same as in Example 1, the join command displays:
```
Erwin G.        555-1234         Dept. 389
Frost                            Dept. 217
Nicholson                        Dept. 311
Norwood M.      555-5341         Dept. 454
Wright M.       555-1234         Dept. 520
Xandy G.        555-5015         Dept. 999
```
This command performs the same join operation as in Example 1, and also lists the lines of names that have no match in the phonedir file. The names Frost and Nicholson are included in the listing, even though they do not have entries in the phonedir file.
To display selected fields with the join command, enter:
```
join -o 2.3,2.1,1.2,1.3 phonedir names
```
This displays the following fields in the order given:
Field 3 of names Department number

Field 1 of names Last name

Field 2 of phonedir First initial

Field 3 of phonedir Telephone number
If the phonedir file and names files are the same as in Example 1, the join command displays:
```
389              Erwin G.        555-1234
454              Norwood M.      555-5341
520              Wright M.       555-1234
999              Xandy G.        555-5015
```
To perform the join operation on a field other than the first, enter:
```
sort +2 -3 phonedir | join -1 3 - numbers
```
This command combines the lines in the phonedir and numbers files, comparing the third field of the phonedir file to the first field of the numbers file.
First, this command sorts the phonedir file by the third field, because both files must be sorted by their join fields. The output of the sort command is then piped to the join command. The - (dash) by itself causes the join command to use this output as its first file. The -1 3 flag defines the third field of the sorted phonedir file as the join field. This is compared to the first field of numbers because its join field is not specified with a -2 flag.

If the numbers file contains:
```
555-0256
555-1234
555-5555
555-7358
```
then this command displays the names listed in the phonedir file or each telephone number:
```
555-0256        Jackson J.
555-1234        Erwin G.
555-1234        Wright M.
```
Note that the join command lists all the matches for a given field. In this case, the join command lists both Erwin G . and Wright M. as having the telephone number 555-1234 . The number 555-5555 is not listed because it does not appear in the phonedir file.

Files

/usr/bin/join	Contains the join command.
*/usr/lib/nls/loc/.src**	Contains collation information.

Related Information

The awk command, comm command, cut command, paste command, sort command.

Files Overview in AIX Version 4.3 System User's Guide: Operating System and Devices.

Input and Output Redirection Overview in AIX Version 4.3 System User's Guide: Operating System and Devices.

National Language Support Overview for Programming in AIX General Programming Concepts: Writing and Debugging Programs.

Field 3 of `names`	Department number
Field 1 of `names`	Last name
Field 2 of `phonedir`	First initial
Field 3 of `phonedir`	Telephone number