Previous Table of Contents Next


Module 69
join

DESCRIPTION

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.

COMMAND FORMAT

Following is the general format of the join command.

     join [ -an ] [ -e str ] [ -jn m ] [ -o list ] [ -tc ] file1 file2

Options

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.

Arguments

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.

JOINING FILES

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.

file1: file2:
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
02213 Sun3/110 Sun Microsystems
03321 Sun4/110 Sun Microsystems
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.

RELATED COMMANDS

Refer to the nawk, comm, cut, grep, paste, sort, and uniq commands described in modules 6, 22, 29, 60, 101, 122, and 149.

APPLICATIONS

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.

TYPICAL OPERATION

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.

1.  Type sort -t: +3.0n -4.0n +2.0n -3.0n /etc/passwd > mypasswd and press Return to sort the passwd file on the group ID and user ID fields.
2.  Type sort -t: +3.0n -4.0n /etc/group > mygroup and press Return to sort the group file on the group ID field.
3.  Type join -t: -j1 4 -j2 3 -o 1.1 1.3 2.1 2.3 1.6 mypasswd mygroup and press Return.
     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
     .
     .
4.  Type rm mypasswd mygroup and press Return to remove the files created in this activity.
5.  Turn to Module 95 (SV), Module 21 (BSD) to continue the learning sequence.


Previous Table of Contents Next