Previous | Table of Contents | Next |
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.
-a n | 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 m th field of file n . If you do not specify n , the m th 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. | ||
-t c | 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 |
Previous | Table of Contents | Next |