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)
172 SELECT '' AS "xxx", *
173 FROM J1_TBL LEFT JOIN J2_TBL USING (i)
176 SELECT '' AS "xxx", *
177 FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
179 SELECT '' AS "xxx", *
180 FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
182 SELECT '' AS "xxx", *
183 FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i)
186 SELECT '' AS "xxx", *
187 FROM J1_TBL FULL JOIN J2_TBL USING (i)
190 SELECT '' AS "xxx", *
191 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);
193 SELECT '' AS "xxx", *
194 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1);
198 -- More complicated constructs
201 -- UNION JOIN isn't implemented yet
202 SELECT '' AS "xxx", *
203 FROM J1_TBL UNION JOIN J2_TBL;
206 -- Multiway full join
209 CREATE TABLE t1 (name TEXT, n INTEGER);
210 CREATE TABLE t2 (name TEXT, n INTEGER);
211 CREATE TABLE t3 (name TEXT, n INTEGER);
213 INSERT INTO t1 VALUES ( 'aa', 11 );
214 INSERT INTO t2 VALUES ( 'aa', 12 );
215 INSERT INTO t2 VALUES ( 'bb', 22 );
216 INSERT INTO t2 VALUES ( 'dd', 42 );
217 INSERT INTO t3 VALUES ( 'aa', 13 );
218 INSERT INTO t3 VALUES ( 'bb', 23 );
219 INSERT INTO t3 VALUES ( 'cc', 33 );
221 SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name);
224 -- Test interactions of join syntax and subqueries
227 -- Basic cases (we expect planner to pull up the subquery here)
229 (SELECT * FROM t2) as s2
231 (SELECT * FROM t3) s3
235 (SELECT * FROM t2) as s2
237 (SELECT * FROM t3) s3
241 (SELECT * FROM t2) as s2
243 (SELECT * FROM t3) s3
246 -- Cases with non-nullable expressions in subquery results;
247 -- make sure these go to null as expected
249 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
251 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
254 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
256 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
259 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
261 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
264 (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1
266 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
268 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
271 (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1
273 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
275 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
278 (SELECT name, n as s1_n FROM t1) as s1
281 (SELECT name, n as s2_n FROM t2) as s2
283 (SELECT name, n as s3_n FROM t3) as s3
287 (SELECT name, n as s1_n FROM t1) as s1
290 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
292 (SELECT name, n as s3_n FROM t3) as s3
296 -- Test for propagation of nullability constraints into sub-joins
298 create temp table x (x1 int, x2 int);
299 insert into x values (1,11);
300 insert into x values (2,22);
301 insert into x values (3,null);
302 insert into x values (4,44);
303 insert into x values (5,null);
305 create temp table y (y1 int, y2 int);
306 insert into y values (1,111);
307 insert into y values (2,222);
308 insert into y values (3,333);
309 insert into y values (4,null);
314 select * from x left join y on (x1 = y1 and x2 is not null);
315 select * from x left join y on (x1 = y1 and y2 is not null);
317 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
319 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
320 on (x1 = xx1 and x2 is not null);
321 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
322 on (x1 = xx1 and y2 is not null);
323 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
324 on (x1 = xx1 and xx2 is not null);
325 -- these should NOT give the same answers as above
326 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
327 on (x1 = xx1) where (x2 is not null);
328 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
329 on (x1 = xx1) where (y2 is not null);
330 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
331 on (x1 = xx1) where (xx2 is not null);
334 -- regression test: check for bug with propagation of implied equality
337 select count(*) from tenk1 a where unique1 in
338 (select unique1 from tenk1 b join tenk1 c using (unique1)
339 where b.unique2 = 42);