compare two columns of different files and print if it matches

This is what awk was designed for:

$ awk -F'|' 'NR==FNR{c[$1$2]++;next};c[$1$2] > 0' file2 file1 abc|123|BNY|apple| cab|234|cyx|orange| 

Explanation

• -F'|' : sets the field separator to |.
• NR==FNR : NR is the current input line number and FNR the current file's line number. The two will be equal only while the 1st file is being read.

• c[$1$2]++; next : if this is the 1st file, save the 1st two fields in the c array. Then, skip to the next line so that this is only applied on the 1st file.

• c[$1$2]>0 : the else block will only be executed if this is the second file so we check whether fields 1 and 2 of this file have already been seen (c[$1$2]>0) and if they have been, we print the line. In awk, the default action is to print the line so if c[$1$2]>0 is true, the line will be printed.

Alternatively, since you tagged with Perl:

perl -e 'open(A, "file2"); while(<A>){/.+?\|[^|]+/ && $k{$&}++}; while(<>){/.+?\|[^|]+/ && do{print if defined($k{$&})}}' file1 

Explanation

The first line will open file2, read everything up to the 2nd | (.+?\|[^|]+) and save that (the $& is the result of the last match operator) in the %k hash.

The second line processes file1, uses the same regex to extract the 1st two columns and print the line if those columns are defined in the %k hash.

Both of the above approaches will need to hold the 2 first columns of file2 in memory. That shouldn't be a problem if you only have a few hundred thousand lines but if it is, you could do something like

cut -d'|' -f 1,2 file2 | while read pat; do grep "^$pat" file1; done 

But that will be slower.

I think

grep -Ff file2 file1 

is what you're looking for. It should be efficient, but I'm not sure it will be as accurate as you want. If abc|123 (for example) is found in a line in file1 in different columns, that line will be printed as well. If you can guarantee that this will never happen, the above line should work.

If you would like to think the problem in SQL like way, then you definitely should try a tool named 'q':

$ q -d '|' "select f1.* from file1 f1 join file2 f2 on (f1.c1 = f2.c1 and f1.c2 = f2.c2)" 

It is more clear and easy to understand if you're familiar with SQL query.

$ sed 's/^/\^/' 2.txt > temp.txt ; grep 1.txt -f temp.txt abc|123|BNY|apple| cab|234|cyx|orange|