logo

utils-std

Collection of commonly available Unix tools git clone https://anongit.hacktivis.me/git/utils-std.git

join.1 (7285B)


  1. .\" SPDX-License-Identifier: BSD-3-Clause
  2. .\" Copyright (c) 1990, 1993
  3. .\" The Regents of the University of California. All rights reserved.
  4. .\"
  5. .\" This code is derived from software contributed to Berkeley by
  6. .\" the Institute of Electrical and Electronics Engineers, Inc.
  7. .\"
  8. .\" Redistribution and use in source and binary forms, with or without
  9. .\" modification, are permitted provided that the following conditions
  10. .\" are met:
  11. .\" 1. Redistributions of source code must retain the above copyright
  12. .\" notice, this list of conditions and the following disclaimer.
  13. .\" 2. Redistributions in binary form must reproduce the above copyright
  14. .\" notice, this list of conditions and the following disclaimer in the
  15. .\" documentation and/or other materials provided with the distribution.
  16. .\" 3. Neither the name of the University nor the names of its contributors
  17. .\" may be used to endorse or promote products derived from this software
  18. .\" without specific prior written permission.
  19. .\"
  20. .\" THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND
  21. .\" ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
  22. .\" IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
  23. .\" ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE
  24. .\" FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
  25. .\" DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
  26. .\" OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
  27. .\" HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
  28. .\" LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
  29. .\" OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
  30. .\" SUCH DAMAGE.
  31. .\"
  32. .\" @(#)join.1 8.3 (Berkeley) 4/28/95
  33. .\"
  34. .Dd June 20, 2020
  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 o Ar list
  47. .Op Fl t Ar char
  48. .Op Fl 1 Ar field
  49. .Op Fl 2 Ar field
  50. .Ar file1
  51. .Ar file2
  52. .Sh DESCRIPTION
  53. The
  54. .Nm
  55. utility performs an
  56. .Dq equality join
  57. on the specified files
  58. and writes the result to the standard output.
  59. The
  60. .Dq join field
  61. is the field in each file by which the files are compared.
  62. The first field in each line is used by default.
  63. There is one line in the output for each pair of lines in
  64. .Ar file1
  65. and
  66. .Ar file2
  67. which have identical join fields.
  68. Each output line consists of the join field, the remaining fields from
  69. .Ar file1
  70. and then the remaining fields from
  71. .Ar file2 .
  72. .Pp
  73. The default field separators are tab and space characters.
  74. In this case, multiple tabs and spaces count as a single field separator,
  75. and leading tabs and spaces are ignored.
  76. The default output field separator is a single space character.
  77. .Pp
  78. Many of the options use file and field numbers.
  79. Both file numbers and field numbers are 1 based, i.e., the first file on
  80. the command line is file number 1 and the first field is field number 1.
  81. The following options are available:
  82. .Bl -tag -width indent
  83. .It Fl a Ar file_number
  84. In addition to the default output, produce a line for each unpairable
  85. line in file
  86. .Ar file_number .
  87. .It Fl e Ar string
  88. Replace empty output fields with
  89. .Ar string .
  90. .It Fl o Ar list
  91. The
  92. .Fl o
  93. option specifies the fields that will be output from each file for
  94. each line with matching join fields.
  95. Each element of
  96. .Ar list
  97. has either the form
  98. .Ar file_number . Ns Ar field ,
  99. where
  100. .Ar file_number
  101. is a file number and
  102. .Ar field
  103. is a field number, or the form
  104. .Ql 0
  105. .Pq zero ,
  106. representing the join field.
  107. The elements of list must be either comma
  108. .Pq Ql \&,
  109. or whitespace separated.
  110. (The latter requires quoting to protect it from the shell, or, a simpler
  111. approach is to use multiple
  112. .Fl o
  113. options.)
  114. .It Fl t Ar char
  115. Use character
  116. .Ar char
  117. as a field delimiter for both input and output.
  118. Every occurrence of
  119. .Ar char
  120. in a line is significant.
  121. .It Fl v Ar file_number
  122. Do not display the default output, but display a line for each unpairable
  123. line in file
  124. .Ar file_number .
  125. The options
  126. .Fl v Cm 1
  127. and
  128. .Fl v Cm 2
  129. may be specified at the same time.
  130. .It Fl 1 Ar field
  131. Join on the
  132. .Ar field Ns 'th
  133. field of
  134. .Ar file1 .
  135. .It Fl 2 Ar field
  136. Join on the
  137. .Ar field Ns 'th
  138. field of
  139. .Ar file2 .
  140. .El
  141. .Pp
  142. When the default field delimiter characters are used, the files to be joined
  143. should be ordered in the collating sequence of
  144. .Xr sort 1 ,
  145. using the
  146. .Fl b
  147. option, on the fields on which they are to be joined, otherwise
  148. .Nm
  149. may not report all field matches.
  150. When the field delimiter characters are specified by the
  151. .Fl t
  152. option, the collating sequence should be the same as
  153. .Xr sort 1
  154. without the
  155. .Fl b
  156. option.
  157. .Pp
  158. If one of the arguments
  159. .Ar file1
  160. or
  161. .Ar file2
  162. is
  163. .Sq Fl ,
  164. the standard input is used.
  165. .Sh EXIT STATUS
  166. .Ex -std
  167. .Sh EXAMPLES
  168. Assuming a file named
  169. .Pa nobel_laureates.txt
  170. with information about some of the first Nobel Peace Prize laureates:
  171. .Bd -literal -offset indent
  172. 1901,Jean Henri Dunant,M
  173. 1901,Frederic Passy,M
  174. 1902,Elie Ducommun,M
  175. 1905,Baroness Bertha Sophie Felicita Von Suttner,F
  176. 1910,Permanent International Peace Bureau,
  177. .Ed
  178. .Pp
  179. and a second file
  180. .Pa nobel_nationalities.txt
  181. with their nationalities:
  182. .Bd -literal -offset indent
  183. Jean Henri Dunant,Switzerland
  184. Frederic Passy,France
  185. Elie Ducommun,Switzerland
  186. Baroness Bertha Sophie Felicita Von Suttner
  187. .Ed
  188. .Pp
  189. Join the two files using the second column from first file and the default first
  190. column from second file specifying a custom field delimiter:
  191. .Bd -literal -offset indent
  192. $ join -t, -1 2 nobel_laureates.txt nobel_nationalities.txt
  193. Jean Henri Dunant,1901,M,Switzerland
  194. Frederic Passy,1901,M,France
  195. Elie Ducommun,1902,M,Switzerland
  196. Baroness Bertha Sophie Felicita Von Suttner,1905,F
  197. .Ed
  198. .Pp
  199. Show only the year and the nationality of the laureate using
  200. .Ql <<NULL>>
  201. to replace empty fields:
  202. .Bd -literal -offset indent
  203. $ join -e "<<NULL>>" -t, -1 2 -o "1.1 2.2" nobel_laureates.txt nobel_nationalities.txt
  204. 1901,Switzerland
  205. 1901,France
  206. 1902,Switzerland
  207. 1905,<<NULL>>
  208. .Ed
  209. .Pp
  210. Show only lines from first file which do not have a match in second file:
  211. .Bd -literal -offset indent
  212. $ join -v1 -t, -1 2 nobel_laureates.txt nobel_nationalities.txt
  213. Permanent International Peace Bureau,1910,
  214. .Ed
  215. .Pp
  216. Assuming a file named
  217. .Pa capitals.txt
  218. with the following content:
  219. .Bd -literal -offset indent
  220. Belgium,Brussels
  221. France,Paris
  222. Italy,Rome
  223. Switzerland
  224. .Ed
  225. .Pp
  226. Show the name and capital of the country where the laureate was born.
  227. This example uses
  228. .Pa nobel_nationalities.txt
  229. as a bridge but does not show any information from that file.
  230. Also see the note about
  231. .Xr sort 1
  232. above to understand why we need to sort the intermediate result.
  233. .Bd -literal -offset indent
  234. $ join -t, -1 2 -o "1.2 2.2" nobel_laureates.txt nobel_nationalities.txt | \e
  235. sort -k2 -t, | join -t, -e "<<NULL>>" -1 2 -o "1.1 2.2" - capitals.txt
  236. Elie Ducommun,<<NULL>>
  237. Jean Henri Dunant,<<NULL>>
  238. .Ed
  239. .Sh COMPATIBILITY
  240. Compatibility with
  241. .Fl a
  242. and multi-operand
  243. .Fl o
  244. from historical versions of
  245. .Nm
  246. got dropped, compatibility with
  247. .Fl j Ar field
  248. is kept for now.
  249. .Sh SEE ALSO
  250. .Xr awk 1 ,
  251. .Xr comm 1 ,
  252. .Xr paste 1 ,
  253. .Xr sort 1 ,
  254. .Xr uniq 1
  255. .Sh STANDARDS
  256. .Nm
  257. should be compliant with the
  258. IEEE Std 1003.1-2024 (“POSIX.1”)
  259. specification.