AIX Tip of the Week

AIX Tip of the Week: UNIX "join" Command

Audience: AIX Users

Date: December 1998

A little known, but useful, UNIX data manipulation tool is the join command. The function of the join command is similar to a relational database join. It combines common lines from two sorted text files based on a common field. The output of the join command contains common field and the remainder of each line from both files. Unmatched lines are not included in the output.

The following example illustrates the use of the join command. In this example, a hypothetical inventory file is joined with a description file based on the part number. The inventory file is a two column file containing a list of part numbers and its quantity on hand. The description file is a two column file containing the part number and its description. The common field between the two files is the part number, and both files are sorted on the part number.

Example

$ cat description.txt Part Number; Description 1; Pencils 2; Erasers 3; Paper Clips, Regular 3A; Paper Clips, Large 4; Paper, Regular 8.5x11 4A; Paper, Legal Size 5; Envelope 6; Staples 7; Tape 8; Hole Punch 9; Glue 10; Pen, Blue 10A; Pen, Red 10B; Pen, Black 10C; Pen, Green ....etc... $ cat inventory.txt Part Number; Quantity on Hand 1; 4 3; 16 3A; 24 4; 0 6; 1 8; 8 10; 2 10B; 14 ....etc.... $ join -t";" inventory.txt description.txt Part Number; Quantity on Hand; Description 1; 4; Pencil 3; 16; Paper Clips, Regular 3A; 24; Paper Clips, Large 4; 0; Paper, Regular 8.5x11 6; 1; Staples 8; 8; Hole Punch 10; 2; Pen, Blue 10B; 14; Pen, Black ....etc....

Note that only the matching lines are displayed. Unmatched lines are discarded. See the man page for the join command for other useful options.