1.\" $NetBSD: join.1,v 1.15 2017/07/04 07:01:53 wiz Exp $ 2.\" 3.\" Copyright (c) 1990, 1993 4.\" The Regents of the University of California. All rights reserved. 5.\" 6.\" This code is derived from software contributed to Berkeley by 7.\" the Institute of Electrical and Electronics Engineers, Inc. 8.\" 9.\" Redistribution and use in source and binary forms, with or without 10.\" modification, are permitted provided that the following conditions 11.\" are met: 12.\" 1. Redistributions of source code must retain the above copyright 13.\" notice, this list of conditions and the following disclaimer. 14.\" 2. Redistributions in binary form must reproduce the above copyright 15.\" notice, this list of conditions and the following disclaimer in the 16.\" documentation and/or other materials provided with the distribution. 17.\" 3. Neither the name of the University nor the names of its contributors 18.\" may be used to endorse or promote products derived from this software 19.\" without specific prior written permission. 20.\" 21.\" THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND 22.\" ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 23.\" IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 24.\" ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE 25.\" FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 26.\" DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS 27.\" OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 28.\" HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 29.\" LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY 30.\" OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF 31.\" SUCH DAMAGE. 32.\" 33.\" from: @(#)join.1 8.3 (Berkeley) 4/28/95 34.\" 35.Dd April 28, 1995 36.Dt JOIN 1 37.Os 38.Sh NAME 39.Nm join 40.Nd relational database operator 41.Sh SYNOPSIS 42.Nm 43.Op Fl a Ar file_number | Fl v Ar file_number 44.Op Fl e Ar string 45.Op Fl j Ar file_number field 46.Op Fl o Ar list 47.Op Fl t Ar char 48.Op Fl \&1 Ar field 49.Op Fl \&2 Ar field 50.Ar file1 file2 51.Sh DESCRIPTION 52The join utility performs an ``equality join'' on the specified files 53and writes the result to the standard output. 54The ``join field'' is the field in each file by which the files are compared. 55The first field in each line is used by default. 56There is one line in the output for each pair of lines in 57.Ar file1 58and 59.Ar file2 60which have identical join fields. 61Each output line consists of the join field, the remaining fields from 62.Ar file1 63and then the remaining fields from 64.Ar file2 . 65.Pp 66The default field separators are tab and space characters. 67In this case, multiple tabs and spaces count as a single field separator, 68and leading tabs and spaces are ignored. 69The default output field separator is a single space character. 70.Pp 71Many of the options use file and field numbers. 72Both file numbers and field numbers are 1 based, i.e. the first file on 73the command line is file number 1 and the first field is field number 1. 74The following options are available: 75.Bl -tag -width Fl 76.It Fl a Ar file_number 77In addition to the default output, produce a line for each unpairable 78line in file 79.Ar file_number . 80(The argument to 81.Fl a 82must not be preceded by a space; see the 83.Sx COMPATIBILITY 84section.) 85.It Fl e Ar string 86Replace empty output fields with 87.Ar string . 88.It Fl o Ar list 89The 90.Fl o 91option specifies the fields that will be output from each file for 92each line with matching join fields. 93Each element of 94.Ar list 95has the form 96.Ql file_number.field , 97where 98.Ar file_number 99is a file number and 100.Ar field 101is a field number. 102The elements of list must be either comma (``,'') or whitespace separated. 103(The latter requires quoting to protect it from the shell, or, a simpler 104approach is to use multiple 105.Fl o 106options.) 107.It Fl t Ar char 108Use character 109.Ar char 110as a field delimiter for both input and output. 111Every occurrence of 112.Ar char 113in a line is significant. 114.It Fl v Ar file_number 115Do not display the default output, but display a line for each unpairable 116line in file 117.Ar file_number . 118The options 119.Fl v Ar 1 120and 121.Fl v Ar 2 122may be specified at the same time. 123.It Fl 1 Ar field 124Join on the 125.Ar field Ns 'th 126field of file 1. 127.It Fl 2 Ar field 128Join on the 129.Ar field Ns 'th 130field of file 2. 131.El 132.Pp 133When the default field delimiter characters are used, the files to be joined 134should be ordered in the collating sequence of 135.Xr sort 1 , 136using the 137.Fl b 138option, on the fields on which they are to be joined, otherwise 139.Nm 140may not report all field matches. 141When the field delimiter characters are specified by the 142.Fl t 143option, the collating sequence should be the same as 144.Xr sort 1 145without the 146.Fl b 147option. 148.Pp 149If one of the arguments 150.Ar file1 151or 152.Ar file2 153is ``-'', the standard input is used. 154.Sh EXIT STATUS 155.Ex -std join 156.Sh COMPATIBILITY 157For compatibility with historic versions of 158.Nm , 159the following options are available: 160.Bl -tag -width Fl 161.It Fl a 162In addition to the default output, produce a line for each unpairable line 163in both file 1 and file 2. 164(To distinguish between this and 165.Fl a Ar file_number , 166.Nm 167currently requires that the latter not include any white space.) 168.It Fl j1 Ar field 169Join on the 170.Ar field Ns 'th 171field of file 1. 172.It Fl j2 Ar field 173Join on the 174.Ar field Ns 'th 175field of file 2. 176.It Fl j Ar field 177Join on the 178.Ar field Ns 'th 179field of both file 1 and file 2. 180.It Fl o Ar list ... 181Historical implementations of 182.Nm 183permitted multiple arguments to the 184.Fl o 185option. 186These arguments were of the form ``file_number.field_number'' as described 187for the current 188.Fl o 189option. 190This has obvious difficulties in the presence of files named ``1.2''. 191.El 192.Pp 193These options are available only so historic shell scripts don't require 194modification and should not be used. 195.Sh SEE ALSO 196.Xr awk 1 , 197.Xr comm 1 , 198.Xr paste 1 , 199.Xr sort 1 , 200.Xr uniq 1 201.Sh STANDARDS 202The 203.Nm 204command is expected to be 205.St -p1003.2 206compatible. 207