comm and join: set operations without a database

Two sorted files, four questions

“Which users are in both lists?” and “which are only in the old one?” are database questions, and people import CSVs into SQLite to answer them. Two coreutils commands answer them on sorted text files, and they have been doing it since long before SQLite existed.

comm answers set questions#

Give it two sorted files and it prints three columns: lines only in the first, lines only in the second, lines in both.

$ cat a.txt        $ cat b.txt
alice              bob
bob                carol
carol              dave

$ comm a.txt b.txt
alice
		bob
		carol
	dave

The indentation is the answer. alice is in column one, so it exists only in a.txt. dave is indented once, only in b.txt. bob and carol are indented twice, present in both.

That output is hard to read and easy to filter. That is the point. Each column has a number, and passing that number suppresses it:

# suppress columns 1 and 2, keep only what is in both
$ comm -12 a.txt b.txt
bob
carol

# suppress 2 and 3, keep only what is unique to the first file
$ comm -23 a.txt b.txt
alice

Intersection and difference, in four characters. -13 gives you what only the second file has, and the symmetric difference is -3.

The sorted requirement is not a suggestion#

comm walks both files once, in lockstep, so it handles files far larger than memory. It also means unsorted input produces nonsense, and it tells you:

$ comm u.txt a.txt
comm: file 1 is not in sorted order
	alice
b

A warning on stderr and wrong output on stdout. Always sort first, and be consistent about it, because two files sorted under different rules are not comparable even when each looks sorted on its own.

join is the relational one#

Where comm compares whole lines, join matches on a key field and merges the rest, exactly a SQL inner join:

$ cat l.txt        $ cat r.txt
1 alice            1 admin
2 bob              3 user
3 carol

$ join l.txt r.txt
1 alice admin
3 carol user

Key 2 had no match on the right, so it dropped out. To keep it, ask for a left join with -a1:

$ join -a1 l.txt r.txt
1 alice admin
2 bob
3 carol user

The key defaults to the first field of both files. When it is not, name it:

# key is field 2 on the left, field 1 on the right
$ join -1 2 -2 1 l3.txt r3.txt
1 alice admin
2 bob user

And -t sets the delimiter, which turns it into a CSV tool:

$ join -t, lc.txt rc.txt
1,alice,admin
2,bob,user

join is as strict about sorting as comm, and louder about it:

$ join l2.txt r.txt
join: l2.txt:2: is not sorted: 1 alice
3 carol user
join: input is not in sorted order

Note that it still printed a partial result between the two warnings. In a script that ignores stderr, that silently becomes a wrong answer rather than a failure.

Where they run#

Both are coreutils, present on macOS, Debian and Ubuntu, and every example above gave identical output on all three.

The honest limit is scale in the other direction. For two sorted files of any size, comm and join beat anything you could load into memory. For a real join on unsorted data with several keys and types, sorting the input is the expensive part, the moment a database earns its keep.

← → jump to the previous / next post