Using awk for vlookup functionality in bash script

Could you please try following with awk, written and tested with GNU awk with shown samples.

awk '
BEGIN{
  FS=OFS="|"
}
FNR==NR{
  arr[$1]=$2
  next
}
($1 in arr){
  $1=($1 OFS arr[$1])
}
1
' file2.csv file1.csv

Explanation: Adding detailed explanation for above.

awk '                  ##Starting awk program from here.
BEGIN{                 ##Starting BEGIN section from here of this program.
  FS=OFS="|"           ##Setting | as field separator and output field separator.
}
FNR==NR{               ##Checking condition if FNR==NR which will be TRUE when file2.csv is being read.
  arr[$1]=$2           ##Creating arr with index of 1st field and value of 2nd field.
  next                 ##next will skip all further statements from here.
}
($1 in arr){           ##checking condition if $1 is present in arr then do following.
  $1=($1 OFS arr[$1])  ##Saving current $1 OFS and value of arr with index of $1 in $1.
}
1                      ##1 will print the current line.
' file2.csv file1.csv  ##Mentioning Input_file names here.

I tested the join command you provided and I think it produces the intended output on my machine (FreeBSD 12.2-RELEASE):

21|A800|AAAAA|1023
21|A800|BBBBB|1203
21|A800|CCCCC|2533
22|B900|DDDDD|1294
22|B900|EEEEE|1249
22|B900|FFFFF|4129
22A|C1000|GGGGG|4121
22A|C1000|HHHHH|1284
31B|D1000|IIIII|5403
31B|D1000|JJJJJ|1249

It's possible that you need to sort both files first on the columns (or in this case where you join the first columns the whole line should also work) that you intend to join, i.e. join -a1 -t '|' -1 1 -2 1 -o 1.1,2.2,1.2,1.3 <(sort file1.csv) <(sort file2.csv) > file3.csv