SQL multiple data count from multiple tables with union all

My existing SQL looks like the below, it will generate only the total_pending_req count.

SELECT count(table1.employee_code) as total_requests, table1.employee_code as emp_code
FROM table1
WHERE employee_status = 'PENDING'
GROUP BY emp_code

UNION ALL

SELECT count(table2.employee_code) as total_requests, table2.employee_code as emp_code
FROM table2
WHERE employee_status = 'PENDING'
GROUP BY emp_code

UNION ALL

SELECT count(table3.employee_code) as total_requests, table3.employee_code as emp_code
FROM table3
WHERE employee_status = 'PENDING'
GROUP BY emp_code

This will return the result below,

I want to get the request count as total_pending_req, total_rejected_req and total_completed_req considering 3 different tables. All tables have the same status codes, PENDING, COMPLETED and REJECTED. The final result should be like this,

I would like to have an idea, of how to extract data more efficiently, since I have to use UNION ALL. May I know if there is any better approach to extract data more efficiently? I would appreciate your help on this.

The fact that your rows appear in the
several table{1,2,3} relations is just
an annoying distraction.
Let's make it a single relation, already.
We could create a table or a view.

CREATE VIEW combined AS
(SELECT * FROM table1
 UNION ALL
 SELECT * FROM table2
 UNION ALL 
 SELECT * FROM table3
 UNION ALL
)

Good!

With that out of the way, it becomes a trivial GROUP BY.

SELECT    employee_code, employee_status, COUNT(*)
FROM      combined
GROUP BY  employee_code, employee_status

You can phrase it as a
CTE
if you're averse to DDL.
Or create a VIEW that does the UNION ALL heavy lifting.
Or a MATERIALIZED VIEW, whatever.

The output format is three emps × three statuses,
or nine rows.

If you really need three rows, feel free to SELECT
from that relation to re-format it.

you can use CASE and Union All as follows

select 
table1.employee_code as emp_code,
case employee_status = 'PENDING' then count(table1.employee_code)  else 0 end as PENDING,
case employee_status = 'COMPLETED' then count(table1.employee_code)  else 0 end as COMPLETED,
case employee_status = 'REJECTED' then count(table1.employee_code)  else 0 end as REJECTED   
from FROM table1
GROUP BY emp_code
UNION ALL
select 
table2.employee_code as emp_code,
case employee_status = 'PENDING' then count(table2.employee_code)  else 0 end as PENDING,
case employee_status = 'COMPLETED' then count(table2.employee_code)  else 0 end as COMPLETED,
case employee_status = 'REJECTED' then count(table2.employee_code)  else 0 end as REJECTED   
from FROM table2
GROUP BY emp_code
UNION ALL
select 
table3.employee_code as emp_code,
case employee_status = 'PENDING' then count(table3.employee_code)  else 0 end as PENDING,
case employee_status = 'COMPLETED' then count(table3.employee_code)  else 0 end as COMPLETED,
case employee_status = 'REJECTED' then count(table3.employee_code)  else 0 end as REJECTED   
from FROM table3
GROUP BY emp_code

Or using SUM and Case

SELECT 
table1.employee_code as emp_code,
SUM(CASE employee_status = 'PENDING' Then 1 Else 0 End ) as PENDING,
SUM(CASE employee_status = 'COMPLETED' Then 1 Else 0 End ) as COMPLETED,
SUM(CASE employee_status = 'REJECTED' Then 1 Else 0 End ) as REJECTED
from FROM table1
GROUP BY emp_code
UNION ALL
SELECT 
table2.employee_code as emp_code,
SUM(CASE employee_status = 'PENDING' Then 1 Else 0 End ) as PENDING,
SUM(CASE employee_status = 'COMPLETED' Then 1 Else 0 End ) as COMPLETED,
SUM(CASE employee_status = 'REJECTED' Then 1 Else 0 End ) as REJECTED
from FROM table2
GROUP BY emp_code
UNION ALL
SELECT 
table3.employee_code as emp_code,
SUM(CASE employee_status = 'PENDING' Then 1 Else 0 End ) as PENDING,
SUM(CASE employee_status = 'COMPLETED' Then 1 Else 0 End ) as COMPLETED,
SUM(CASE employee_status = 'REJECTED' Then 1 Else 0 End ) as REJECTED
from FROM table3
GROUP BY emp_code