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