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.1 (Berkeley) 06/06/93 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.It Fl e Ar string 62Replace empty output fields with 63.Ar string . 64.It Fl o Ar list 65The 66.Fl o 67option specifies the fields that will be output from each file for 68each line with matching join fields. 69Each element of 70.Ar list 71has the form 72.Ql file_number.field , 73where 74.Ar file_number 75is a file number and 76.Ar field 77is a field number. 78The elements of list must be either comma (``,'') or whitespace separated. 79(The latter requires quoting to protect it from the shell, or, a simpler 80approach is to use multiple 81.Fl o 82options.) 83.It Fl t Ar char 84Use character 85.Ar char 86as a field delimiter for both input and output. 87Every occurrence of 88.Ar char 89in a line is significant. 90.It Fl v Ar file_number 91Do not display the default output, but display a line for each unpairable 92line in file 93.Ar file_number . 94The options 95.Fl v Ar 1 96and 97.Fl v Ar 2 98may be specified at the same time. 99.It Fl 1 Ar field 100Join on the 101.Ar field Ns 'th 102field of file 1. 103.It Fl 2 Ar field 104Join on the 105.Ar field Ns 'th 106field of file 2. 107.El 108.Pp 109When the default field delimiter characters are used, the files to be joined 110should be ordered in the collating sequence of 111.Xr sort 1 , 112using the 113.Fl b 114option, on the fields on which they are to be joined, otherwise 115.Nm join 116may not report all field matches. 117When the field delimiter characters are specified by the 118.Fl t 119option, the collating sequence should be the same as 120.Xr sort 121without the 122.Fl b 123option. 124.Pp 125If one of the arguments 126.Ar file1 127or 128.Ar file2 129is ``-'', the standard input is used. 130.Pp 131The 132.Nm join 133utility exits 0 on success, and >0 if an error occurs. 134.Sh COMPATIBILITY 135For compatibility with historic versions of 136.Nm join , 137the following options are available: 138.Bl -tag -width Fl 139.It Fl a 140In addition to the default output, produce a line for each unpairable line 141in both file 1 and file 2. 142.It Fl j1 Ar field 143Join on the 144.Ar field Ns 'th 145field of file 1. 146.It Fl j2 Ar field 147Join on the 148.Ar field Ns 'th 149field of file 2. 150.It Fl j Ar field 151Join on the 152.Ar field Ns 'th 153field of both file 1 and file 2. 154.It Fl o Ar list ... 155Historical implementations of 156.Nm join 157permitted multiple arguments to the 158.Fl o 159option. 160These arguments were of the form ``file_number.field_number'' as described 161for the current 162.Fl o 163option. 164This has obvious difficulties in the presence of files named ``1.2''. 165.El 166.Pp 167These options are available only so historic shellscripts don't require 168modification and should not be used. 169.Sh STANDARDS 170The 171.Nm join 172command is expected to be 173.St -p1003.2 174compatible. 175.Sh SEE ALSO 176.Xr awk 1 , 177.Xr comm 1 , 178.Xr paste 1 , 179.Xr sort 1 , 180.Xr uniq 1 181