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