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