Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table countries 
(
country_id int,
country_name varchar(100),
region_id int
);
create table locations
(
location_id int,
city varchar(100),
country_id int
);
insert into countries values(1,'England',1);
1 rows affected

insert into countries values(2,'France',1);
1 rows affected

insert into countries values(3,'Spain',1);
1 rows affected

insert into countries values(4,'Italy',1);
1 rows affected

insert into locations values (1,'Tokyo',10);
1 rows affected

insert into locations values (2,'Paris',2);
1 rows affected

insert into locations values (3,'Sevilla',3);
1 rows affected

insert into locations values (4,'Berlin',6);
1 rows affected

SELECT c.country_name, l.city
FROM countries c
LEFT JOIN locations l ON c.country_id = l.country_id
WHERE l.city IS NULL
UNION ALL
SELECT c.country_name, l.city
FROM countries c
RIGHT JOIN locations l ON c.country_id = l.country_id
WHERE c.country_name IS NULL;


COUNTRY_NAME
CITY


Italy
null

England
null

null
Berlin

null
Tokyo


SELECT c.country_name, l.city
FROM countries c
LEFT JOIN locations l ON c.country_id = l.country_id
WHERE l.city IS NULL
UNION ALL
SELECT c.country_name, l.city
FROM locations l
LEFT JOIN countries c ON c.country_id = l.country_id
WHERE c.country_name IS NULL;


COUNTRY_NAME
CITY


Italy
null

England
null

null
Berlin

null
Tokyo


SELECT c.country_name, NULL city
FROM countries c
WHERE country_id NOT IN (SELECT country_id FROM locations)
UNION ALL
SELECT NULL country_name, l.city
FROM locations l
WHERE country_id NOT IN (SELECT country_id FROM countries);


COUNTRY_NAME
CITY


Italy
null

England
null

null
Berlin

null
Tokyo


SELECT c.country_name, NULL city
FROM countries c
WHERE NOT EXISTS (SELECT 1 FROM locations WHERE country_id = c.country_id)
UNION
SELECT NULL country_name, l.city
FROM locations l
WHERE NOT EXISTS (SELECT 1 FROM countries WHERE country_id = l.country_id);


COUNTRY_NAME
CITY


England
null

Italy
null

null
Berlin

null
Tokyo