Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE citizen (id      NUMBER,
                      name    VARCHAR2(20),
                      postal  NUMBER,  -- <-- could do with a redesign to postalcode.id instead.
                      leader  NUMBER);
CREATE TABLE postalcode (id      NUMBER,
                         postal  NUMBER,
                         city    VARCHAR2(20),
                         area    VARCHAR2(20));
INSERT INTO citizen (id, name, postal, leader)
              SELECT 1, 'Smith', 2200,  null FROM DUAL
        UNION SELECT 2, 'Green', 31006, 1    FROM DUAL
        UNION SELECT 3, 'Jensen', 623,  1    FROM DUAL;
3 rows affected

SELECT *
  FROM citizen;


ID
NAME
POSTAL
LEADER


1
Smith
2200
null

2
Green
31006
1

3
Jensen
623
1


INSERT INTO postalcode (id, postal, city, area)
                 SELECT 1, 2200,     'BigCity',         'Geancy'  FROM DUAL
           UNION SELECT 2, 31006,    'SmallTown',       'Snizkim' FROM DUAL
           UNION SELECT 3, 31006,    'Settlement',      'Moon'    FROM DUAL  -- <-- Uuh-uhh.
           UNION SELECT 4, 78567390, 'LookoutTowerX89', 'Space'   FROM DUAL;
4 rows affected

SELECT *
  FROM postalcode;


ID
POSTAL
CITY
AREA


1
2200
BigCity
Geancy

2
31006
SmallTown
Snizkim

3
31006
Settlement
Moon

4
78567390
LookoutTowerX89
Space


-- CROSS JOIN
SELECT *
  FROM citizen
 CROSS JOIN postalcode


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

1
Smith
2200
null
2
31006
SmallTown
Snizkim

1
Smith
2200
null
3
31006
Settlement
Moon

1
Smith
2200
null
4
78567390
LookoutTowerX89
Space

2
Green
31006
1
1
2200
BigCity
Geancy

2
Green
31006
1
2
31006
SmallTown
Snizkim

2
Green
31006
1
3
31006
Settlement
Moon

2
Green
31006
1
4
78567390
LookoutTowerX89
Space

3
Jensen
623
1
1
2200
BigCity
Geancy

3
Jensen
623
1
2
31006
SmallTown
Snizkim

3
Jensen
623
1
3
31006
Settlement
Moon

3
Jensen
623
1
4
78567390
LookoutTowerX89
Space


-- INNER JOIN
SELECT *
  FROM citizen    c
  JOIN postalcode p ON c.postal = p.postal


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

2
Green
31006
1
2
31006
SmallTown
Snizkim

2
Green
31006
1
3
31006
Settlement
Moon


-- LEFT OUTER JOIN
SELECT *
  FROM citizen         c
  LEFT JOIN postalcode p ON c.postal = p.postal


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

2
Green
31006
1
2
31006
SmallTown
Snizkim

2
Green
31006
1
3
31006
Settlement
Moon

3
Jensen
623
1
null
null
null
null


-- CROSS JOIN resulting in rows as the General idea/INNER JOIN
SELECT *
  FROM citizen          c
  CROSS JOIN postalcode p
 WHERE c.postal = p.postal -- < -- The WHERE condition is limiting the resulting rows


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

2
Green
31006
1
2
31006
SmallTown
Snizkim

2
Green
31006
1
3
31006
Settlement
Moon


-- INNER JOIN becomes a cartesian products. It's the same as General idea/CROSS JOIN
SELECT *
  FROM citizen    c
  JOIN postalcode p ON 1 = 1  -- < -- The ON condition makes it a CROSS JOIN


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

1
Smith
2200
null
2
31006
SmallTown
Snizkim

1
Smith
2200
null
3
31006
Settlement
Moon

1
Smith
2200
null
4
78567390
LookoutTowerX89
Space

2
Green
31006
1
1
2200
BigCity
Geancy

2
Green
31006
1
2
31006
SmallTown
Snizkim

2
Green
31006
1
3
31006
Settlement
Moon

2
Green
31006
1
4
78567390
LookoutTowerX89
Space

3
Jensen
623
1
1
2200
BigCity
Geancy

3
Jensen
623
1
2
31006
SmallTown
Snizkim

3
Jensen
623
1
3
31006
Settlement
Moon

3
Jensen
623
1
4
78567390
LookoutTowerX89
Space


-- LEFT JOIN results in rows as the General idea/CROSS JOIN
SELECT *
  FROM citizen         c
  LEFT JOIN postalcode p ON 1 = 1 -- < -- The ON condition makes it a CROSS JOIN


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

