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.\" $FreeBSD$ 37.\" 38.Dd April 28, 1995 39.Dt JOIN 1 40.Os 41.Sh NAME 42.Nm join 43.Nd relational database operator 44.Sh SYNOPSIS 45.Nm join 46.Oo 47.Fl a Ar file_number | Fl v Ar file_number 48.Oc 49.Op Fl e Ar string 50.Op Fl j Ar file_number field 51.Op Fl o Ar list 52.Bk -words 53.Ek 54.Op Fl t Ar char 55.Op Fl \&1 Ar field 56.Op Fl \&2 Ar field 57.Ar file1 58.Ar file2 59.Sh DESCRIPTION 60The join utility performs an ``equality join'' on the specified files 61and writes the result to the standard output. 62The ``join field'' is the field in each file by which the files are compared. 63The first field in each line is used by default. 64There is one line in the output for each pair of lines in 65.Ar file1 66and 67.Ar file2 68which have identical join fields. 69Each output line consists of the join field, the remaining fields from 70.Ar file1 71and then the remaining fields from 72.Ar file2 . 73.Pp 74The default field separators are tab and space characters. 75In this case, multiple tabs and spaces count as a single field separator, 76and leading tabs and spaces are ignored. 77The default output field separator is a single space character. 78.Pp 79Many of the options use file and field numbers. 80Both file numbers and field numbers are 1 based, i.e. the first file on 81the command line is file number 1 and the first field is field number 1. 82The following options are available: 83.Bl -tag -width Fl 84.It Fl a Ar file_number 85In addition to the default output, produce a line for each unpairable 86line in file 87.Ar file_number . 88(The argument to 89.Fl a 90must not be preceded by a space; see the 91.Sx COMPATIBILITY 92section.) 93.It Fl e Ar string 94Replace empty output fields with 95.Ar string . 96.It Fl o Ar list 97The 98.Fl o 99option specifies the fields that will be output from each file for 100each line with matching join fields. 101Each element of 102.Ar list 103has the form 104.Ql file_number.field , 105where 106.Ar file_number 107is a file number and 108.Ar field 109is a field number. 110The elements of list must be either comma (``,'') or whitespace separated. 111(The latter requires quoting to protect it from the shell, or, a simpler 112approach is to use multiple 113.Fl o 114options.) 115.It Fl t Ar char 116Use character 117.Ar char 118as a field delimiter for both input and output. 119Every occurrence of 120.Ar char 121in a line is significant. 122.It Fl v Ar file_number 123Do not display the default output, but display a line for each unpairable 124line in file 125.Ar file_number . 126The options 127.Fl v Ar 1 128and 129.Fl v Ar 2 130may be specified at the same time. 131.It Fl 1 Ar field 132Join on the 133.Ar field Ns 'th 134field of file 1. 135.It Fl 2 Ar field 136Join on the 137.Ar field Ns 'th 138field of file 2. 139.El 140.Pp 141When the default field delimiter characters are used, the files to be joined 142should be ordered in the collating sequence of 143.Xr sort 1 , 144using the 145.Fl b 146option, on the fields on which they are to be joined, otherwise 147.Nm join 148may not report all field matches. 149When the field delimiter characters are specified by the 150.Fl t 151option, the collating sequence should be the same as 152.Xr sort 153without the 154.Fl b 155option. 156.Pp 157If one of the arguments 158.Ar file1 159or 160.Ar file2 161is ``-'', the standard input is used. 162.Pp 163The 164.Nm join 165utility exits 0 on success, and >0 if an error occurs. 166.Sh COMPATIBILITY 167For compatibility with historic versions of 168.Nm join , 169the following options are available: 170.Bl -tag -width Fl 171.It Fl a 172In addition to the default output, produce a line for each unpairable line 173in both file 1 and file 2. 174(To distinguish between this and 175.Fl a Ar file_number , 176.Nm join 177currently requires that the latter not include any white space.) 178.It Fl j1 Ar field 179Join on the 180.Ar field Ns 'th 181field of file 1. 182.It Fl j2 Ar field 183Join on the 184.Ar field Ns 'th 185field of file 2. 186.It Fl j Ar field 187Join on the 188.Ar field Ns 'th 189field of both file 1 and file 2. 190.It Fl o Ar list ... 191Historical implementations of 192.Nm join 193permitted multiple arguments to the 194.Fl o 195option. 196These arguments were of the form ``file_number.field_number'' as described 197for the current 198.Fl o 199option. 200This has obvious difficulties in the presence of files named ``1.2''. 201.El 202.Pp 203These options are available only so historic shellscripts don't require 204modification and should not be used. 205.Sh STANDARDS 206The 207.Nm join 208command is expected to be 209.St -p1003.2 210compatible. 211.Sh SEE ALSO 212.Xr awk 1 , 213.Xr comm 1 , 214.Xr paste 1 , 215.Xr sort 1 , 216.Xr uniq 1 217