How to join 2 files based one key and selected some specific column?

Question

I have 2 files and want to inner join them using awk. This is written using sql server :

SELECT [file1.column1],[file2.column2] FROM file1
INNER JOIN file2 on file2.column1 = file1.column5;

This is the file i want to join:

file1 :

file2:

so the key is column5 file1 and column1 file2.

How to write them in awk language?

Your help will be useful for me to learn this awk.

Answer 1

One way:

join -t"|" -1 5 -2 1 -o 1.1 2.2 file1 file2

-1 5 - Use the 5th column of file1 -2 1 - Use the 1st column of file2 -o 1.1 2.2 - Print as output 1st column of 1st file, 2nd column of 2nd file

Answer 2

If awk is the tool of choice

awk -F'|' 'NR==FNR{id[$5]=0; next}{if ($1 in id) print $1, $2}' file1 file2