By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36229 in the last week).
CREATE TABLE citizen (id NUMBER,
name VARCHAR2(20),
postal NUMBER, -- <-- could do with a redesign to postalcode.id instead.
leader NUMBER);
✓
hidden batch(es)
CREATE TABLE postalcode (id NUMBER,
postal NUMBER,
city VARCHAR2(20),
area VARCHAR2(20));
✓
hidden batch(es)
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
hidden batch(es)
SELECT *
FROM citizen;
ID
NAME
POSTAL
LEADER
1
Smith
2200
2
Green
31006
1
3
Jensen
623
1
…
hidden batch(es)
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
hidden batch(es)
SELECT *
FROM postalcode;
ID
POSTAL
CITY
AREA
1
2200
BigCity
Geancy
2
31006
SmallTown
Snizkim
3
31006
Settlement
Moon
4
78567390
LookoutTowerX89
Space
…
hidden batch(es)
-- CROSS JOIN
SELECT *
FROM citizen
CROSS JOIN postalcode
ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA
1
Smith
2200
1
2200
BigCity
Geancy
1
Smith
2200
2
31006
SmallTown
Snizkim
1
Smith
2200
3
31006
Settlement
Moon
1
Smith
2200
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
…
hidden batch(es)
-- 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
1
2200
BigCity
Geancy
2
Green
31006
1
2
31006
SmallTown
Snizkim
2
Green
31006
1
3
31006
Settlement
Moon
…
hidden batch(es)
-- 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
1
2200
BigCity
Geancy
2
Green
31006
1
2
31006
SmallTown
Snizkim
2
Green
31006
1
3
31006
Settlement
Moon
3
Jensen
623
1
…
hidden batch(es)
-- 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
1
2200
BigCity
Geancy
2
Green
31006
1
2
31006
SmallTown
Snizkim
2
Green
31006
1
3
31006
Settlement
Moon
…
hidden batch(es)
-- 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
1
2200
BigCity
Geancy
1
Smith
2200
2
31006
SmallTown
Snizkim
1
Smith
2200
3
31006
Settlement
Moon
1
Smith
2200
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
…
hidden batch(es)
-- 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
1
2200
BigCity
Geancy
1
Smith
2200
2
31006
SmallTown
Snizkim
1
Smith
2200
3
31006
Settlement
Moon
1
Smith
2200
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
…
hidden batch(es)
-- 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
1
2200
BigCity
Geancy
2
Green
31006
1
2
31006
SmallTown
Snizkim
2
Green
31006
1
3
31006
Settlement
Moon
…
hidden batch(es)
SELECT *
FROM citizen c1
JOIN citizen c2 ON c1.id = c2.leader
ID
NAME
POSTAL
LEADER
ID
NAME
POSTAL
LEADER
1
Smith
2200
2
Green
31006
1
1
Smith
2200
3
Jensen
623
1
…
hidden batch(es)
SELECT *
FROM citizen c
CROSS JOIN postalcode p
WHERE c.name = 'Smith';
ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA
1
Smith
2200
1
2200
BigCity
Geancy
1
Smith
2200
2
31006
SmallTown
Snizkim
1
Smith
2200
3
31006
Settlement
Moon
1
Smith
2200
4
78567390
LookoutTowerX89
Space
…
hidden batch(es)
SELECT *
FROM citizen c
CROSS JOIN postalcode p
WHERE p.area = 'Moon';
ID
NAME
POSTAL
LEADER
ID
POSTAL
CITY
AREA
1
Smith
2200
3
31006
Settlement
Moon
2
Green
31006
1
3
31006
Settlement
Moon
3
Jensen
623
1
3
31006
Settlement
Moon
…
hidden batch(es)
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
3
31006
Settlement
Moon
…
hidden batch(es)
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
3
31006
Settlement
Moon
…
hidden batch(es)
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
1
2200
BigCity
Geancy
1
Smith
2200
2
31006
SmallTown
Snizkim
1
Smith
2200
3
31006
Settlement
Moon
1
Smith
2200
4
78567390
LookoutTowerX89
Space
2
Green
31006
1
3
31006
Settlement
Moon
3
Jensen
623
1
3
31006
Settlement
Moon
…
hidden batch(es)
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
1
2200
BigCity
Geancy
1
Smith
2200
2
31006
SmallTown
Snizkim
1
Smith
2200
3
31006
Settlement
Moon
1
Smith
2200
4
78567390
LookoutTowerX89
Space
2
Green
31006
1
3
31006
Settlement
Moon
3
Jensen
623
1
3
31006
Settlement
Moon
…
hidden batch(es)
SELECT *
FROM citizen
WHERE name = 'Smith'
UNION
SELECT *
FROM postalcode
WHERE area = 'Moon';
ORA-01790: expression must have same datatype as corresponding expression