join -- join two sorted, textual relational databases

SYNOPSIS

join [-i] [-a n] [-e s] [-o list] [-t c] [-v n] [-1 n] [-2 n] file1 file2

join [-i] [-a n] [-e s] [-j[n] m] [-o list] [-t c] file1 file2

join joins two databases. It assumes that both file1 and file2 contain textual databases in which each input line is a record and that the input records are sorted in ascending order on a particular join key field (by default the first field in each file). If you specify - in place of file1 or file2, join uses the standard input for that file.

Conceptually, join computes the Cartesian product of records from both files. By default, spaces or tabs separate input fields and join discards any leading or trailing white space. (There can be no white-space delimited empty input fields.) It then generates output for those combined records in which the join key field (the first field by default) matches in each file. The default output for join is the common join key field followed first by all the other fields in file1, then all the other fields in file2. The other fields from each file appear in the same order they appeared in the original file. The default output field separator is a space character.

Options

-a n: produces an output line for lines that do not match in addition to one for a pair of records that do match. If you specify n as one of 1 or 2, join produces unpaired records from only that file. If you specify both -a 1 and -a 2, it produces unpaired records from both files.
-e string: replaces empty fields (selected by -o) with string on output.
-i: ignores case when comparing fields.
-j[n] m: uses field number m as the join key field. By default, the join key field is the first field in each input line. As with the -a option, if n is present, this option specifies the key field just for that file; otherwise, it specifies it for both files.
-o list ...: specifies the fields to be output. You can specify each element in list as either n.m where n is a file number (1 or 2) and m is a field number, or as 0 (zero), which represents the join field. You can specify any number of output fields by separating them with blanks or commas. The POSIX-compatible form (listed first in SYNOPSIS) requires that you specify multiple output fields as a single argument; therefore shell quoting may be necessary. join outputs the fields in the order you list them.
-t c: sets the field separator to the character c. Each instance of c introduces a new field, making empty fields possible.
-v n: suppresses matching lines. If you specify n as one of 1 or 2, join produces unpaired records from only that file. If you specify both -v 1 and -v 2, it produces unpaired records from both files. This does not suppress any lines produced using the -a option.
-1 n: uses the nth field of file1 as the join key field.
-2 n: uses the nth field of file2 as the join key field.

EXAMPLES

The following script produces a report about files in the current directory containing file name, file mode, and a guess at what the file contains:

file * | tr -s ':' ';' >temp1
ls -l | tr -s ' ' ';' >temp2
join -t ';' -2 9 -o 1.1,2.1,1.2 -- temp1 temp2
rm temp[12]

This example uses the POSIX implementation of the join command. Using the obsolete form of the command, the third line of the script might look like:

join -t ';' -j2 9 -o 1.1 2.1 1.2 temp1 temp2

DIAGNOSTICS

Possible exit status values are:

0

Successful completion.

1

Failure due to any of the following:

— invalid syntax

— the wrong number of command line arguments

— cannot open the input file

— badly constructed output list

— too many -o options on the command line

2

Failure due to an invalid command line argument.

Most diagnostics deal with argument syntax and are self-explanatory. For example:

Badly constructed output list at "list": indicates that the list for a -o option did not have the proper syntax.

PORTABILITY

Windows 8.1. Windows Server 2012 R2. Windows 10. Windows Server 2016. Windows Server 2019. Windows 11. Windows Server 2022. POSIX.2. x/OPEN Portability Guide 4.0. All UNIX systems.

POSIX considers the -j option obsolete.

NOTE

The join command supplied with PTC MKS Toolkit should not be confused with the Windows join command.

AVAILABILITY

PTC MKS Toolkit for Power Users
PTC MKS Toolkit for System Administrators
PTC MKS Toolkit for Developers
PTC MKS Toolkit for Interoperability
PTC MKS Toolkit for Professional Developers
PTC MKS Toolkit for Professional Developers 64-Bit Edition
PTC MKS Toolkit for Enterprise Developers
PTC MKS Toolkit for Enterprise Developers 64-Bit Edition

join
join two sorted, textual relational databases

Command