1
Smith
2200
null
2
31006
SmallTown
Snizkim

1
Smith
2200
null
3
31006
Settlement
Moon

1
Smith
2200
null
4
78567390
LookoutTowerX89
Space

2
Green
31006
1
1
2200
BigCity
Geancy

2
Green
31006
1
2
31006
SmallTown
Snizkim

2
Green
31006
1
3
31006
Settlement
Moon

2
Green
31006
1
4
78567390
LookoutTowerX89
Space

3
Jensen
623
1
1
2200
BigCity
Geancy

3
Jensen
623
1
2
31006
SmallTown
Snizkim

3
Jensen
623
1
3
31006
Settlement
Moon

3
Jensen
623
1
4
78567390
LookoutTowerX89
Space


-- LEFT JOIN results in rows as the General idea/INNER JOIN
SELECT *
  FROM citizen         c
  LEFT JOIN postalcode p ON c.postal = p.postal
 WHERE p.postal IS NOT NULL -- < -- removed the row where there's no mathcing result from postalcode


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

2
Green
31006
1
2
31006
SmallTown
Snizkim

2
Green
31006
1
3
31006
Settlement
Moon


SELECT *
  FROM citizen c1
  JOIN citizen c2 ON c1.id = c2.leader


ID
NAME
POSTAL
LEADER
ID
NAME
POSTAL
LEADER


1
Smith
2200
null
2
Green
31006
1

1
Smith
2200
null
3
Jensen
623
1


SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name = 'Smith';


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

1
Smith
2200
null
2
31006
SmallTown
Snizkim

1
Smith
2200
null
3
31006
Settlement
Moon

1
Smith
2200
null
4
78567390
LookoutTowerX89
Space


SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE p.area = 'Moon';


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
3
31006
Settlement
Moon

2
Green
31006
1
3
31006
Settlement
Moon

3
Jensen
623
1
3
31006
Settlement
Moon


SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name = 'Smith'
   AND p.area = 'Moon';


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
3
31006
Settlement
Moon


SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name = 'Smith'
INTERSECT
SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE p.area = 'Moon';


QCSJ_C000000000300000
NAME
QCSJ_C000000000300002
LEADER
QCSJ_C000000000300001
QCSJ_C000000000300003
CITY
AREA


1
Smith
2200
null
3
31006
Settlement
Moon


SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name = 'Smith'
UNION
SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE p.area = 'Moon';


QCSJ_C000000000300000
NAME
QCSJ_C000000000300002
LEADER
QCSJ_C000000000300001
QCSJ_C000000000300003
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

1
Smith
2200
null
2
31006
SmallTown
Snizkim

1
Smith
2200
null
3
31006
Settlement
Moon

1
Smith
2200
null
4
78567390
LookoutTowerX89
Space

2
Green
31006
1
3
31006
Settlement
Moon

3
Jensen
623
1
3
31006
Settlement
Moon


SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name = 'Smith'
   OR p.area = 'Moon';


ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA


1
Smith
2200
null
1
2200
BigCity
Geancy

1
Smith
2200
null
2
31006
SmallTown
Snizkim

1
Smith
2200
null
3
31006
Settlement
Moon

1
Smith
2200
null
4
78567390
LookoutTowerX89
Space

2
Green
31006
1
3
31006
Settlement
Moon

3
Jensen
623
1
3
31006
Settlement
Moon


SELECT *
  FROM citizen
 WHERE name = 'Smith'
UNION  
SELECT *
  FROM postalcode
 WHERE area = 'Moon';
ORA-01790: expression must have same datatype as corresponding expression
ID	POSTAL	CITY	AREA
1	2200	BigCity	Geancy
2	31006	SmallTown	Snizkim
3	31006	Settlement	Moon
4	78567390	LookoutTowerX89	Space
ID	NAME	POSTAL	LEADER	ID	POSTAL	CITY	AREA
1	Smith	2200	null	1	2200	BigCity	Geancy
1	Smith	2200	null	2	31006	SmallTown	Snizkim
1	Smith	2200	null	3	31006	Settlement	Moon
1	Smith	2200	null	4	78567390	LookoutTowerX89	Space
2	Green	31006	1	1	2200	BigCity	Geancy
2	Green	31006	1	2	31006	SmallTown	Snizkim
2	Green	31006	1	3	31006	Settlement	Moon
2	Green	31006	1	4	78567390	LookoutTowerX89	Space
3	Jensen	623	1	1	2200	BigCity	Geancy
3	Jensen	623	1	2	31006	SmallTown	Snizkim
3	Jensen	623	1	3	31006	Settlement	Moon
3	Jensen	623	1	4	78567390	LookoutTowerX89	Space