|Previous||Table of Contents||Next|
The external join command performs the relational database join function. It joins two files together based on a key field (column) that exists in both files. The files must be sorted on the key field. If the contents in the key field of both files match, the two lines are merged. The merged line is written to the standard output.
Following is the general format of the join command.
join [ -an ] [ -e str ] [ -jn m ] [ -o list ] [ -tc ] file1 file2
The following list describes the options and their arguments that may be used to control how join functions.
|-an||If a field in file n does not have a match in the other file, the line is written to file n. For example, if you use|
|join -a1 file1 file2|
|and file1 has a line in it with a key field that is not matched in file2, then that line is written to the file named 1.|
|-e str||Replaces empty output fields with the str string. The -o option causes empty fields in certain conditions.|
|-jn m||The join is performed using the mth field of file n. If you do not specify n, the mth field is used from both files. The fields are numbered beginning with 1.|
|-o list||Specifies the fields to be displayed on output after the join has been performed. The list is comprised of n.m descriptions that define which fields from each file are to be displayed. The n refers to the file name and the m refers to the field number. Multiple n.m descriptions may be used in the list. They must be space separated. An example is,|
|join -j1 3 -j2 4 -o 1.1 1.3 2.1 1.5 file1 file2|
|which uses field 3 of file1 and field 4 of file2 to join the files. The output is field 1 of file1, field 3 of file1, field 1 of file2, and field 5 of file1.|
|-tc||Character c is used as the input and output field separator. For example,|
|join -t: file1 file2|
|informs join to use a : (colon) field separator in place of a tab.|
|The default input field separators are blanks (spaces, tabs, and new-lines). If the default separators are being used, multiple occurrences count as only one field separator. Leading separators are ignored. The default output separator is a space.|
The following list describes the arguments that may be passed to the join command.
|-||A hyphen may be used in place of file1 to cause join to read the standard input as file1.|
|file1||The first file to be joined to the second file. The file must be sorted in ASCII collating sequence on the fields that are used to join the file to file2.|
|file2||The second file to be joined to the first file. The file must be sorted in ASCII collating sequence on the fields that are used to join the file to file1.|
The joining of files is a relational database function. It involves the combining of two files (tables) that share a common field (key). Each file must be sorted on this field. The join command reads the first line of the first file, then reads lines from the second file until the contents in the key field of the second file match or exceed the contents in the key field of the first file. If a match occurs, the line from the first file and the line from the second file are combined and displayed on the output. If no match occurs, join begins the process again except reading is done from the first file. This process continues until one or both files have been read to the end and no more lines in the other file can match the last line of the file.
An example of a join command might help clarify how the join function works. Let's assume we have the following files.
|01123||HP 9000/825||01123||Hewlett Packard|
|02213||Sun 3/110||02213||Sun Microsystems|
|03321||Sun 4/110||03321||Sun Microsystems|
|08412||HP 9000/835||08412||Hewlett Packard|
The following command should produce the combined output as shown.
$ join -t"<Tab>" file1 file2
|01123||HP 9000/825||Hewlett Packard|
|08412||HP 9000/835||Hewlett Packard|
DIAGNOSTICS AND BUGS
If fields are separated by spaces and tabs you need to use the sort -b command for sorting the file. If you use the -t option you need to use the sort command without the -b option.
Unfortunately, the comm, join, sort, and uniq commands have different format conventions for referencing data.
Numeric filenames may conflict with the -o option and the field descriptions that relate to it. One solution is to mv your files to temporary names beginning with a nonnumeric character. Then after the join, move the files back to their original names.
Refer to the nawk, comm, cut, grep, paste, sort, and uniq commands described in modules 6, 22, 29, 60, 101, 122, and 149.
You use the join command to join related files together based on a key field. The output is a subset of the fields and lines from both files. It is useful in combining two related tables (files) together to create one larger table.
In this activity you use the join command to join the /etc/passwd and /etc/group file together to show the user name, user ID, group name, group ID, group name, and home directory. First we must insure that both files are sorted in ASCII collating sequence. Begin at the shell prompt.
cj> join -t: -j1 4 -j2 3 -o 1.1 1.3 2.1 2.3 1.6 mypasswd mygroup root:0:0:root . . mylogin:60:40:tech . .
|Previous||Table of Contents||Next|