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 July 5, 2004 39.Dt JOIN 1 40.Os 41.Sh NAME 42.Nm join 43.Nd relational database operator 44.Sh SYNOPSIS 45.Nm 46.Oo 47.Fl a Ar file_number | Fl v Ar file_number 48.Oc 49.Op Fl e Ar string 50.Op Fl o Ar list 51.Op Fl t Ar char 52.Op Fl 1 Ar field 53.Op Fl 2 Ar field 54.Ar file1 55.Ar file2 56.Sh DESCRIPTION 57The 58.Nm 59utility performs an 60.Dq equality join 61on the specified files 62and writes the result to the standard output. 63The 64.Dq join field 65is 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 indent 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.It Fl e Ar string 92Replace empty output fields with 93.Ar string . 94.It Fl o Ar list 95The 96.Fl o 97option specifies the fields that will be output from each file for 98each line with matching join fields. 99Each element of 100.Ar list 101has either the form 102.Ar file_number . Ns Ar field , 103where 104.Ar file_number 105is a file number and 106.Ar field 107is a field number, or the form 108.Ql 0 109.Pq zero , 110representing the join field. 111The elements of list must be either comma 112.Pq Ql \&, 113or 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 Cm 1 131and 132.Fl v Cm 2 133may be specified at the same time. 134.It Fl 1 Ar field 135Join on the 136.Ar field Ns 'th 137field of 138.Ar file1 . 139.It Fl 2 Ar field 140Join on the 141.Ar field Ns 'th 142field of 143.Ar file2 . 144.El 145.Pp 146When the default field delimiter characters are used, the files to be joined 147should be ordered in the collating sequence of 148.Xr sort 1 , 149using the 150.Fl b 151option, on the fields on which they are to be joined, otherwise 152.Nm 153may not report all field matches. 154When the field delimiter characters are specified by the 155.Fl t 156option, the collating sequence should be the same as 157.Xr sort 1 158without the 159.Fl b 160option. 161.Pp 162If one of the arguments 163.Ar file1 164or 165.Ar file2 166is 167.Sq Fl , 168the standard input is used. 169.Sh EXIT STATUS 170.Ex -std 171.Sh COMPATIBILITY 172For compatibility with historic versions of 173.Nm , 174the following options are available: 175.Bl -tag -width indent 176.It Fl a 177In addition to the default output, produce a line for each unpairable line 178in both 179.Ar file1 180and 181.Ar file2 . 182.It Fl j1 Ar field 183Join on the 184.Ar field Ns 'th 185field of 186.Ar file1 . 187.It Fl j2 Ar field 188Join on the 189.Ar field Ns 'th 190field of 191.Ar file2 . 192.It Fl j Ar field 193Join on the 194.Ar field Ns 'th 195field of both 196.Ar file1 197and 198.Ar file2 . 199.It Fl o Ar list ... 200Historical implementations of 201.Nm 202permitted multiple arguments to the 203.Fl o 204option. 205These arguments were of the form 206.Ar file_number . Ns Ar field_number 207as described 208for the current 209.Fl o 210option. 211This has obvious difficulties in the presence of files named 212.Pa 1.2 . 213.El 214.Pp 215These options are available only so historic shell scripts do not require 216modification and should not be used. 217.Sh SEE ALSO 218.Xr awk 1 , 219.Xr comm 1 , 220.Xr paste 1 , 221.Xr sort 1 , 222.Xr uniq 1 223.Sh STANDARDS 224The 225.Nm 226command conforms to 227.St -p1003.1-2001 . 228