← Full reference

join

Join two files on a common field, like JOIN in SQL — inner, outer, "unmatched"

join combines lines of two files whose key field values match — like JOIN in SQL. For example, a list of id name and a list of id role become id name role. Both files must be sorted by the key.

Syntax

join [options] file1 file2
join -t, -1 2 -2 1 file1 file2     # key: field 2 of the first and field 1 of the second

One of the files (but not both) can be - — standard input.

Output format

By default, for each pair of lines it prints: the key, then the remaining fields from the first file, then the remaining fields from the second. The output separator is a space (or the character from -t). If a key repeats in both files, all combinations are printed: 2 lines × 2 lines = 4 lines.

Options

Key and separator

OptionWhat it does
-1 Nthe key in the first file is field N (default 1)
-2 Nthe key in the second file is field N
-j Nthe same field in both files (-1 N -2 N)
-t Cinput and output field separator — a single character (-t,, -t:). Tab: -t $'\t'
-icompare keys case-insensitively

Without -t, fields are separated by spaces and tabs, leading blanks are ignored, and a single space is used on output.

Which lines to print (JOIN types)

OptionSQL equivalentWhat is printed
(no options)INNER JOINonly lines that have a match
-a 1LEFT JOINplus unmatched lines from the first file
-a 2RIGHT JOINplus unmatched lines from the second file
-a 1 -a 2FULL JOINplus unmatched lines from both
-v 1anti-joinonly unmatched lines from the first file
-v 2anti-joinonly unmatched lines from the second file

Line format

OptionWhat it does
-o 1.2,2.3print only the listed fields: file.field; 0 — the key
-o autothe number of fields is taken from the first line of each file — needed for -e to fill gaps
-e STRINGwhat to fill missing fields with (works together with -o)
--headerthe first line of each file is a header: printed once, not used for matching

Sort order check

OptionWhat it does
--check-orderstop with an error at the first unsorted line
--nocheck-orderdon't check (and don't warn)
-zlines are separated by a null byte

By default join warns about disorder (is not sorted) only if it encountered an unmatched line, and then returns code 1.

Examples

printf '1 alice\n2 bob\n3 carol\n' > users.txt
printf '1 admin\n3 dev\n4 ops\n' > roles.txt

# inner join on the first field
join users.txt roles.txt

# left join: bob without a role is included too
join -a 1 users.txt roles.txt

# full join with a placeholder for gaps
join -a 1 -a 2 -e NULL -o auto users.txt roles.txt

# users without a role / roles without a user
join -v 1 users.txt roles.txt
join -v 2 users.txt roles.txt

# CSV: the key is field 2 in the first file and field 1 in the second, print name and role
printf 'alice,10\nbob,20\n' > people.csv
printf '10,admin\n20,dev\n' > ids.csv
join -t, -1 2 -2 1 -o 1.1,2.2 <(sort -t, -k2,2 people.csv) <(sort -t, -k1,1 ids.csv)

# files with a header
printf 'id name\n1 alice\n2 bob\n' > hu.txt
printf 'id role\n1 admin\n' > hr.txt
join --header -a 1 -e - -o auto hu.txt hr.txt

# sort by the key on the fly
join <(sort -k1,1 users.txt) <(sort -k1,1 roles.txt)

Pitfalls

  • Sort both files by the key, as text. join expects the same order that sort -k1,1 produces (or sort -t, -kN,N for your field) in the same locale. Numeric sorting won't work: after sort -n the order 2, 10 is out of order for join, and lines with key 10 may not find a match and get lost.
  • Unsorted input doesn't always give an error. If all lines happen to find a match, there's no warning. If not, some lines disappear and stderr shows input is not in sorted order. In scripts add --check-order to fail right away.
  • A header without --header counts as data. It takes part in matching and sorting: sort moves the line id name to the end, after the digits, and the order for join breaks. Either use --header or cut off the header (tail -n +2) before sorting.
  • -e doesn't work without -o. join -a1 -e NULL prints a short line without the placeholder. Add -o auto or an explicit field list.
  • -t is a single character only. A multi-character separator (::, , ) isn't supported — replace it first, e.g. with sed.
  • Without -t, alignment by spaces is lost: on output the fields are joined by a single space. For a pretty table pipe the result through column -t.
  • Case and \r matter. Alice ≠ alice (there's -i), and a key 1\r from a Windows file won't match 1.
  • Repeated keys multiply. Two lines with key 1 in the first file and three in the second produce six lines — check the data for duplicates with uniq -d.

See also

sort · comm · paste · awk · cut · column