Provided by: manpages-posix_2017a-2_all 
PROLOG
This manual page is part of the POSIX Programmer's Manual. The Linux implementation of
this interface may differ (consult the corresponding Linux manual page for details of
Linux behavior), or the interface may not be implemented on Linux.
NAME
join — relational database operator
SYNOPSIS
join [-a file_number|-v file_number] [-e string] [-o list] [-t char]
[-1 field] [-2 field] file1 file2
DESCRIPTION
The join utility shall perform an equality join on the files file1 and file2. The joined
files shall be written to the standard output.
The join field is a field in each file on which the files are compared. The join utility
shall write one line in the output for each pair of lines in file1 and file2 that have
join fields that collate equally. The output line by default shall consist of the join
field, then the remaining fields from file1, then the remaining fields from file2. This
format can be changed by using the -o option (see below). The -a option can be used to add
unmatched lines to the output. The -v option can be used to output only unmatched lines.
The files file1 and file2 shall be ordered in the collating sequence of sort -b on the
fields on which they shall be joined, by default the first in each line. All selected
output shall be written in the same collating sequence.
The default input field separators shall be <blank> characters. In this case, multiple
separators shall count as one field separator, and leading separators shall be ignored.
The default output field separator shall be a <space>.
The field separator and collating sequence can be changed by using the -t option (see
below).
If the same key appears more than once in either file, all combinations of the set of
remaining fields in file1 and the set of remaining fields in file2 are output in the order
of the lines encountered.
If the input files are not in the appropriate collating sequence, the results are
unspecified.
OPTIONS
The join utility shall conform to the Base Definitions volume of POSIX.1‐2017, Section
12.2, Utility Syntax Guidelines.
The following options shall be supported:
-a file_number
Produce a line for each unpairable line in file file_number, where file_number
is 1 or 2, in addition to the default output. If both -a1 and -a2 are specified,
all unpairable lines shall be output.
-e string Replace empty output fields in the list selected by -o with the string string.
-o list Construct the output line to comprise the fields specified in list, each element
of which shall have one of the following two forms:
1. file_number.field, where file_number is a file number and field is a decimal
integer field number
2. 0 (zero), representing the join field
The elements of list shall be either <comma>-separated or <blank>-separated, as
specified in Guideline 8 of the Base Definitions volume of POSIX.1‐2017, Section
12.2, Utility Syntax Guidelines. The fields specified by list shall be written
for all selected output lines. Fields selected by list that do not appear in the
input shall be treated as empty output fields. (See the -e option.) Only
specifically requested fields shall be written. The application shall ensure
that list is a single command line argument.
-t char Use character char as a separator, for both input and output. Every appearance
of char in a line shall be significant. When this option is specified, the
collating sequence shall be the same as sort without the -b option.
-v file_number
Instead of the default output, produce a line only for each unpairable line in
file_number, where file_number is 1 or 2. If both -v1 and -v2 are specified, all
unpairable lines shall be output.
-1 field Join on the fieldth field of file 1. Fields are decimal integers starting with
1.
-2 field Join on the fieldth field of file 2. Fields are decimal integers starting with
1.
OPERANDS
The following operands shall be supported:
file1, file2
A pathname of a file to be joined. If either of the file1 or file2 operands is
'-', the standard input shall be used in its place.
STDIN
The standard input shall be used only if the file1 or file2 operand is '-'. See the INPUT
FILES section.
INPUT FILES
The input files shall be text files.
ENVIRONMENT VARIABLES
The following environment variables shall affect the execution of join:
LANG Provide a default value for the internationalization variables that are unset or
null. (See the Base Definitions volume of POSIX.1‐2017, Section 8.2,
Internationalization Variables for the precedence of internationalization
variables used to determine the values of locale categories.)
LC_ALL If set to a non-empty string value, override the values of all the other
internationalization variables.
LC_COLLATE
Determine the locale of the collating sequence join expects to have been used
when the input files were sorted.
LC_CTYPE Determine the locale for the interpretation of sequences of bytes of text data
as characters (for example, single-byte as opposed to multi-byte characters in
arguments and input files).
LC_MESSAGES
Determine the locale that should be used to affect the format and contents of
diagnostic messages written to standard error.
NLSPATH Determine the location of message catalogs for the processing of LC_MESSAGES.
ASYNCHRONOUS EVENTS
Default.
STDOUT
The join utility output shall be a concatenation of selected character fields. When the
-o option is not specified, the output shall be:
"%s%s%s\n", <join field>, <other file1 fields>,
<other file2 fields>
If the join field is not the first field in a file, the <other file fields> for that file
shall be:
<fields preceding join field>, <fields following join field>
When the -o option is specified, the output format shall be:
"%s\n", <concatenation of fields>
where the concatenation of fields is described by the -o option, above.
For either format, each field (except the last) shall be written with its trailing
separator character. If the separator is the default (<blank> characters), a single
<space> shall be written after each field (except the last).
STDERR
The standard error shall be used only for diagnostic messages.
OUTPUT FILES
None.
EXTENDED DESCRIPTION
None.
EXIT STATUS
The following exit values shall be returned:
0 All input files were output successfully.
>0 An error occurred.
CONSEQUENCES OF ERRORS
Default.
The following sections are informative.
APPLICATION USAGE
Pathnames consisting of numeric digits or of the form string.string should not be
specified directly following the -o list.
If the collating sequence of the current locale does not have a total ordering of all
characters (see the Base Definitions volume of POSIX.1‐2017, Section 7.3.2, LC_COLLATE),
join treats fields that collate equally but are not identical as being the same. If this
behavior is not desired, it can be avoided by forcing the use of the POSIX locale
(although this means re-sorting the input files into the POSIX locale collating sequence.)
When using join to process pathnames, it is recommended that LC_ALL, or at least LC_CTYPE
and LC_COLLATE, are set to POSIX or C in the environment, since pathnames can contain byte
sequences that do not form valid characters in some locales, in which case the utility's
behavior would be undefined. In the POSIX locale each byte is a valid single-byte
character, and therefore this problem is avoided.
EXAMPLES
The -o 0 field essentially selects the union of the join fields. For example, given file
phone:
!Name Phone Number
Don +1 123-456-7890
Hal +1 234-567-8901
Yasushi +2 345-678-9012
and file fax:
!Name Fax Number
Don +1 123-456-7899
Keith +1 456-789-0122
Yasushi +2 345-678-9011
(where the large expanses of white space are meant to each represent a single <tab>), the
command:
join -t "<tab>" -a 1 -a 2 -e '(unknown)' -o 0,1.2,2.2 phone fax
(where <tab> is a literal <tab> character) would produce:
!Name Phone Number Fax Number
Don +1 123-456-7890 +1 123-456-7899
Hal +1 234-567-8901 (unknown)
Keith (unknown) +1 456-789-0122
Yasushi +2 345-678-9012 +2 345-678-9011
Multiple instances of the same key will produce combinatorial results. The following:
fa:
a x
a y
a z
fb:
a p
will produce:
a x p
a y p
a z p
And the following:
fa:
a b c
a d e
fb:
a w x
a y z
a o p
will produce:
a b c w x
a b c y z
a b c o p
a d e w x
a d e y z
a d e o p
RATIONALE
The -e option is only effective when used with -o because, unless specific fields are
identified using -o, join is not aware of what fields might be empty. The exception to
this is the join field, but identifying an empty join field with the -e string is not
historical practice and some scripts might break if this were changed.
The 0 field in the -o list was adopted from the Tenth Edition version of join to satisfy
international objections that the join in the base documents for IEEE Std 1003.2‐1992 did
not support the ``full join'' or ``outer join'' described in relational database
literature. Although it has been possible to include a join field in the output (by
default, or by field number using -o), the join field could not be included for an
unpaired line selected by -a. The -o 0 field essentially selects the union of the join
fields.
This sort of outer join was not possible with the join commands in the base documents for
IEEE Std 1003.2‐1992. The -o 0 field was chosen because it is an upwards-compatible change
for applications. An alternative was considered: have the join field represent the union
of the fields in the files (where they are identical for matched lines, and one or both
are null for unmatched lines). This was not adopted because it would break some historical
applications.
The ability to specify file2 as - is not historical practice; it was added for
completeness.
The -v option is not historical practice, but was considered necessary because it
permitted the writing of only those lines that do not match on the join field, as opposed
to the -a option, which prints both lines that do and do not match. This additional
facility is parallel with the -v option of grep.
Some historical implementations have been encountered where a blank line in one of the
input files was considered to be the end of the file; the description in this volume of
POSIX.1‐2017 does not cite this as an allowable case.
Earlier versions of this standard allowed -j, -j1, -j2 options, and a form of the -o
option that allowed the list option-argument to be multiple arguments. These forms are no
longer specified by POSIX.1‐2008 but may be present in some implementations.
FUTURE DIRECTIONS
None.
SEE ALSO
awk, comm, sort, uniq
The Base Definitions volume of POSIX.1‐2017, Section 7.3.2, LC_COLLATE, Chapter 8,
Environment Variables, Section 12.2, Utility Syntax Guidelines
COPYRIGHT
Portions of this text are reprinted and reproduced in electronic form from IEEE Std
1003.1-2017, Standard for Information Technology -- Portable Operating System Interface
(POSIX), The Open Group Base Specifications Issue 7, 2018 Edition, Copyright (C) 2018 by
the Institute of Electrical and Electronics Engineers, Inc and The Open Group. In the
event of any discrepancy between this version and the original IEEE and The Open Group
Standard, the original IEEE and The Open Group Standard is the referee document. The
original Standard can be obtained online at http://www.opengroup.org/unix/online.html .
Any typographical or formatting errors that appear in this page are most likely to have
been introduced during the conversion of the source files to man page format. To report
such errors, see https://www.kernel.org/doc/man-pages/reporting_bugs.html .