Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table EMPLOYEES(emp_id int, name varchar(20), is_active varchar(10));
create table Address(add_id int ,emp_id int ,address varchar(50),is_active varchar(10));
insert into EMPLOYEES values (1,'NAME1','A');
1 rows affected

insert into Address values(1,1,'addr1','N');
1 rows affected

insert into Address values(2,1,'addr1','N');
1 rows affected

SELECT * FROM EMPLOYEES e
LEFT OUTER JOIN (select * from Address where is_active='A') a
ON e.emp_id=a.emp_id 
WHERE e.is_active='A'
AND (a.is_active='A' or a.is_active is null);


EMP_ID
NAME
IS_ACTIVE
ADD_ID
EMP_ID
ADDRESS
IS_ACTIVE


1
NAME1
A
null
null
null
null