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