[ Previous | Next | Table of Contents | Index | Library Home | Legal | Search ]

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.
  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 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.

  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 -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 5L Version 5.1 System User's Guide: Operating System and Devices.

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

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


[ Previous | Next | Table of Contents | Index | Library Home | Legal | Search ]