Let’s say your dataset is big in both dimensions – rows and columns. Then you want to use join. To use join, you have to sort your data first. Something along those lines:
<File1.txt sort -k2,2 > File1-sorted.txt
<File2.txt sort -k3,3 -S1G > File2-sorted.txt
join -1 2 -2 3 File1-sorted.txt File2-sorted.txt > matches.txt
The sort -k2,2 means ‘sort whole rows so the values of second column are in ascending order. The join -1 2 means ‘the key in the first file is the second column’.
If your files are bigger than say 100 MB it pays of to assign additional memory to the sort via the -S option. The rule of thumb is to assign 1.3 times the size of the input to avoid any disk swapping by sort. But only if your system can handle that.
If one of your data files is very small (say up to 100 lines), you can consider doing something like
<File2.txt fgrep -F <( <File1.txt cut -f2 ) > File2-matches.txt
to avoid the sort, but then you’d have to look up the ‘keys’ from that file.
The decision which one to use is very similar to the ‘hash join’ and ‘merge join’ in the database world.
2
solved Find rows with the same value in a column in two files