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.Bk -words 52.Ek 53.Op Fl t Ar char 54.Op Fl 1 Ar field 55.Op Fl 2 Ar field 56.Ar file1 57.Ar file2 58.Sh DESCRIPTION 59The 60.Nm 61utility performs an 62.Dq equality join 63on the specified files 64and writes the result to the standard output. 65The 66.Dq join field 67is the field in each file by which the files are compared. 68The first field in each line is used by default. 69There is one line in the output for each pair of lines in 70.Ar file1 71and 72.Ar file2 73which have identical join fields. 74Each output line consists of the join field, the remaining fields from 75.Ar file1 76and then the remaining fields from 77.Ar file2 . 78.Pp 79The default field separators are tab and space characters. 80In this case, multiple tabs and spaces count as a single field separator, 81and leading tabs and spaces are ignored. 82The default output field separator is a single space character. 83.Pp 84Many of the options use file and field numbers. 85Both file numbers and field numbers are 1 based, i.e., the first file on 86the command line is file number 1 and the first field is field number 1. 87The following options are available: 88.Bl -tag -width indent 89.It Fl a Ar file_number 90In addition to the default output, produce a line for each unpairable 91line in file 92.Ar file_number . 93.It Fl e Ar string 94Replace empty output fields with 95.Ar string . 96.It Fl o Ar list 97The 98.Fl o 99option specifies the fields that will be output from each file for 100each line with matching join fields. 101Each element of 102.Ar list 103has either the form 104.Ar file_number . Ns Ar field , 105where 106.Ar file_number 107is a file number and 108.Ar field 109is a field number, or the form 110.Ql 0 111.Pq zero , 112representing the join field. 113The elements of list must be either comma 114.Pq Ql \&, 115or whitespace separated. 116(The latter requires quoting to protect it from the shell, or, a simpler 117approach is to use multiple 118.Fl o 119options.) 120.It Fl t Ar char 121Use character 122.Ar char 123as a field delimiter for both input and output. 124Every occurrence of 125.Ar char 126in a line is significant. 127.It Fl v Ar file_number 128Do not display the default output, but display a line for each unpairable 129line in file 130.Ar file_number . 131The options 132.Fl v Cm 1 133and 134.Fl v Cm 2 135may be specified at the same time. 136.It Fl 1 Ar field 137Join on the 138.Ar field Ns 'th 139field of 140.Ar file1 . 141.It Fl 2 Ar field 142Join on the 143.Ar field Ns 'th 144field of 145.Ar file2 . 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 169.Sq Fl , 170the standard input is used. 171.Sh EXIT STATUS 172.Ex -std 173.Sh COMPATIBILITY 174For compatibility with historic versions of 175.Nm , 176the following options are available: 177.Bl -tag -width indent 178.It Fl a 179In addition to the default output, produce a line for each unpairable line 180in both 181.Ar file1 182and 183.Ar file2 . 184.It Fl j1 Ar field 185Join on the 186.Ar field Ns 'th 187field of 188.Ar file1 . 189.It Fl j2 Ar field 190Join on the 191.Ar field Ns 'th 192field of 193.Ar file2 . 194.It Fl j Ar field 195Join on the 196.Ar field Ns 'th 197field of both 198.Ar file1 199and 200.Ar file2 . 201.It Fl o Ar list ... 202Historical implementations of 203.Nm 204permitted multiple arguments to the 205.Fl o 206option. 207These arguments were of the form 208.Ar file_number . Ns Ar field_number 209as described 210for the current 211.Fl o 212option. 213This has obvious difficulties in the presence of files named 214.Pa 1.2 . 215.El 216.Pp 217These options are available only so historic shell scripts do not require 218modification and should not be used. 219.Sh SEE ALSO 220.Xr awk 1 , 221.Xr comm 1 , 222.Xr paste 1 , 223.Xr sort 1 , 224.Xr uniq 1 225.Sh STANDARDS 226The 227.Nm 228command conforms to 229.St -p1003.1-2001 . 230