6 CREATE TABLE JOIN1_TBL (
12 CREATE TABLE JOIN2_TBL (
17 CREATE TABLE JOIN3_TBL (
23 CREATE TABLE JOIN4_TBL (
28 INSERT INTO JOIN1_TBL VALUES (1, 3, 'one');
29 INSERT INTO JOIN1_TBL VALUES (2, 2, 'two');
30 INSERT INTO JOIN1_TBL VALUES (3, 1, 'three');
31 INSERT INTO JOIN1_TBL VALUES (4, 0, 'four');
33 INSERT INTO JOIN2_TBL VALUES (1, -1);
34 INSERT INTO JOIN2_TBL VALUES (2, 2);
35 INSERT INTO JOIN2_TBL VALUES (3, -3);
36 INSERT INTO JOIN2_TBL VALUES (2, 4);
41 -- Qualifications are not allowed on cross joins,
42 -- which degenerate into a standard unqualified inner join.
46 FROM JOIN1_TBL CROSS JOIN JOIN2_TBL;
48 SELECT '' AS "xxx", i, k, t
49 FROM JOIN1_TBL CROSS JOIN JOIN2_TBL;
51 SELECT '' AS "xxx", ii, tt, kk
52 FROM JOIN1_TBL CROSS JOIN JOIN2_TBL AS JT (ii, jj, tt, ii2, kk);
54 SELECT '' AS "xxx", jt.ii, jt.jj, jt.kk
55 FROM JOIN1_TBL CROSS JOIN JOIN2_TBL AS JT (ii, jj, tt, ii2, kk);
60 -- Inner joins (equi-joins)
65 -- Inner joins (equi-joins) with USING clause
66 -- The USING syntax changes the shape of the resulting table
67 -- by including a column in the USING clause only once in the result.
70 -- Inner equi-join on all columns with the same name
72 FROM JOIN1_TBL NATURAL JOIN JOIN2_TBL;
74 -- Inner equi-join on specified column
76 FROM JOIN1_TBL INNER JOIN JOIN2_TBL USING (i);
78 -- Same as above, slightly different syntax
80 FROM JOIN1_TBL JOIN JOIN2_TBL USING (i);
84 -- Inner joins (equi-joins)
88 FROM JOIN1_TBL JOIN JOIN2_TBL ON (JOIN1_TBL.i = JOIN2_TBL.i);
91 FROM JOIN1_TBL JOIN JOIN2_TBL ON (JOIN1_TBL.i = JOIN2_TBL.k);
94 FROM JOIN1_TBL CROSS JOIN JOIN2_TBL;
101 SELECT '' AS "xxx", *
102 FROM JOIN1_TBL JOIN JOIN2_TBL ON (JOIN1_TBL.i <= JOIN2_TBL.k);
109 SELECT '' AS "xxx", *
110 FROM JOIN1_TBL OUTER JOIN JOIN2_TBL USING (i);
112 SELECT '' AS "xxx", *
113 FROM JOIN1_TBL LEFT OUTER JOIN JOIN2_TBL USING (i);
115 SELECT '' AS "xxx", *
116 FROM JOIN1_TBL RIGHT OUTER JOIN JOIN2_TBL USING (i);
118 SELECT '' AS "xxx", *
119 FROM JOIN1_TBL FULL OUTER JOIN JOIN2_TBL USING (i);
123 -- More complicated constructs
130 DROP TABLE JOIN1_TBL;
131 DROP TABLE JOIN2_TBL;