18 INSERT INTO J1_TBL VALUES (1, 4, 'one');
19 INSERT INTO J1_TBL VALUES (2, 3, 'two');
20 INSERT INTO J1_TBL VALUES (3, 2, 'three');
21 INSERT INTO J1_TBL VALUES (4, 1, 'four');
22 INSERT INTO J1_TBL VALUES (5, 0, 'five');
23 INSERT INTO J1_TBL VALUES (6, 6, 'six');
24 INSERT INTO J1_TBL VALUES (7, 7, 'seven');
25 INSERT INTO J1_TBL VALUES (8, 8, 'eight');
26 INSERT INTO J1_TBL VALUES (0, NULL, 'zero');
27 INSERT INTO J1_TBL VALUES (NULL, NULL, 'null');
28 INSERT INTO J1_TBL VALUES (NULL, 0, 'zero');
30 INSERT INTO J2_TBL VALUES (1, -1);
31 INSERT INTO J2_TBL VALUES (2, 2);
32 INSERT INTO J2_TBL VALUES (3, -3);
33 INSERT INTO J2_TBL VALUES (2, 4);
34 INSERT INTO J2_TBL VALUES (5, -5);
35 INSERT INTO J2_TBL VALUES (5, -5);
36 INSERT INTO J2_TBL VALUES (0, NULL);
37 INSERT INTO J2_TBL VALUES (NULL, NULL);
38 INSERT INTO J2_TBL VALUES (NULL, 0);
42 -- Make sure that table/column aliases are supported
43 -- before diving into more complex join syntax.
53 FROM J1_TBL AS t1 (a, b, c);
56 FROM J1_TBL t1 (a, b, c);
59 FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e);
61 SELECT '' AS "xxx", t1.a, t2.e
62 FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e)
68 -- Qualifications are not allowed on cross joins,
69 -- which degenerate into a standard unqualified inner join.
73 FROM J1_TBL CROSS JOIN J2_TBL;
76 SELECT '' AS "xxx", i, k, t
77 FROM J1_TBL CROSS JOIN J2_TBL;
79 -- resolve previous ambiguity by specifying the table name
80 SELECT '' AS "xxx", t1.i, k, t
81 FROM J1_TBL t1 CROSS JOIN J2_TBL t2;
83 SELECT '' AS "xxx", ii, tt, kk
84 FROM (J1_TBL CROSS JOIN J2_TBL)
85 AS tx (ii, jj, tt, ii2, kk);
87 SELECT '' AS "xxx", tx.ii, tx.jj, tx.kk
88 FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e))
89 AS tx (ii, jj, tt, ii2, kk);
92 FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b;
97 -- Inner joins (equi-joins)
102 -- Inner joins (equi-joins) with USING clause
103 -- The USING syntax changes the shape of the resulting table
104 -- by including a column in the USING clause only once in the result.
107 -- Inner equi-join on specified column
108 SELECT '' AS "xxx", *
109 FROM J1_TBL INNER JOIN J2_TBL USING (i);
111 -- Same as above, slightly different syntax
112 SELECT '' AS "xxx", *
113 FROM J1_TBL JOIN J2_TBL USING (i);
115 SELECT '' AS "xxx", *
116 FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a)
119 SELECT '' AS "xxx", *
120 FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
126 -- Inner equi-join on all columns with the same name
129 SELECT '' AS "xxx", *
130 FROM J1_TBL NATURAL JOIN J2_TBL;
132 SELECT '' AS "xxx", *
133 FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
135 SELECT '' AS "xxx", *
136 FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
138 -- mismatch number of columns
139 -- currently, Postgres will fill in with underlying names
140 SELECT '' AS "xxx", *
141 FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
145 -- Inner joins (equi-joins)
148 SELECT '' AS "xxx", *
149 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
151 SELECT '' AS "xxx", *
152 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
159 SELECT '' AS "xxx", *
160 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k);
165 -- Note that OUTER is a noise word
168 SELECT '' AS "xxx", *
169 FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i);
171 SELECT '' AS "xxx", *
172 FROM J1_TBL LEFT JOIN J2_TBL USING (i);
174 SELECT '' AS "xxx", *
175 FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
177 SELECT '' AS "xxx", *
178 FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
180 SELECT '' AS "xxx", *
181 FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i);
183 SELECT '' AS "xxx", *
184 FROM J1_TBL FULL JOIN J2_TBL USING (i);
186 SELECT '' AS "xxx", *
187 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);
189 SELECT '' AS "xxx", *
190 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1);
194 -- More complicated constructs
197 -- UNION JOIN isn't implemented yet
198 SELECT '' AS "xxx", *
199 FROM J1_TBL UNION JOIN J2_TBL;