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