clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1044276 fiddles created (9443 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
 hidden batch(es)