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