preserving field order in a 'join'

Question

Let's say I have two files like this:

1   a   one ...
2   b   two ...
3   c   three   ...
4   d   four    ...
5   e   five    ...
6   f   six ...

and

a
c
f

Is there a way, not knowing the number of columns in file 1 (or not having to repeat each field), I can use join to get the matching lines

1   a   one ...
3   c   three   ...
6   f   six ...

I've tried

join -1 2 -o 1.0 file1 file2

like you'd write $0 to get all fields in awk, but I obviously get invalid field number.

Answer 1

You can choose which fields to print in which order or print all fields in default order. But you cannot print 'the rest of the fields'.

From man join:

-o FORMAT
       obey FORMAT while constructing output line
[...]
Any FIELD is a field number counted from 1. FORMAT is one or
more  comma or blank separated specifications, each being
'FILENUM.FIELD' or '0'.

You could use awk:

awk -v regex="$(head -c -1 file2 | tr '\n' '|')" '$2 ~ regex' file1

• Remove last newline of file2 and replace newlines wit '|', the regex will be a|c|f
• Match the second field of file1 with that regex

Or use the awk solution of Sundeep especially if file2 contains special characters.