first of all, let's assume that the indexes that should be added in both AB tables are added (it should not be so slow if table B is added with a federated index). Since you said in your comments that you don't have permission to touch their tables, don't consider this problem yet.

Let's start with why nested join is slower: multiple layers of join are used in the statement, and a join is a Cartesian product. When there is a large amount of data, the amount of Cartesian product is astronomical. With subqueries, in the worst case, the amount of computation is a Cartesian product.

solution, of course, the best way is to write a python script to load the data of table B into memory at one time, and then assemble the data of table A by yourself, so that you don't have to query table B every time.

if the conditions do not allow, try adding limit 1 to the subquery to reduce the number of rows for some full table scans:

select 

(select code_name from B where code_id = A.GJ and field_name = 'GJ' limit 1) as GJ
(select code_name from B where code_id = A.MZ and field_name = 'MZ' limit 1) as MZ
(select code_name from B where code_id = A.JG and field_name = 'JG' limit 1) as JG

FROM

A;

explain take a look at the execution plan

B table to establish a joint index idx_code_id_field_name (code_id,field_name)
if you don't have permission to modify the table, you can always create a temporary table of your own. The temporary table is indexed and associated with Table A