# join

## 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 **-a** 1 and **-a** 2 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 **-v** 1 and **-v** 2 are specified, all
                     unpairable lines shall be output.
    
           **-1** _field_  Join on the _field_ th field of file 1. Fields are decimal
                     integers starting with 1.
    
           **-2** _field_  Join on the _field_ th 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.
    


* * *