Efficient way to write query for multiple table which depends on column value of a table in mysql?
First of all, append a computed column, say A4Key, set to SUBSTRING(A4, 4, 3).

This column shall be kept up-to-date either by your application code or by triggers, such as:

CREATE TRIGGER ... BEFORE UPDATE ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...

CREATE TRIGGER ... BEFORE INSERT ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...


first solution

Then, rewrite your query to:

SELECT * FROM A
WHERE A4Key IN('023', '009', '011', '013', '015', '017', '019', '021')
  AND A3 NOT IN(SELECT Z FROM B)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('006', '024', '028', '031')
  AND A3 NOT IN(SELECT Z FROM C)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('004', '025')
  AND A3 NOT IN(SELECT Z FROM D)


Now, evaluate your query plans to devise the best indexes.

second solution

Create a table such as:

CREATE TABLE A4Exceptions(
  A4Key VARCHAR(3),
  Z     `whichever_type_of_Z`
)


With initial data set by:

INSERT INTO A4Exceptions SELECT '023', Z FROM B
INSERT INTO A4Exceptions SELECT '009', Z FROM B
...
INSERT INTO A4Exceptions SELECT '006', Z FROM C
...


And keep it up-to-date with the changing (?) data from B, C and D.

Done that, now you may, with blazing performance (after creating some indexes):

SELECT *
FROM A
WHERE NOT EXISTS(
  SELECT *
  FROM A4Exceptions exc
  WHERE exc.A4Key = A.A4Key
    AND ext.Z     = A.A3
)