Oracle 18c

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

-- Policies selected by client
create table client_policies (
  id number,
  client_id number,
  policy_id varchar2(30),
  policy_description varchar2(100),
  policy_added_date timestamp,
  policy_modified_date timestamp
)
/
-- All available policies
create table policies (
  policy_id varchar2(30),
  policy_description varchar2(100)
)
/
begin
insert into client_policies values (1, 123, 'HOUSE_INSURANCE', 'house insurance policy', sysdate - 10, sysdate - 3);
insert into client_policies values (2, 123, 'HEALTH_INSURANCE', 'health insurance policy', sysdate - 20, sysdate - 5);
insert into client_policies values (3, 123, 'CIVIL_LIABILITY_INSURANCE', 'civil liability insurance policy', sysdate - 2, sysdate - 1);

insert into policies values ('HOUSE_INSURANCE', 'house insurance policy');
insert into policies values ('HEALTH_INSURANCE', 'health insurance policy');
insert into policies values ('CIVIL_LIABILITY_INSURANCE', 'civil liability insurance policy');
insert into policies values ('CAR_INSURANCE', 'car insurance policy');
insert into policies values ('ETC_INSURANCE', 'etc... insurance policy');
end;
/
1 rows affected

SELECT
  client_policies.id,
  policies.policy_id,
  COALESCE(client_policies.policy_description, policies.policy_description)  AS policy_description,
  client_policies.policy_added_date,
  client_policies.policy_modified_date  
FROM
  policies
LEFT JOIN
  client_policies
    ON client_policies.policy_id = policies.policy_id


ID
POLICY_ID
POLICY_DESCRIPTION
POLICY_ADDED_DATE
POLICY_MODIFIED_DATE


1
HOUSE_INSURANCE
house insurance policy
11-JUN-21 17.15.43.000000
18-JUN-21 17.15.43.000000

2
HEALTH_INSURANCE
health insurance policy
01-JUN-21 17.15.43.000000
16-JUN-21 17.15.43.000000

3
CIVIL_LIABILITY_INSURANCE
civil liability insurance policy
19-JUN-21 17.15.43.000000
20-JUN-21 17.15.43.000000

null
ETC_INSURANCE
etc... insurance policy
null
null

null
CAR_INSURANCE
car insurance policy
null
null

ID	POLICY_ID	POLICY_DESCRIPTION	POLICY_ADDED_DATE	POLICY_MODIFIED_DATE
1	HOUSE_INSURANCE	house insurance policy	11-JUN-21 17.15.43.000000	18-JUN-21 17.15.43.000000
2	HEALTH_INSURANCE	health insurance policy	01-JUN-21 17.15.43.000000	16-JUN-21 17.15.43.000000
3	CIVIL_LIABILITY_INSURANCE	civil liability insurance policy	19-JUN-21 17.15.43.000000	20-JUN-21 17.15.43.000000
null	ETC_INSURANCE	etc... insurance policy	null	null
null	CAR_INSURANCE	car insurance policy	null	null