SQL> --
SQL> -- drop tables
SQL> --
SQL> drop table R;
Table dropped.
SQL> drop table S;
Table dropped.
SQL> drop table T;
Table dropped.
SQL> drop table U;
Table dropped.
SQL> drop table V;
Table dropped.
SQL> --
SQL> -- create tables
SQL> --
SQL> create table R (A1 char(3), A2 char(3), A3 char(3));
Table created.
SQL> create table S (A1 char(3), A2 char(3), A3 char(3));
Table created.
SQL> create table T (B1 char(3), B2 char(3), B3 char(3));
Table created.
SQL> create table U (C1 char(3), C2 char(3));
Table created.
SQL> create table V (D1 char(3), A2 char(3));
Table created.
SQL> --
SQL> -- create data
SQL> --
SQL> insert into R (A1, A2, A3) values('v1', 'v2', 'v3');
1 row created.
SQL> insert into R (A1, A2, A3) values('v4', 'v5', 'v6');
1 row created.
SQL> insert into S (A1, A2, A3) values('v7', 'v8', 'v9');
1 row created.
SQL> insert into S (A1, A2, A3) values('v1', 'v2', 'v3');
1 row created.
SQL> insert into T (B1, B2, B3) values('v10', 'v11', 'v12');
1 row created.
SQL> insert into T (B1, B2, B3) values('v13', 'v14', 'v15');
1 row created.
SQL> insert into U (C1, C2) values('v5', 'v11');
1 row created.
SQL> insert into U (C1, C2) values('v8', 'v12');
1 row created.
SQL> insert into V (D1, A2) values('v16', 'v2');
1 row created.
SQL> insert into V (D1, A2) values('v17', 'v3');
1 row created.
SQL> --
SQL> -- Table R
SQL> --
SQL> SELECT * FROM R;
A1 A2 A3
--- --- ---
v1 v2 v3
v4 v5 v6
SQL> --
SQL> -- Table S
SQL> --
SQL> SELECT * FROM S;
A1 A2 A3
--- --- ---
v7 v8 v9
v1 v2 v3
SQL> --
SQL> -- Table T
SQL> --
SQL> SELECT * FROM T;
B1 B2 B3
--- --- ---
v10 v11 v12
v13 v14 v15
SQL> --
SQL> -- Table U
SQL> --
SQL> SELECT * FROM U;
C1 C2
--- ---
v5 v11
v8 v12
SQL> --
SQL> -- Table V
SQL> --
SQL> SELECT * FROM V;
D1 A2
--- ---
v16 v2
v17 v3
SQL> --
SQL> --------------------------------------------------
SQL> --
SQL> -- Selection
SQL> --
SQL> SELECT * FROM R r WHERE r.A2='v5';
A1 A2 A3
--- --- ---
v4 v5 v6
SQL> --
SQL> -- Projection
SQL> --
SQL> SELECT A1, A3 FROM R;
A1 A3
--- ---
v1 v3
v4 v6
SQL> --
SQL> -- Rename
SQL> --
SQL> SELECT r.A1 AS Col1, r.A2 AS Col2, r.A3 AS Col3 FROM R r;
COL COL COL
--- --- ---
v1 v2 v3
v4 v5 v6
SQL> --
SQL> -- Cartesian Product - Cross Join
SQL> --
SQL> SELECT * FROM R, U;
A1 A2 A3 C1 C2
--- --- --- --- ---
v1 v2 v3 v5 v11
v1 v2 v3 v8 v12
v4 v5 v6 v5 v11
v4 v5 v6 v8 v12
SQL> SELECT * FROM R CROSS JOIN U;
A1 A2 A3 C1 C2
--- --- --- --- ---
v1 v2 v3 v5 v11
v1 v2 v3 v8 v12
v4 v5 v6 v5 v11
v4 v5 v6 v8 v12
SQL> --
SQL> -- Set Union
SQL> --
SQL> SELECT * FROM R UNION ALL SELECT * FROM S;
A1 A2 A3
--- --- ---
v1 v2 v3
v4 v5 v6
v7 v8 v9
v1 v2 v3
SQL> SELECT * FROM R UNION SELECT * FROM S;
A1 A2 A3
--- --- ---
v1 v2 v3
v4 v5 v6
v7 v8 v9
SQL> --
SQL> -- Set Difference
SQL> --
SQL> SELECT * FROM R r WHERE r.A2 NOT IN (SELECT s.A2 FROM S s);
A1 A2 A3
--- --- ---
v4 v5 v6
SQL> SELECT * FROM R MINUS SELECT * FROM S;
A1 A2 A3
--- --- ---
v4 v5 v6
SQL> --
SQL> -- Set Intersection
SQL> --
SQL> SELECT * FROM R r WHERE r.A2 IN (SELECT s.A2 FROM S s);
A1 A2 A3
--- --- ---
v1 v2 v3
SQL> SELECT * FROM R INTERSECT SELECT * FROM S;
A1 A2 A3
--- --- ---
v1 v2 v3
SQL> --
SQL> -- Anti Join == Set Differecnce
SQL> --
SQL> -- Inner Join
SQL> --
SQL> SELECT * FROM R r, U u WHERE r.A2 = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
SQL> SELECT * FROM R r INNER JOIN U u ON r.A2 = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
SQL> SELECT * FROM R r JOIN U u ON r.A2 = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
SQL> -- Empty
SQL> SELECT * FROM R r INNER JOIN S s ON r.A2 = s.A2 INNER JOIN T t ON r.A2 = t.B1;
no rows selected
SQL> --
SQL> -- Semi Join
SQL> --
SQL> SELECT r.* FROM R r, U u WHERE r.A2 = u.C1;
A1 A2 A3
--- --- ---
v4 v5 v6
SQL> SELECT r.* FROM R r INNER JOIN U u ON r.A2 = u.C1;
A1 A2 A3
--- --- ---
v4 v5 v6
SQL> SELECT r.* FROM R r JOIN U u ON r.A2 = u.C1;
A1 A2 A3
--- --- ---
v4 v5 v6
SQL> --
SQL> -- Theta Join
SQL> --
SQL> SELECT * FROM R r, U u WHERE r.A2 < u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v1 v2 v3 v5 v11
v1 v2 v3 v8 v12
v4 v5 v6 v8 v12
SQL> SELECT * FROM R r INNER JOIN U u ON r.A2 < u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v1 v2 v3 v5 v11
v1 v2 v3 v8 v12
v4 v5 v6 v8 v12
SQL> SELECT * FROM R r JOIN U u ON r.A2 < u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v1 v2 v3 v5 v11
v1 v2 v3 v8 v12
v4 v5 v6 v8 v12
SQL> --
SQL> -- Self Join
SQL> --
SQL> SELECT * FROM R r1, R r2 WHERE r1.A1 < r2.A1;
A1 A2 A3 A1 A2 A3
--- --- --- --- --- ---
v1 v2 v3 v4 v5 v6
SQL> SELECT * FROM R r1 INNER JOIN R r2 ON r1.A1 < r2.A1;
A1 A2 A3 A1 A2 A3
--- --- --- --- --- ---
v1 v2 v3 v4 v5 v6
SQL> SELECT * FROM R r1 JOIN R r2 ON r1.A1 < r2.A1;
A1 A2 A3 A1 A2 A3
--- --- --- --- --- ---
v1 v2 v3 v4 v5 v6
SQL> --
SQL> -- Natural Join
SQL> --
SQL> SELECT r.A2, r.A1, r.A3, v.D1 FROM R r, V v WHERE r.A2 = v.A2;
A2 A1 A3 D1
--- --- --- ---
v2 v1 v3 v16
SQL> SELECT * FROM R NATURAL JOIN V;
A2 A1 A3 D1
--- --- --- ---
v2 v1 v3 v16
SQL> --
SQL> -- Left Outer Join
SQL> --
SQL> SELECT * FROM R r, U u WHERE r.A2 = u.C1(+);
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
v1 v2 v3
SQL> SELECT * FROM R r LEFT OUTER JOIN U u ON r.A2 = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
v1 v2 v3
SQL> SELECT * FROM R r LEFT JOIN U u ON r.A2 = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
v1 v2 v3
SQL> --
SQL> -- Right Outer Join
SQL> --
SQL> SELECT * FROM R r, U u WHERE r.A2(+) = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
v8 v12
SQL> SELECT * FROM R r RIGHT OUTER JOIN U u ON r.A2 = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
v8 v12
SQL> SELECT * FROM R r RIGHT JOIN U u ON r.A2 = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
v8 v12
SQL> --
SQL> -- Full Outer Join
SQL> --
SQL> SELECT * FROM R r, U u WHERE r.A2 = u.C1(+) UNION SELECT * FROM R r, U u WHERE r.A2(+) = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v1 v2 v3
v4 v5 v6 v5 v11
v8 v12
SQL> SELECT * FROM R r FULL OUTER JOIN U u ON r.A2 = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
v1 v2 v3
v8 v12
SQL> SELECT * FROM R r FULL JOIN U u ON r.A2 = u.C1;
A1 A2 A3 C1 C2
--- --- --- --- ---
v4 v5 v6 v5 v11
v1 v2 v3
v8 v12
SQL> --
SQL> --
SQL> --
SQL> spool off