join Command Purpose Joins the data fields of two files. Syntax join [ -a FileNumber | -v FileNumber ] [ -e String ] [ -t Character ] [ -j [FileNumber] Field ] [ -o FileNumber.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. Both files must be sorted according to the collating sequence specified by the current value of the LC_COLLATE category. One line appears in the output for each identical join field ap- pearing in both files. The join field is the field in the input files that the join command examines 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 - (minus) as the file name. Both input files cannot be specified with a - (minus). Fields are normally separated by a space, a tab character, or a new-line character. In this case, the join command treats con- secutive separators as one and discards leading separators. A maximum of 40 fields is allowed. 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. This flag is equivalent to using the -j1 Field flag. -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. This flag is equivalent to using the -j2 Field flag. -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 File1 and File2, respectively. -e String Replaces empty output fields with the string specified by the String parameter. -j Field Joins the two files using the field specified by the Field variable in each file. The value of the field variable must be a positive decimal integer. This flag is equivalent to using the -1 Field and -2 Field flags together. Note: The -j flag is an obsolete feature and may not be valid in the future. -j1 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. This flag is equivalent to using the -1 Field flag. Note: The -j1 flag is an obsolete feature and may not be valid in the future. -j2 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. This flag is equivalent to using the -2 Field flag. Note: The -j2 flag is an obsolete feature and may not be valid in the future. -o FileNumber.Field Constructs output lines from the fields specified in the FileNumber.Field variable list. Each field is designated by a file and a field number. The value of the FileNumber variable must be either 1 or 2, corresponding to the files specified by File1 and File2, respectively. The value of the Field variable must be a positive decimal integer. Multiple fields may be listed by using a , (comma), blank, or tab separa- tor. -t Character Uses the character specified by the Character param- eter as the field separator character in the input and the out- put. 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, respectively. Examples Note: The vertical alignment shown in the following examples may not be consistent with your output. 1. 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. 2. To display unmatched lines with the join command, enter: join -a1 phonedir names If phonedir and names are the same as in Example 1, the join com- mand 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 performs the same join operation as in Example 1 , and also lists the lines of names that have no match in the phonedir file. Frost and Nicholson are included in the listing, even though they do not have entries in the phonedir file. 3. 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 4. To perform the join operation on a field other than the first, enter: sort +2 -3 phonedir | join -j1 3 - numbers This combines the lines in the phonedir and numbers files, com- paring the third field of the phonedir file to the first field of the numbers file. First, this 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 - (minus) by itself causes the join command to use this output as its first file. The -j1 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 -j 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. Implementation Specifics This command is part of AIX Base Operating System (BOS) Runtime. File /usr/bin/join Contains the join command. /usr/lib/nls/loc/*.src Contains collation information. Suggested Reading Prerequisite Information Understanding Files in System User's Guide introduces you to files and ways to work with them. Input and Output Handling User's Overview in System User's Guide describes how the AIX Operating System processes input and out- put. For information on collating sequences, see National Language Support Overview in System User's Guide. Related Information The awk command, comm command, sort command, cut command, paste command.