JOIN(1,C) AIX Commands Reference JOIN(1,C)
-------------------------------------------------------------------------------
join
PURPOSE
Joins data bases files.
SYNTAX
+---------------+ +---------+ +-------------+
join ---| +-----------+ |---| one of |---| +---------+ |--->
+-| -e string |-+ | +-----+ | +-| -j num |-+
^| -t char || +-| -a1 |-+ ^| -j1 num ||
|+-----------+| | -a2 | || -j2 num ||
+-------------+ +-----+ |+---------+|
+-----------+
+------------------+
>---| |--- file1 --- file2 ---|
+- -o --- n.num ---+
^ 1 |
+--- , ---+
-----------------
1 Do not put a blank on either side of the comma.
DESCRIPTION
The join command reads file1 and file2, 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 LANG and LC_COLLATE
environment variables, if set, for the fields on which they are to be joined
(normally the first field in each line).
One line appears in the output for each identical join field appearing in both
file1 and file2. The join field is the field in the input files that join
looks at to determine what will be included in the output. The output line
consists of the join field, the rest of the line from file1, and the rest of
the line from file2. You can specify standard input in place of file1 by
substituting a - (minus) for the name.
Fields are normally separated by a blank or a tab character. In this case,
join treats consecutive separators as one, and discards leading separators.
FLAGS
-anum When num is 1, join produces an output line for each line
found in file1 but not in file2. When num is 2, join
produces an output line for each line found in the file2
but not in file1.
Processed November 8, 1990 JOIN(1,C) 1
JOIN(1,C) AIX Commands Reference JOIN(1,C)
-e string Replaces empty output fields with string.
-j[n] num Joins the two files on the numth field of file n. n is 1
or 2. If you do not specify n, join uses the numth field
in each file.
-o n.num[,n.num...] Makes each output line consist of the fields specified in
list, in which each element has the form n.num, where n
is a file number and num is a field number.
-tchar Uses char as the field separator character in the input
and the output. Every appearance of char in a line is
significant. The default separator is a blank. If you
specify -t, the sequence is that of a plain sort. To
specify a tab character, enclose it in single quotation
marks (' ').
EXAMPLES
Note: The vertical alignment shown in these examples may not be consistent
with your output.
1. To perform a simple join operation on two files whose first fields are the
same:
join phonedir names
+----------------------+--------------------+---------------------------------+
| | and "names" is | |
|If "phonedir" | this listing of | |
|contains the | names and | then join displays: |
|following telephone | department | |
|directory: | numbers: | |
+----------------------+--------------------+---------------------------------+
| | | |
|Brown J. 555-6235|Elder Dept. 389| Elder G. 555-1234 Dept. 389 |
|Dickerson B. 555-1842|Frost Dept. 217| Grabczak P. 555-2240 Dept. 311 |
|Elder G. 555-1234|Grabczak Dept. 311| McGuff M. 555-5341 Dept. 454 |
|Grabczak P. 555-2240|McGuff Dept. 454| Wilde C. 555-1234 Dept. 520 |
|Harper M. 555-0256|Wilde Dept. 520| |
|Johnson M. 555-7358| | |
|Lewis B. 555-3237| | |
|McGuff M. 555-5341| | |
|Wilde C. 555-1234| | |
| | | |
+----------------------+--------------------+---------------------------------+
Each line consists of the join field (the last name), followed by the rest
of the line found in "phonedir" and the rest of the line in "names".
2. To display unmatched lines with the command:
Processed November 8, 1990 JOIN(1,C) 2
JOIN(1,C) AIX Commands Reference JOIN(1,C)
join -a2 phonedir names
+----------------------+-------------------+----------------------------------+
|If "phonedir" | and "names" | then join displays: |
|contains: | contains: | |
+----------------------+-------------------+----------------------------------+
| | | |
|Brown J. 555-6235|Elder Dept. 389| Elder G. 555-1234 Dept. 389 |
|Dickerson B. 555-1842|Frost Dept. 217| Frost Dept. 217 |
|Elder G. 555-1234|Grabczak Dept. 311| Grabczak P. 555-2240 Dept. 311 |
|Grabczak P. 555-2240|McGuff Dept. 454| McGuff M. 555-5341 Dept. 454 |
|Harper M. 555-0256|Wilde Dept. 520| Wilde C. 555-1234 Dept. 520 |
|Johnson M. 555-7358| | |
|Lewis B. 555-3237| | |
|McGuff M. 555-5341| | |
|Wilde C. 555-1234| | |
| | | |
+----------------------+-------------------+----------------------------------+
This performs the same join operation as in Example 1, and also lists the
lines of "names" that have no match in "phonedir". It includes "Frost"'s
name and department number in the listing, although there is no entry for
"Frost" in "phonedir":
3. To display selected fields:
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)
Processed November 8, 1990 JOIN(1,C) 3
JOIN(1,C) AIX Commands Reference JOIN(1,C)
+-------------------------+-------------------------+-------------------------+
|If "phonedir" contains: | and "names" contains: | then join displays: |
+-------------------------+-----------------------+---------------------------+
| | | |
| Brown J. 555-6235 | Elder Dept. 389 | 389 Elder G. 555-1234 |
| Dickerson B. 555-1842 | Frost Dept. 217 | 311 Grabczak P. 555-2240 |
| Elder G. 555-1234 | Grabczak Dept. 311 | 454 McGuff M. 555-5341 |
| Grabczak P. 555-2240 | McGuff Dept. 454 | 520 Wilde C. 555-1234 |
| Harper M. 555-0256 | Wilde Dept. 520 | |
| Johnson M. 555-7358 | | |
| Lewis B. 555-3237 | | |
| McGuff M. 555-5341 | | |
| Wilde C. 555-1234 | | |
| | | |
+-------------------------+-----------------------+---------------------------+
4. To perform the join operation on a field other than the first:
sort +2 -3 phonedir | join -j1 3 - numbers
This combines the lines in "phonedir" and "names", comparing the third
field of "phonedir" to the first field of "numbers".
First, this sorts "phonedir" by the third field, because both files must be
sorted by their join fields. The output of sort is then piped to join.
The - (minus sign) by itself causes the join command to use this output as
its first file. The -j"1 3" defines the third field of the sorted
"phonedir" as the join field. This is compared to the first field of
"numbers" because its join field is not specified with a -j flag.
+-------------+---------------------------------------------------------------+
| | this command displays the names listed in "phonedir" for each |
|If "numbers" | telephone number: |
|contains: | |
+-------------+---------------------------------------------------------------+
| | |
| 555-0256 | 555-0256 Harper M. |
| 555-1234 | 555-1234 Elder G. |
| 555-5555 | 555-1234 Wilde C. |
| 555-7358 | 555-7358 Johnson M. |
| | |
+-------------+---------------------------------------------------------------+
Note that join lists all the matches for a given field. In this case, join
lists both "Elder G." and "Wilde C." as having the telephone number
"555-1234". The number "555-5555" is not listed because it does not appear
in "phonedir".
Processed November 8, 1990 JOIN(1,C) 4
JOIN(1,C) AIX Commands Reference JOIN(1,C)
RELATED INFORMATION
See the following commands: "awk, nawk, oawk," "comm," "sort," "cut," and
"paste."
See the environment miscellaneous facility in AIX Operating System Technical
Reference.
See "Introduction to International Character Support" in Managing the AIX
Operating System.
Processed November 8, 1990 JOIN(1,C) 5