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.3 (Berkeley) 4/28/95 36.\" 37.Dd April 28, 1995 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(The argument to 88.Fl a 89must not be preceded by a space; see the 90.Sx COMPATIBILITY 91section.) 92.It Fl e Ar string 93Replace empty output fields with 94.Ar string . 95.It Fl o Ar list 96The 97.Fl o 98option specifies the fields that will be output from each file for 99each line with matching join fields. 100Each element of 101.Ar list 102has the form 103.Ql file_number.field , 104where 105.Ar file_number 106is a file number and 107.Ar field 108is a field number. 109The elements of list must be either comma (``,'') or whitespace separated. 110(The latter requires quoting to protect it from the shell, or, a simpler 111approach is to use multiple 112.Fl o 113options.) 114.It Fl t Ar char 115Use character 116.Ar char 117as a field delimiter for both input and output. 118Every occurrence of 119.Ar char 120in a line is significant. 121.It Fl v Ar file_number 122Do not display the default output, but display a line for each unpairable 123line in file 124.Ar file_number . 125The options 126.Fl v Ar 1 127and 128.Fl v Ar 2 129may be specified at the same time. 130.It Fl 1 Ar field 131Join on the 132.Ar field Ns 'th 133field of file 1. 134.It Fl 2 Ar field 135Join on the 136.Ar field Ns 'th 137field of file 2. 138.El 139.Pp 140When the default field delimiter characters are used, the files to be joined 141should be ordered in the collating sequence of 142.Xr sort 1 , 143using the 144.Fl b 145option, on the fields on which they are to be joined, otherwise 146.Nm join 147may not report all field matches. 148When the field delimiter characters are specified by the 149.Fl t 150option, the collating sequence should be the same as 151.Xr sort 152without the 153.Fl b 154option. 155.Pp 156If one of the arguments 157.Ar file1 158or 159.Ar file2 160is ``-'', the standard input is used. 161.Pp 162The 163.Nm join 164utility exits 0 on success, and >0 if an error occurs. 165.Sh COMPATIBILITY 166For compatibility with historic versions of 167.Nm join , 168the following options are available: 169.Bl -tag -width Fl 170.It Fl a 171In addition to the default output, produce a line for each unpairable line 172in both file 1 and file 2. 173(To distinguish between this and 174.Fl a Ar file_number , 175.Nm join 176currently requires that the latter not include any white space.) 177.It Fl j1 Ar field 178Join on the 179.Ar field Ns 'th 180field of file 1. 181.It Fl j2 Ar field 182Join on the 183.Ar field Ns 'th 184field of file 2. 185.It Fl j Ar field 186Join on the 187.Ar field Ns 'th 188field of both file 1 and file 2. 189.It Fl o Ar list ... 190Historical implementations of 191.Nm join 192permitted multiple arguments to the 193.Fl o 194option. 195These arguments were of the form ``file_number.field_number'' as described 196for the current 197.Fl o 198option. 199This has obvious difficulties in the presence of files named ``1.2''. 200.El 201.Pp 202These options are available only so historic shellscripts don't require 203modification and should not be used. 204.Sh STANDARDS 205The 206.Nm join 207command is expected to be 208.St -p1003.2 209compatible. 210.Sh SEE ALSO 211.Xr awk 1 , 212.Xr comm 1 , 213.Xr paste 1 , 214.Xr sort 1 , 215.Xr uniq 1 216