Lines Matching full:join
18 # $Id: join.test,v 1.11 2003/09/27 13:39:40 drh Exp $
23 do_test join-1.1 {
32 do_test join-1.2 {
42 do_test join-1.3 {
44 SELECT * FROM t1 NATURAL JOIN t2;
47 do_test join-1.3.1 {
49 SELECT * FROM t2 NATURAL JOIN t1;
52 do_test join-1.4 {
54 SELECT * FROM t1 INNER JOIN t2 USING(b,c);
57 do_test join-1.5 {
59 SELECT * FROM t1 INNER JOIN t2 USING(b);
62 do_test join-1.6 {
64 SELECT * FROM t1 INNER JOIN t2 USING(c);
67 do_test join-1.7 {
69 SELECT * FROM t1 INNER JOIN t2 USING(c,b);
73 do_test join-1.8 {
75 SELECT * FROM t1 NATURAL CROSS JOIN t2;
78 do_test join-1.9 {
80 SELECT * FROM t1 CROSS JOIN t2 USING(b,c);
83 do_test join-1.10 {
85 SELECT * FROM t1 NATURAL INNER JOIN t2;
88 do_test join-1.11 {
90 SELECT * FROM t1 INNER JOIN t2 USING(b,c);
93 do_test join-1.12 {
95 SELECT * FROM t1 natural inner join t2;
98 do_test join-1.13 {
100 SELECT * FROM t1 NATURAL JOIN
104 do_test join-1.14 {
107 NATURAL JOIN t1
111 do_test join-1.15 {
120 do_test join-1.16 {
122 SELECT * FROM t1 natural join t2 natural join t3;
125 do_test join-1.17 {
127 SELECT * FROM t1 natural join t2 natural join t3;
130 do_test join-1.18 {
139 do_test join-1.19 {
141 SELECT * FROM t1 natural join t2 natural join t4;
144 do_test join-1.19 {
146 SELECT * FROM t1 natural join t2 natural join t4;
149 do_test join-1.20 {
151 SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1
155 do_test join-2.1 {
157 SELECT * FROM t1 NATURAL LEFT JOIN t2;
160 do_test join-2.2 {
162 SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
165 do_test join-2.3 {
167 SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
170 do_test join-2.4 {
172 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
175 do_test join-2.5 {
177 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
180 do_test join-2.6 {
182 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
186 do_test join-3.1 {
188 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
190 } {1 {a NATURAL join may not have an ON or USING clause}}
191 do_test join-3.2 {
193 SELECT * FROM t1 NATURAL JOIN t2 USING(b);
195 } {1 {a NATURAL join may not have an ON or USING clause}}
196 do_test join-3.3 {
198 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
200 } {1 {cannot have both ON and USING clauses in the same join}}
201 do_test join-3.4 {
203 SELECT * FROM t1 JOIN t2 USING(a);
205 } {1 {cannot join using column a - column not present in both tables}}
206 do_test join-3.5 {
211 do_test join-3.6 {
213 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
216 do_test join-3.7 {
218 SELECT * FROM t1 INNER OUTER JOIN t2;
220 } {1 {unknown or unsupported join type: INNER OUTER}}
221 do_test join-3.7 {
223 SELECT * FROM t1 LEFT BOGUS JOIN t2;
225 } {1 {unknown or unsupported join type: LEFT BOGUS}}
227 do_test join-4.1 {
243 SELECT * FROM t6 NATURAL JOIN t5;
246 do_test join-4.2 {
251 do_test join-4.3 {
256 do_test join-4.4 {
259 SELECT * FROM t6 NATURAL JOIN t5;
262 do_test join-4.6 {
267 do_test join-4.7 {
272 do_test join-4.8 {
275 SELECT * FROM t6 NATURAL JOIN t5;
278 do_test join-4.9 {
283 do_test join-4.10 {
289 do_test join-5.1 {
302 usuarios left outer join centros on usuarios.idcentro = centros.id;
308 do_test join-7.1 {
327 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
331 # Make sure a left join where the right table is really a view that
332 # is itself a join works right. Ticket #306.
334 do_test join-8.1 {
348 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
351 do_test join-8.2 {
353 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
357 do_test join-8.3 {
359 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
363 # Ticket #350 describes a scenario where LEFT OUTER JOIN does not
364 # function correctly if the right table in the join is really
367 # To test the problem, we generate the same LEFT OUTER JOIN in two
373 do_test join-9.1 {
382 SELECT * FROM t12 NATURAL LEFT JOIN t13
384 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
387 do_test join-9.2 {
390 SELECT * FROM t12 NATURAL LEFT JOIN t13
392 SELECT * FROM t12 NATURAL LEFT JOIN v13;