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;

SELECT * FROM citizen;

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;

SELECT * FROM postalcode;

-- CROSS JOIN SELECT * FROM citizen CROSS JOIN postalcode

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

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

-- 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

-- 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

-- 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

-- 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

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

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

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

SELECT * FROM citizen c CROSS JOIN postalcode p WHERE c.name = 'Smith' AND p.area = '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';

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';

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

SELECT * FROM citizen WHERE name = 'Smith' UNION SELECT * FROM postalcode WHERE area = 'Moon';