JOIN(1POSIX)

📦 version 2017a, in debian bookworm, debian trixie, ubuntu jammy, ubuntu noble, ubuntu resolute, ubuntu stonking

💖 join(1POSIX) — relational database operator 💖

okay bestie, sit down, hydrate, because we're about to talk about join and it's honestly so underrated. This is the POSIX Programmer's Manual page for join from the POSIX Programmer's Manual — iykyk.

✨ PROLOG ✨

real talk first, no cap: this manual page is part of the POSIX Programmer's Manual. The Linux implementation of this interface may differ — so go check the corresponding Linux manual page for the deets on Linux behavior. Or, plot twist, the interface might not even be implemented on Linux at all. 💀

🌸 NAME 🌸

join — relational database operator slay 💅

🎀 SYNOPSIS 🎀

join [-a file_number|-v file_number] [-e string] [-o list] [-t char]
    [-1 field] [-2 field] file1 file2

🦋 DESCRIPTION 🦋

So here's the vibe: the join utility shall perform an equality join on the files file1 and file2. The joined files shall be written to the standard output. simple as that.

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. By default, that output line shall consist of the join field, then the remaining fields from file1, then the remaining fields from file2. You can change this format with the -o option (peep below 👀). The -a option can add unmatched lines to the output, and the -v option can output only unmatched lines.

okay this part matters: 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. so yes, you have to sort first. it's giving "do your homework before the group project." 😭

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 (again, 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. (yes it gets combinatorial, we have examples later, breathe.)

⚠️ heads up: if the input files are not in the appropriate collating sequence, the results are unspecified. no cap. don't test it.

✨ OPTIONS ✨

The join utility shall conform to the Base Definitions volume of POSIX.1‐2017, Section 12.2, Utility Syntax Guidelines. (yes we cite our sources, we're classy like that.)

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. — fr fr, quote it, or the shell will eat it and you'll be sad.

  • -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 *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. that's it. that's the rule.

🧚 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. (this one is lowkey the main character of this section.)
  • 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. (yes. that's the whole section. iconic, honestly.)

💖 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. (so if you see stuff there, something's up bestie.)

🎀 OUTPUT FILES 🎀

None.

🦋 EXTENDED DESCRIPTION 🦋

None.

🔥 EXIT STATUS 🔥

The following exit values shall be returned:

  • 0 — All input files were output successfully. slay! 🎉
  • >0 — An error occurred. 💀

🌸 CONSEQUENCES OF ERRORS 🌸

Default.

The following sections are informative. (translation: the fun part starts here, these are the vibes-and-advice sections.)

👑 APPLICATION USAGE 👑

Pathnames consisting of numeric digits or of the form string.string should not be specified directly following the -o list. (because join will read them as field selectors, and then you're gonna have a bad time.)

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. no cap, this is the move. 🫶

⭐ 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

see? combinatorial. told you. 😭

okay bestie that's part 1 — more coming, don't go anywhere 💖

🧠 RATIONALE 🧠

okay so bestie, why is -e like that? -e only does its thing when you also use -o, because unless you name specific fields with -o, join literally doesn't know which fields could be empty. The one exception is the join field itself, but using the -e string to identify an empty join field is not historical practice, and some scripts might break if that changed. so yeah, don't expect it. 💅

The 0 field in the -o list? That 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. Even though you could already 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 basically selects the union of the join fields. union slay 💅

This sort of outer join wasn't possible with the join commands in the base documents for IEEE Std 1003.2‐1992. The -o 0 field was chosen because it's an upwards-compatible change for applications. They considered an alternative: have the join field represent the union of the fields in the files (where they're identical for matched lines, and one or both are null for unmatched lines). That was not adopted because it would break some historical applications. rip alternative 🙈

Being able to specify file2 as - is not historical practice; it was added for completeness.

The -v option is not historical practice either, but it was considered necessary because it let you write 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 extra facility is parallel with the -v option of grep. bestie behavior.

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. so no, that's not a vibe we're claiming.

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. so if you see them, it's giving legacy, not current spec.

🔮 FUTURE DIRECTIONS 🔮

None. literally none. the future is a blank line, bestie. 🦋

👀 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.

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 .

and that's the whole page, bestie. it's from IEEE/The Open Group, dated 2017, and it's JOIN(1POSIX) — go forth and join responsibly. 💖✨

📦 where this page comes from