NAME

join − relational database operator

SYNOPSIS

join [ options ] file1 file2

DESCRIPTION

Join forms, on the standard output, a join of the two relations specified by the lines of file1 and file2. If file1 is ‘−’, the standard input is used. 

File1 and file2 must be sorted in increasing ASCII collating sequence on the fields on which they are to be joined.  If not otherwise stated, join normally joins on the first field in each line. 

There is one line in the output for each line in file1 and file2 that have identical join fields.  The output line normally consists of the common field, then the rest of the line from file1, then the rest of the line from file2, but the order of output of the fields can be changed with the -o option, described below. 

Fields are normally separated by blank, tab or newline.  In this case, multiple separators count as one, and leading separators are discarded.  The field separators can be changed if desired. 

These options are recognized:

−an In addition to the normal output, produce a line for each unpairable line in file n, where n is 1 or 2. 

−e s Replace empty output fields by string s.

−jn m
Join on the mth field of file n. If n is missing, use the mth field in each file.

−o list
Each output line comprises the fields specifed in list, each element of which has the form n.m, where n is a file number and m is a field number. 

−tc Use character c as a separator (tab character).  Every appearance of c in a line is significant. 

EXAMPLE

Consider that we have two files called people and work, which contain a list of peoples’ name and their workplaces.  The people file contains:

Austen
Bailey
Clark
Daniels
Davidson
Dawson
Morgan
Parker
Smith
Williams

and the work file contains:

Jack Austen Anchor Brewery
Maryann Clark Shoeshop
Steve Daniels McGuiness Distillery
Sylvia Dawson Laphroaig
Henry Morgan Downtown Theatre
Sally Smith Talcum Powdery
Bill Williams Computer Software

The example below shows the effects of the join program:

join -j1 1  -j2 2  -o 2.1 2.2 2.3 2.4 2.5 people work
Jack Austen Anchor Brewery
Maryann Clark Shoeshop
Steve Daniels McGuiness Distillery
Sylvia Dawson Laphroaig
Henry Morgan Downtown Theatre
Sally Smith Talcum Powdery
Bill Williams Computer Software

The join was done between the first field of the people file, and the second field of the work file.  The -o option is used to get the output lines in first name - last name order. 

For every first field in people which matches the second field in work, the output consists of the peoples names followed by their place of work.  If the -o option was not used, the peoples’ names would appear last name first. 

SEE ALSO

sort(1), comm(1), awk(1)

BUGS

With default field separation, the collating sequence is that of sort −b; with −t, the sequence is that of a plain sort. 

The conventions of join, sort, comm, uniq, look and awk(1) are wildly incongruous.

7th Edition