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