1JOIN(P) POSIX Programmer's Manual JOIN(P)
2
6 join - relational database operator
7
9 join [-a file_number | -v file_number][-e string][-o list][-t char]
10 [-1 field][-2 field] file1 file2
11
13 The join utility shall perform an equality join on the files file1 and
14 file2. The joined files shall be written to the standard output.
15 The join field is a field in each file on which the files are compared.
17 The join utility shall write one line in the output for each pair of
18 lines in file1 and file2 that have identical join fields. The output
19 line by default shall consist of the join field, then the remaining
20 fields from file1, then the remaining fields from file2. This format
21 can be changed by using the -o option (see below). The -a option can be
22 used to add unmatched lines to the output. The -v option can be used
23 to output only unmatched lines.
24 The files file1 and file2 shall be ordered in the collating sequence of
26 sort -b on the fields on which they shall be joined, by default the
27 first in each line. All selected output shall be written in the same
28 collating sequence.
29 The default input field separators shall be <blank>s. In this case,
31 multiple separators shall count as one field separator, and leading
32 separators shall be ignored. The default output field separator shall
33 be a <space>.
34 The field separator and collating sequence can be changed by using the
36 -t option (see below).
37 If the same key appears more than once in either file, all combinations
39 of the set of remaining fields in file1 and the set of remaining fields
40 in file2 are output in the order of the lines encountered.
41 If the input files are not in the appropriate collating sequence, the
43 results are unspecified.
44
46 The join utility shall conform to the Base Definitions volume of
47 IEEE Std 1003.1-2001, Section 12.2, Utility Syntax Guidelines.
48 The following options shall be supported:
50 -a file_number
52 Produce a line for each unpairable line in file file_number,
54 where file_number is 1 or 2, in addition to the default output.
55 If both -a1 and -a2 are specified, all unpairable lines shall be
56 output.
57 -e string
59 Replace empty output fields in the list selected by -o with the
60 string string.
61 -o list
63 Construct the output line to comprise the fields specified in
64 list, each element of which shall have one of the following two
65 forms:
66 1. file_number.field, where file_number is a file number and
68 field is a decimal integer field number
69 2. 0 (zero), representing the join field
71 The elements of list shall be either comma-separated or <blank>-sepa‐
73 rated, as specified in Guideline 8 of the Base Definitions volume of
74 IEEE Std 1003.1-2001, Section 12.2, Utility Syntax Guidelines. The
75 fields specified by list shall be written for all selected output
76 lines. Fields selected by list that do not appear in the input shall be
77 treated as empty output fields. (See the -e option.) Only specifically
78 requested fields shall be written. The application shall ensure that
79 list is a single command line argument.
80 -t char
82 Use character char as a separator, for both input and output.
83 Every appearance of char in a line shall be significant. When
84 this option is specified, the collating sequence shall be the
85 same as sort without the -b option.
86 -v file_number
88 Instead of the default output, produce a line only for each
90 unpairable line in file_number, where file_number is 1 or 2. If
91 both -v1 and -v2 are specified, all unpairable lines shall be
92 output.
93 -1 field
95 Join on the fieldth field of file 1. Fields are decimal integers
96 starting with 1.
97 -2 field
99 Join on the fieldth field of file 2. Fields are decimal integers
100 starting with 1.
101
104 The following operands shall be supported:
105 file1, file2
107 A pathname of a file to be joined. If either of the file1 or
108 file2 operands is '-' , the standard input shall be used in its
109 place.
110
113 The standard input shall be used only if the file1 or file2 operand is
114 '-' . See the INPUT FILES section.
115
117 The input files shall be text files.
118
120 The following environment variables shall affect the execution of join:
121 LANG Provide a default value for the internationalization variables
123 that are unset or null. (See the Base Definitions volume of
124 IEEE Std 1003.1-2001, Section 8.2, Internationalization Vari‐
125 ables for the precedence of internationalization variables used
126 to determine the values of locale categories.)
127 LC_ALL If set to a non-empty string value, override the values of all
129 the other internationalization variables.
130 LC_COLLATE
132 Determine the locale of the collating sequence join expects to
134 have been used when the input files were sorted.
135 LC_CTYPE
137 Determine the locale for the interpretation of sequences of
138 bytes of text data as characters (for example, single-byte as
139 opposed to multi-byte characters in arguments and input files).
140 LC_MESSAGES
142 Determine the locale that should be used to affect the format
143 and contents of diagnostic messages written to standard error.
144 NLSPATH
146 Determine the location of message catalogs for the processing of
147 LC_MESSAGES .
148
151 Default.
152
154 The join utility output shall be a concatenation of selected character
155 fields. When the -o option is not specified, the output shall be:
156 "%s%s%s\n", <join field>, <other file1 fields>,
159 <other file2 fields>
160 If the join field is not the first field in a file, the
162 <other file fields> for that file shall be:
163 <fields preceding join field>, <fields following join field>
166 When the -o option is specified, the output format shall be:
168 "%s\n", <concatenation of fields>
171 where the concatenation of fields is described by the -o option, above.
173 For either format, each field (except the last) shall be written with
175 its trailing separator character. If the separator is the default (
176 <blank>s), a single <space> shall be written after each field (except
177 the last).
178
180 The standard error shall be used only for diagnostic messages.
181
183 None.
184
186 None.
187
189 The following exit values shall be returned:
190 0 All input files were output successfully.
192 >0 An error occurred.
194
197 Default.
198 The following sections are informative.
200
202 Pathnames consisting of numeric digits or of the form string.string
203 should not be specified directly following the -o list.
204
206 The -o 0 field essentially selects the union of the join fields. For
207 example, given file phone:
208 !Name Phone Number
211 Don +1 123-456-7890
212 Hal +1 234-567-8901
213 Yasushi +2 345-678-9012
214 and file fax:
216 !Name Fax Number
219 Don +1 123-456-7899
220 Keith +1 456-789-0122
221 Yasushi +2 345-678-9011
222 (where the large expanses of white space are meant to each represent a
224 single <tab>), the command:
225 join -t "<tab>" -a 1 -a 2 -e '(unknown)' -o 0,1.2,2.2 phone fax
228 would produce:
230 !Name Phone Number Fax Number
233 Don +1 123-456-7890 +1 123-456-7899
234 Hal +1 234-567-8901 (unknown)
235 Keith (unknown) +1 456-789-0122
236 Yasushi +2 345-678-9012 +2 345-678-9011
237 Multiple instances of the same key will produce combinatorial results.
239 The following:
240 fa:
243 a x
244 a y
245 a z
246 fb:
247 a p
248 will produce:
250 a x p
253 a y p
254 a z p
255 And the following:
257 fa:
260 a b c
261 a d e
262 fb:
263 a w x
264 a y z
265 a o p
266 will produce:
268 a b c w x
271 a b c y z
272 a b c o p
273 a d e w x
274 a d e y z
275 a d e o p
276
278 The -e option is only effective when used with -o because, unless spe‐
279 cific fields are identified using -o, join is not aware of what fields
280 might be empty. The exception to this is the join field, but identify‐
281 ing an empty join field with the -e string is not historical practice
282 and some scripts might break if this were changed.
283 The 0 field in the -o list was adopted from the Tenth Edition version
285 of join to satisfy international objections that the join in the base
286 documents does not support the "full join" or "outer join" described in
287 relational database literature. Although it has been possible to
288 include a join field in the output (by default, or by field number
289 using -o), the join field could not be included for an unpaired line
290 selected by -a. The -o 0 field essentially selects the union of the
291 join fields.
292 This sort of outer join was not possible with the join commands in the
294 base documents. The -o 0 field was chosen because it is an upwards-com‐
295 patible change for applications. An alternative was considered: have
296 the join field represent the union of the fields in the files (where
297 they are identical for matched lines, and one or both are null for
298 unmatched lines). This was not adopted because it would break some his‐
299 torical applications.
300 The ability to specify file2 as - is not historical practice; it was
302 added for completeness.
303 The -v option is not historical practice, but was considered necessary
305 because it permitted the writing of only those lines that do not match
306 on the join field, as opposed to the -a option, which prints both lines
307 that do and do not match. This additional facility is parallel with the
308 -v option of grep.
309 Some historical implementations have been encountered where a blank
311 line in one of the input files was considered to be the end of the
312 file; the description in this volume of IEEE Std 1003.1-2001 does not
313 cite this as an allowable case.
314
316 None.
317
319 awk , comm , sort , uniq
320
322 Portions of this text are reprinted and reproduced in electronic form
323 from IEEE Std 1003.1, 2003 Edition, Standard for Information Technology
324 -- Portable Operating System Interface (POSIX), The Open Group Base
325 Specifications Issue 6, Copyright (C) 2001-2003 by the Institute of
326 Electrical and Electronics Engineers, Inc and The Open Group. In the
327 event of any discrepancy between this version and the original IEEE and
328 The Open Group Standard, the original IEEE and The Open Group Standard
329 is the referee document. The original Standard can be obtained online
330 at http://www.opengroup.org/unix/online.html .
331IEEE/The Open Group 2003 JOIN(P)