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. Neither the name of the University nor the names of its contributors 16.\" may be used to endorse or promote products derived from this software 17.\" without specific prior written permission. 18.\" 19.\" THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND 20.\" ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 21.\" IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 22.\" ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE 23.\" FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 24.\" DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS 25.\" OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 26.\" HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 27.\" LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY 28.\" OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF 29.\" SUCH DAMAGE. 30.\" 31.\" @(#)join.1 8.3 (Berkeley) 4/28/95 32.\" $FreeBSD: head/usr.bin/join/join.1 235597 2012-05-18 03:30:50Z gjb $ 33.\" 34.Dd January 12, 2016 35.Dt JOIN 1 36.Os 37.Sh NAME 38.Nm join 39.Nd relational database operator 40.Sh SYNOPSIS 41.Nm 42.Oo 43.Fl a Ar file_number | Fl v Ar file_number 44.Oc 45.Op Fl e Ar string 46.Op Fl j Ar fileno field 47.Op Fl o Ar list 48.Op Fl t Ar char 49.Op Fl 1 Ar field 50.Op Fl 2 Ar field 51.Ar file1 52.Ar file2 53.Sh DESCRIPTION 54The 55.Nm 56utility performs an 57.Dq equality join 58on the specified files 59and writes the result to the standard output. 60The 61.Dq join field 62is the field in each file by which the files are compared. 63The first field in each line is used by default. 64There is one line in the output for each pair of lines in 65.Ar file1 66and 67.Ar file2 68which have identical join fields. 69Each output line consists of the join field, the remaining fields from 70.Ar file1 71and then the remaining fields from 72.Ar file2 . 73.Pp 74The default field separators are tab and space characters. 75In this case, multiple tabs and spaces count as a single field separator, 76and leading tabs and spaces are ignored. 77The default output field separator is a single space character. 78.Pp 79Many of the options use file and field numbers. 80Both file numbers and field numbers are 1 based, i.e., the first file on 81the command line is file number 1 and the first field is field number 1. 82The following options are available: 83.Bl -tag -width indent 84.It Fl a Ar file_number 85In addition to the default output, produce a line for each unpairable 86line in file 87.Ar file_number . 88.It Fl e Ar string 89Replace empty output fields with 90.Ar string . 91.It Fl o Ar list 92The 93.Fl o 94option specifies the fields that will be output from each file for 95each line with matching join fields. 96Each element of 97.Ar list 98has either the form 99.Ar file_number . Ns Ar field , 100where 101.Ar file_number 102is a file number and 103.Ar field 104is a field number, or the form 105.Ql 0 106.Pq zero , 107representing the join field. 108The elements of list must be either comma 109.Pq Ql \&, 110or whitespace separated. 111(The latter requires quoting to protect it from the shell, or, a simpler 112approach is to use multiple 113.Fl o 114options.) 115.It Fl t Ar char 116Use character 117.Ar char 118as a field delimiter for both input and output. 119Every occurrence of 120.Ar char 121in a line is significant. 122.It Fl v Ar file_number 123Do not display the default output, but display a line for each unpairable 124line in file 125.Ar file_number . 126The options 127.Fl v Cm 1 128and 129.Fl v Cm 2 130may be specified at the same time. 131.It Fl 1 Ar field 132Join on the 133.Ar field Ns 'th 134field of 135.Ar file1 . 136.It Fl 2 Ar field 137Join on the 138.Ar field Ns 'th 139field of 140.Ar file2 . 141.El 142.Pp 143When the default field delimiter characters are used, the files to be joined 144should be ordered in the collating sequence of 145.Xr sort 1 , 146using the 147.Fl b 148option, on the fields on which they are to be joined, otherwise 149.Nm 150may not report all field matches. 151When the field delimiter characters are specified by the 152.Fl t 153option, the collating sequence should be the same as 154.Xr sort 1 155without the 156.Fl b 157option. 158.Pp 159If one of the arguments 160.Ar file1 161or 162.Ar file2 163is 164.Sq Fl , 165the standard input is used. 166.Sh EXIT STATUS 167.Ex -std 168.Sh COMPATIBILITY 169For compatibility with historic versions of 170.Nm , 171the following options are available: 172.Bl -tag -width indent 173.It Fl a 174In addition to the default output, produce a line for each unpairable line 175in both 176.Ar file1 177and 178.Ar file2 . 179.It Fl j1 Ar field 180Join on the 181.Ar field Ns 'th 182field of 183.Ar file1 . 184.It Fl j2 Ar field 185Join on the 186.Ar field Ns 'th 187field of 188.Ar file2 . 189.It Fl j Ar field 190Join on the 191.Ar field Ns 'th 192field of both 193.Ar file1 194and 195.Ar file2 . 196.It Fl o Ar list ... 197Historical implementations of 198.Nm 199permitted multiple arguments to the 200.Fl o 201option. 202These arguments were of the form 203.Ar file_number . Ns Ar field_number 204as described 205for the current 206.Fl o 207option. 208This has obvious difficulties in the presence of files named 209.Pa 1.2 . 210.El 211.Pp 212These options are available only so historic shell scripts do not require 213modification and should not be used. 214.Sh SEE ALSO 215.Xr awk 1 , 216.Xr comm 1 , 217.Xr paste 1 , 218.Xr sort 1 , 219.Xr uniq 1 220.Sh STANDARDS 221The 222.Nm 223command conforms to 224.St -p1003.1-2001 . 225