Oracle 11g Release 2

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

CREATE TABLE A (
  id INTEGER
);
CREATE TABLE B (
  id INTEGER
);
INSERT ALL 
INTO A(id) VALUES ('1')
INTO A(id) VALUES ('2')
INTO A(id) VALUES ('3')
SELECT * FROM dual
3 rows affected

INSERT ALL 
INTO B(id) VALUES ('2')
INTO B(id) VALUES ('3')
INTO B(id) VALUES ('4')
SELECT * FROM dual
3 rows affected

SELECT COALESCE(A.id, B.id) AS id,
       CASE WHEN A.id IS NULL THEN 'In B'
            WHEN B.id IS NULL THEN 'In A'
            ELSE 'In A & In B' END AS Present
FROM A
FULL OUTER JOIN B ON B.id = A.id
ORDER BY id


ID
PRESENT


1
In A

2
In A & In B

3
In A & In B

4
In B