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