« back to all changes in this revision
Viewing changes to tests/r/pbxt/join_outer.result
- browse files at revision 505
- compare with another revision
- compare with revision 518
- download diff
- download tarball
- view history from revision 505
- Committer: Brian Aker
- Date: 2008-10-11 18:46:03 UTC
- mfrom: (499.1.1 codestyle)
- Revision ID: brian@tangent.org-20081011184603-acnvl454409fzhvq
Merging Monty
- files added:
- tests/r/pbxt
- files modified:
- client/drizzletest.cc
added
removed
tests/r/pbxt/join_outer.result
1
drop table if exists t0,t1,t2,t3,t4,t5;
2
CREATE TABLE t1 (
3
grp int default NULL,
4
a bigint default NULL,
5
c char(10) NOT NULL default ''
6
) ENGINE=MyISAM;
7
INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
8
create table t2 (id int, a bigint not null, c char(10), d int, primary key (a));
9
insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
10
select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a;
11
grp a c id a c d
12
1 1 a 1 1 a 1
13
3 4 E 3 4 A 4
14
3 5 C 3 5 B 5
15
3 6 D 3 6 C 6
16
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
17
grp a c id a c d
18
NULL NULL NULL NULL NULL NULL
19
1 1 a 1 1 a 1
20
2 2 b NULL NULL NULL NULL
21
2 3 c NULL NULL NULL NULL
22
3 4 E 3 4 A 4
23
3 5 C 3 5 B 5
24
3 6 D 3 6 C 6
25
select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
26
grp a c id a c d
27
1 1 a 1 1 a 1
28
3 4 E 3 4 A 4
29
3 5 C 3 5 B 5
30
3 6 D 3 6 C 6
31
NULL NULL NULL 4 7 D 7
32
select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2;
33
grp a c id a c d
34
1 1 a 1 1 a 1
35
3 4 E 3 4 A 4
36
3 5 C 3 5 B 5
37
3 6 D 3 6 C 6
38
NULL NULL NULL 4 7 D 7
39
select t1.*,t2.* from t1 left join t2 using (a);
40
grp a c id a c d
41
1 1 a 1 1 a 1
42
2 2 b NULL NULL NULL NULL
43
2 3 c NULL NULL NULL NULL
44
3 4 E 3 4 A 4
45
3 5 C 3 5 B 5
46
3 6 D 3 6 C 6
47
NULL NULL NULL NULL NULL NULL
48
select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
49
grp a c id a c d
50
1 1 a 1 1 a 1
51
3 4 E 3 4 A 4
52
3 5 C 3 5 B 5
53
3 6 D 3 6 C 6
54
select t1.*,t2.* from t1 left join t2 using (a,c);
55
grp a c id a c d
56
1 1 a 1 1 a 1
57
2 2 b NULL NULL NULL NULL
58
2 3 c NULL NULL NULL NULL
59
3 4 E NULL NULL NULL NULL
60
3 5 C NULL NULL NULL NULL
61
3 6 D NULL NULL NULL NULL
62
NULL NULL NULL NULL NULL NULL
63
select t1.*,t2.* from t1 left join t2 using (c);
64
grp a c id a c d
65
1 1 a 1 1 a 1
66
1 1 a 3 4 A 4
67
2 2 b 3 5 B 5
68
2 3 c 3 6 C 6
69
3 4 E NULL NULL NULL NULL
70
3 5 C 3 6 C 6
71
3 6 D 4 7 D 7
72
NULL NULL NULL NULL NULL NULL
73
select t1.*,t2.* from t1 natural left outer join t2;
74
grp a c id a c d
75
1 1 a 1 1 a 1
76
2 2 b NULL NULL NULL NULL
77
2 3 c NULL NULL NULL NULL
78
3 4 E NULL NULL NULL NULL
79
3 5 C NULL NULL NULL NULL
80
3 6 D NULL NULL NULL NULL
81
NULL NULL NULL NULL NULL NULL
82
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
83
grp a c id a c d
84
3 4 E 3 4 A 4
85
3 5 C 3 5 B 5
86
3 6 D 3 6 C 6
87
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
88
grp a c id a c d
89
2 2 b NULL NULL NULL NULL
90
2 3 c NULL NULL NULL NULL
91
NULL NULL NULL NULL NULL NULL
92
explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1;
93
id select_type table type possible_keys key key_len ref rows Extra
94
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
95
explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
96
id select_type table type possible_keys key key_len ref rows Extra
97
1 SIMPLE t1 ALL NULL NULL NULL NULL 7
98
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.a 1 Using where
99
select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
100
grp a c id a c d a
101
1 1 a 1 1 a 1 1
102
2 2 b NULL NULL NULL NULL NULL
103
2 3 c NULL NULL NULL NULL NULL
104
3 4 E 3 4 A 4 4
105
3 5 C 3 5 B 5 5
106
3 6 D 3 6 C 6 6
107
NULL NULL NULL NULL NULL NULL NULL
108
explain select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
109
ERROR 42S22: Unknown column 't3.a' in 'on clause'
110
select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
111
ERROR 42S22: Unknown column 't3.a' in 'on clause'
112
select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
113
ERROR 42S22: Unknown column 't3.a' in 'on clause'
114
select t1.*,t2.* from t1 inner join t2 using (a);
115
grp a c id a c d
116
1 1 a 1 1 a 1
117
3 4 E 3 4 A 4
118
3 5 C 3 5 B 5
119
3 6 D 3 6 C 6
120
select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
121
grp a c id a c d
122
1 1 a 1 1 a 1
123
3 4 E 3 4 A 4
124
3 5 C 3 5 B 5
125
3 6 D 3 6 C 6
126
select t1.*,t2.* from t1 natural join t2;
127
grp a c id a c d
128
1 1 a 1 1 a 1
129
drop table t1,t2;
130
CREATE TABLE t1 (
131
usr_id INT NOT NULL,
132
uniq_id INT NOT NULL AUTO_INCREMENT,
133
start_num INT NOT NULL DEFAULT 1,
134
increment INT NOT NULL DEFAULT 1,
135
PRIMARY KEY (uniq_id),
136
INDEX usr_uniq_idx (usr_id, uniq_id),
137
INDEX uniq_usr_idx (uniq_id, usr_id)
138
);
139
CREATE TABLE t2 (
140
id INT NOT NULL DEFAULT 0,
141
usr2_id INT NOT NULL DEFAULT 0,
142
max INT NOT NULL DEFAULT 0,
143
c_amount INT NOT NULL DEFAULT 0,
144
d_max INT NOT NULL DEFAULT 0,
145
d_num INT NOT NULL DEFAULT 0,
146
orig_time INT NOT NULL DEFAULT 0,
147
c_time INT NOT NULL DEFAULT 0,
148
active ENUM ("no","yes") NOT NULL,
149
PRIMARY KEY (id,usr2_id),
150
INDEX id_idx (id),
151
INDEX usr2_idx (usr2_id)
152
);
153
INSERT INTO t1 VALUES (3,NULL,0,50),(3,NULL,0,200),(3,NULL,0,25),(3,NULL,0,84676),(3,NULL,0,235),(3,NULL,0,10),(3,NULL,0,3098),(3,NULL,0,2947),(3,NULL,0,8987),(3,NULL,0,8347654),(3,NULL,0,20398),(3,NULL,0,8976),(3,NULL,0,500),(3,NULL,0,198);
154
SELECT t1.usr_id,t1.uniq_id,t1.increment,
155
t2.usr2_id,t2.c_amount,t2.max
156
FROM t1
157
LEFT JOIN t2 ON t2.id = t1.uniq_id
158
WHERE t1.uniq_id = 4
159
ORDER BY t2.c_amount;
160
usr_id uniq_id increment usr2_id c_amount max
161
3 4 84676 NULL NULL NULL
162
SELECT t1.usr_id,t1.uniq_id,t1.increment,
163
t2.usr2_id,t2.c_amount,t2.max
164
FROM t2
165
RIGHT JOIN t1 ON t2.id = t1.uniq_id
166
WHERE t1.uniq_id = 4
167
ORDER BY t2.c_amount;
168
usr_id uniq_id increment usr2_id c_amount max
169
3 4 84676 NULL NULL NULL
170
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
171
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
172
ERROR 23000: Duplicate entry '2-3' for key 'PRIMARY'
173
INSERT INTO t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
174
SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 ORDER BY t2.c_amount;
175
usr_id uniq_id increment usr2_id c_amount max
176
3 4 84676 NULL NULL NULL
177
SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 GROUP BY t2.c_amount;
178
usr_id uniq_id increment usr2_id c_amount max
179
3 4 84676 NULL NULL NULL
180
SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4;
181
usr_id uniq_id increment usr2_id c_amount max
182
3 4 84676 NULL NULL NULL
183
drop table t1,t2;
184
CREATE TABLE t1 (
185
cod_asig int DEFAULT '0' NOT NULL,
186
desc_larga_cat varchar(80) DEFAULT '' NOT NULL,
187
desc_larga_cas varchar(80) DEFAULT '' NOT NULL,
188
desc_corta_cat varchar(40) DEFAULT '' NOT NULL,
189
desc_corta_cas varchar(40) DEFAULT '' NOT NULL,
190
cred_total double(3,1) DEFAULT '0.0' NOT NULL,
191
pre_requisit int,
192
co_requisit int,
193
preco_requisit int,
194
PRIMARY KEY (cod_asig)
195
);
196
INSERT INTO t1 VALUES (10360,'asdfggfg','Introduccion a los Ordenadores I','asdfggfg','Introduccio Ordinadors I',6.0,NULL,NULL,NULL);
197
INSERT INTO t1 VALUES (10361,'Components i Circuits Electronics I','Componentes y Circuitos Electronicos I','Components i Circuits Electronics I','Comp. i Circ. Electr. I',6.0,NULL,NULL,NULL);
198
INSERT INTO t1 VALUES (10362,'Laboratori d`Ordinadors','Laboratorio de Ordenadores','Laboratori d`Ordinadors','Laboratori Ordinadors',4.5,NULL,NULL,NULL);
199
INSERT INTO t1 VALUES (10363,'Tecniques de Comunicacio Oral i Escrita','Tecnicas de Comunicacion Oral y Escrita','Tecniques de Comunicacio Oral i Escrita','Tec. Com. Oral i Escrita',4.5,NULL,NULL,NULL);
200
INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
201
INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
202
INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
203
INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
204
CREATE TABLE t2 (
205
idAssignatura int DEFAULT '0' NOT NULL,
206
Grup int DEFAULT '0' NOT NULL,
207
Places int DEFAULT '0' NOT NULL,
208
PlacesOcupades int DEFAULT '0',
209
PRIMARY KEY (idAssignatura,Grup)
210
);
211
INSERT INTO t2 VALUES (10360,12,333,0);
212
INSERT INTO t2 VALUES (10361,30,2,0);
213
INSERT INTO t2 VALUES (10361,40,3,0);
214
INSERT INTO t2 VALUES (10360,45,10,0);
215
INSERT INTO t2 VALUES (10362,10,12,0);
216
INSERT INTO t2 VALUES (10360,55,2,0);
217
INSERT INTO t2 VALUES (10360,70,0,0);
218
INSERT INTO t2 VALUES (10360,565656,0,0);
219
INSERT INTO t2 VALUES (10360,32767,7,0);
220
INSERT INTO t2 VALUES (10360,33,8,0);
221
INSERT INTO t2 VALUES (10360,7887,85,0);
222
INSERT INTO t2 VALUES (11405,88,8,0);
223
INSERT INTO t2 VALUES (10360,0,55,0);
224
INSERT INTO t2 VALUES (10360,99,0,0);
225
INSERT INTO t2 VALUES (11411,30,10,0);
226
INSERT INTO t2 VALUES (11404,0,0,0);
227
INSERT INTO t2 VALUES (10362,11,111,0);
228
INSERT INTO t2 VALUES (10363,33,333,0);
229
INSERT INTO t2 VALUES (11412,55,0,0);
230
INSERT INTO t2 VALUES (50003,66,6,0);
231
INSERT INTO t2 VALUES (11403,5,0,0);
232
INSERT INTO t2 VALUES (11406,11,11,0);
233
INSERT INTO t2 VALUES (11410,11410,131,0);
234
INSERT INTO t2 VALUES (11416,11416,32767,0);
235
INSERT INTO t2 VALUES (11409,0,0,0);
236
CREATE TABLE t3 (
237
id int NOT NULL auto_increment,
238
dni_pasaporte char(16) DEFAULT '' NOT NULL,
239
idPla int DEFAULT '0' NOT NULL,
240
cod_asig int DEFAULT '0' NOT NULL,
241
any int DEFAULT '0' NOT NULL,
242
quatrimestre int DEFAULT '0' NOT NULL,
243
estat char(1) DEFAULT 'M' NOT NULL,
244
PRIMARY KEY (id),
245
UNIQUE dni_pasaporte (dni_pasaporte,idPla),
246
UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre)
247
);
248
INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
249
CREATE TABLE t4 (
250
id int NOT NULL auto_increment,
251
papa int DEFAULT '0' NOT NULL,
252
fill int DEFAULT '0' NOT NULL,
253
idPla int DEFAULT '0' NOT NULL,
254
PRIMARY KEY (id),
255
KEY papa (idPla,papa),
256
UNIQUE papa_2 (idPla,papa,fill)
257
);
258
INSERT INTO t4 VALUES (1,-1,10360,1);
259
INSERT INTO t4 VALUES (2,-1,10361,1);
260
INSERT INTO t4 VALUES (3,-1,10362,1);
261
SELECT DISTINCT fill,desc_larga_cat,cred_total,Grup,Places,PlacesOcupades FROM t4 LEFT JOIN t3 ON t3.cod_asig=fill AND estat='S' AND dni_pasaporte='11111111' AND t3.idPla=1 , t2,t1 WHERE fill=t1.cod_asig AND Places>PlacesOcupades AND fill=idAssignatura AND t4.idPla=1 AND papa=-1;
262
fill desc_larga_cat cred_total Grup Places PlacesOcupades
263
10360 asdfggfg 6.0 0 55 0
264
10360 asdfggfg 6.0 12 333 0
265
10360 asdfggfg 6.0 33 8 0
266
10360 asdfggfg 6.0 45 10 0
267
10360 asdfggfg 6.0 55 2 0
268
10360 asdfggfg 6.0 7887 85 0
269
10360 asdfggfg 6.0 32767 7 0
270
10361 Components i Circuits Electronics I 6.0 30 2 0
271
10361 Components i Circuits Electronics I 6.0 40 3 0
272
10362 Laboratori d`Ordinadors 4.5 10 12 0
273
10362 Laboratori d`Ordinadors 4.5 11 111 0
274
SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ;
275
fill idPla
276
10360 NULL
277
10361 NULL
278
10362 NULL
279
INSERT INTO t3 VALUES (3,'1234',1,10360,98,1,'S');
280
SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ;
281
fill idPla
282
10360 1
283
10361 NULL
284
10362 NULL
285
drop table t1,t2,t3,test.t4;
286
CREATE TABLE t1 (
287
id int NOT NULL auto_increment,
288
name char(60) DEFAULT '' NOT NULL,
289
PRIMARY KEY (id)
290
);
291
INSERT INTO t1 VALUES (1,'Antonio Paz');
292
INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
293
INSERT INTO t1 VALUES (3,'Thimble Smith');
294
CREATE TABLE t2 (
295
id int NOT NULL auto_increment,
296
owner int DEFAULT '0' NOT NULL,
297
name char(60),
298
PRIMARY KEY (id)
299
);
300
INSERT INTO t2 VALUES (1,1,'El Gato');
301
INSERT INTO t2 VALUES (2,1,'Perrito');
302
INSERT INTO t2 VALUES (3,3,'Happy');
303
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner);
304
name name id
305
Antonio Paz El Gato 1
306
Antonio Paz Perrito 2
307
Lilliana Angelovska NULL NULL
308
Thimble Smith Happy 3
309
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
310
name name id
311
Lilliana Angelovska NULL NULL
312
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
313
id select_type table type possible_keys key key_len ref rows Extra
314
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
315
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Not exists
316
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
317
id select_type table type possible_keys key key_len ref rows Extra
318
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
319
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
320
select count(*) from t1 left join t2 on (t1.id = t2.owner);
321
count(*)
322
4
323
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner);
324
name name id
325
Antonio Paz El Gato 1
326
Antonio Paz Perrito 2
327
Lilliana Angelovska NULL NULL
328
Thimble Smith Happy 3
329
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
330
name name id
331
Lilliana Angelovska NULL NULL
332
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
333
id select_type table type possible_keys key key_len ref rows Extra
334
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
335
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Not exists
336
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
337
id select_type table type possible_keys key key_len ref rows Extra
338
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
339
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
340
select count(*) from t2 right join t1 on (t1.id = t2.owner);
341
count(*)
342
4
343
select t1.name, t2.name, t2.id,t3.id from t2 right join t1 on (t1.id = t2.owner) left join t1 as t3 on t3.id=t2.owner;
344
name name id id
345
Antonio Paz El Gato 1 1
346
Antonio Paz Perrito 2 1
347
Lilliana Angelovska NULL NULL NULL
348
Thimble Smith Happy 3 3
349
select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
350
name name id id
351
Antonio Paz El Gato 1 1
352
Antonio Paz Perrito 2 1
353
NULL NULL NULL 2
354
Thimble Smith Happy 3 3
355
select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
356
name name id owner id
357
Antonio Paz El Gato 1 1 1
358
Antonio Paz Perrito 2 1 1
359
NULL NULL NULL NULL 2
360
Thimble Smith Happy 3 3 3
361
drop table t1,t2;
362
create table t1 (id int not null, str char(10), index(str));
363
insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
364
select * from t1 where str is not null order by id;
365
id str
366
3 foo
367
4 bar
368
select * from t1 where str is null;
369
id str
370
1 NULL
371
2 NULL
372
drop table t1;
373
CREATE TABLE t1 (
374
t1_id bigint NOT NULL auto_increment,
375
PRIMARY KEY (t1_id)
376
);
377
CREATE TABLE t2 (
378
t2_id bigint NOT NULL auto_increment,
379
PRIMARY KEY (t2_id)
380
);
381
CREATE TABLE t3 (
382
t3_id bigint NOT NULL auto_increment,
383
PRIMARY KEY (t3_id)
384
);
385
CREATE TABLE t4 (
386
seq_0_id bigint DEFAULT '0' NOT NULL,
387
seq_1_id bigint DEFAULT '0' NOT NULL,
388
KEY seq_0_id (seq_0_id),
389
KEY seq_1_id (seq_1_id)
390
);
391
CREATE TABLE t5 (
392
seq_0_id bigint DEFAULT '0' NOT NULL,
393
seq_1_id bigint DEFAULT '0' NOT NULL,
394
KEY seq_1_id (seq_1_id),
395
KEY seq_0_id (seq_0_id)
396
);
397
insert into t1 values (1);
398
insert into t2 values (1);
399
insert into t3 values (1);
400
insert into t4 values (1,1);
401
insert into t5 values (1,1);
402
explain select * from t3 left join t4 on t4.seq_1_id = t2.t2_id left join t1 on t1.t1_id = t4.seq_0_id left join t5 on t5.seq_0_id = t1.t1_id left join t2 on t2.t2_id = t5.seq_1_id where t3.t3_id = 23;
403
ERROR 42S22: Unknown column 't2.t2_id' in 'on clause'
404
drop table t1,t2,t3,t4,t5;
405
create table t1 (n int, m int, o int, key(n));
406
create table t2 (n int not null, m int, o int, primary key(n));
407
insert into t1 values (1, 2, 11), (1, 2, 7), (2, 2, 8), (1,2,9),(1,3,9);
408
insert into t2 values (1, 2, 3),(2, 2, 8), (4,3,9),(3,2,10);
409
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
410
t1.m = t2.m where t1.n = 1;
411
n m o n m o
412
1 2 11 1 2 3
413
1 2 7 1 2 3
414
1 2 9 1 2 3
415
1 3 9 NULL NULL NULL
416
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
417
t1.m = t2.m where t1.n = 1 order by t1.o;
418
n m o n m o
419
1 2 7 1 2 3
420
1 2 9 1 2 3
421
1 3 9 NULL NULL NULL
422
1 2 11 1 2 3
423
drop table t1,t2;
424
CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
425
INSERT INTO t1 VALUES (1,'a',1);
426
INSERT INTO t1 VALUES (2,'b',1);
427
INSERT INTO t1 VALUES (3,'c',2);
428
CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));
429
INSERT INTO t2 VALUES (1,'x');
430
INSERT INTO t2 VALUES (2,'y');
431
INSERT INTO t2 VALUES (3,'z');
432
SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
433
id2
434
3
435
SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
436
id2
437
3
438
drop table t1,t2;
439
create table t1 ( color varchar(20), name varchar(20) );
440
insert into t1 values ( 'red', 'apple' );
441
insert into t1 values ( 'yellow', 'banana' );
442
insert into t1 values ( 'green', 'lime' );
443
insert into t1 values ( 'black', 'grape' );
444
insert into t1 values ( 'blue', 'blueberry' );
445
create table t2 ( count int, color varchar(20) );
446
insert into t2 values (10, 'green');
447
insert into t2 values (5, 'black');
448
insert into t2 values (15, 'white');
449
insert into t2 values (7, 'green');
450
select * from t1;
451
color name
452
red apple
453
yellow banana
454
green lime
455
black grape
456
blue blueberry
457
select * from t2;
458
count color
459
10 green
460
5 black
461
15 white
462
7 green
463
select * from t2 natural join t1;
464
color count name
465
green 10 lime
466
green 7 lime
467
black 5 grape
468
select t2.count, t1.name from t2 natural join t1;
469
count name
470
10 lime
471
7 lime
472
5 grape
473
select t2.count, t1.name from t2 inner join t1 using (color);
474
count name
475
10 lime
476
7 lime
477
5 grape
478
drop table t1;
479
drop table t2;
480
CREATE TABLE t1 (
481
pcode varchar(8) DEFAULT '' NOT NULL
482
);
483
INSERT INTO t1 VALUES ('kvw2000'),('kvw2001'),('kvw3000'),('kvw3001'),('kvw3002'),('kvw3500'),('kvw3501'),('kvw3502'),('kvw3800'),('kvw3801'),('kvw3802'),('kvw3900'),('kvw3901'),('kvw3902'),('kvw4000'),('kvw4001'),('kvw4002'),('kvw4200'),('kvw4500'),('kvw5000'),('kvw5001'),('kvw5500'),('kvw5510'),('kvw5600'),('kvw5601'),('kvw6000'),('klw1000'),('klw1020'),('klw1500'),('klw2000'),('klw2001'),('klw2002'),('kld2000'),('klw2500'),('kmw1000'),('kmw1500'),('kmw2000'),('kmw2001'),('kmw2100'),('kmw3000'),('kmw3200');
484
CREATE TABLE t2 (
485
pcode varchar(8) DEFAULT '' NOT NULL,
486
KEY pcode (pcode)
487
);
488
INSERT INTO t2 VALUES ('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw6000'),('kvw6000'),('kld2000');
489
SELECT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1
490
LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;
491
pcode count
492
kld2000 1
493
klw1000 0
494
klw1020 0
495
klw1500 0
496
klw2000 0
497
klw2001 0
498
klw2002 0
499
klw2500 0
500
kmw1000 0
501
kmw1500 0
502
kmw2000 0
503
kmw2001 0
504
kmw2100 0
505
kmw3000 0
506
kmw3200 0
507
kvw2000 26
508
kvw2001 0
509
kvw3000 36
510
kvw3001 0
511
kvw3002 0
512
kvw3500 26
513
kvw3501 0
514
kvw3502 0
515
kvw3800 0
516
kvw3801 0
517
kvw3802 0
518
kvw3900 0
519
kvw3901 0
520
kvw3902 0
521
kvw4000 0
522
kvw4001 0
523
kvw4002 0
524
kvw4200 0
525
kvw4500 0
526
kvw5000 0
527
kvw5001 0
528
kvw5500 0
529
kvw5510 0
530
kvw5600 0
531
kvw5601 0
532
kvw6000 2
533
SELECT SQL_BIG_RESULT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1 LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;
534
pcode count
535
kld2000 1
536
klw1000 0
537
klw1020 0
538
klw1500 0
539
klw2000 0
540
klw2001 0
541
klw2002 0
542
klw2500 0
543
kmw1000 0
544
kmw1500 0
545
kmw2000 0
546
kmw2001 0
547
kmw2100 0
548
kmw3000 0
549
kmw3200 0
550
kvw2000 26
551
kvw2001 0
552
kvw3000 36
553
kvw3001 0
554
kvw3002 0
555
kvw3500 26
556
kvw3501 0
557
kvw3502 0
558
kvw3800 0
559
kvw3801 0
560
kvw3802 0
561
kvw3900 0
562
kvw3901 0
563
kvw3902 0
564
kvw4000 0
565
kvw4001 0
566
kvw4002 0
567
kvw4200 0
568
kvw4500 0
569
kvw5000 0
570
kvw5001 0
571
kvw5500 0
572
kvw5510 0
573
kvw5600 0
574
kvw5601 0
575
kvw6000 2
576
drop table t1,t2;
577
CREATE TABLE t1 (
578
id int,
579
pid int,
580
rep_del int,
581
KEY id (id),
582
KEY pid (pid)
583
);
584
INSERT INTO t1 VALUES (1,NULL,NULL);
585
INSERT INTO t1 VALUES (2,1,NULL);
586
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
587
id pid rep_del id pid rep_del
588
1 NULL NULL 2 1 NULL
589
2 1 NULL NULL NULL NULL
590
create index rep_del ON t1(rep_del);
591
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
592
id pid rep_del id pid rep_del
593
1 NULL NULL 2 1 NULL
594
2 1 NULL NULL NULL NULL
595
drop table t1;
596
CREATE TABLE t1 (
597
id int DEFAULT '0' NOT NULL,
598
name tinytext DEFAULT '' NOT NULL,
599
UNIQUE id (id)
600
);
601
INSERT INTO t1 VALUES (1,'yes'),(2,'no');
602
CREATE TABLE t2 (
603
id int DEFAULT '0' NOT NULL,
604
idx int DEFAULT '0' NOT NULL,
605
UNIQUE id (id,idx)
606
);
607
INSERT INTO t2 VALUES (1,1);
608
explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
609
id select_type table type possible_keys key key_len ref rows Extra
610
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
611
1 SIMPLE t2 ref id id 4 test.t1.id 1 Using where; Using index; Not exists
612
SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
613
id name id idx
614
2 no NULL NULL
615
drop table t1,t2;
616
create table t1 (bug_id bigint, reporter bigint);
617
create table t2 (bug_id bigint, who bigint, index(who));
618
insert into t2 values (1,1),(1,2);
619
insert into t1 values (1,1),(2,1);
620
SELECT * FROM t1 LEFT JOIN t2 ON (t1.bug_id = t2.bug_id AND t2.who = 2) WHERE (t1.reporter = 2 OR t2.who = 2);
621
bug_id reporter bug_id who
622
1 1 1 2
623
drop table t1,t2;
624
create table t1 (fooID int auto_increment, primary key (fooID));
625
create table t2 (fooID int not null, barID int not null, primary key (fooID,barID));
626
insert into t1 (fooID) values (10),(20),(30);
627
insert into t2 values (10,1),(20,2),(30,3);
628
explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
629
id select_type table type possible_keys key key_len ref rows Extra
630
1 SIMPLE t2 index NULL PRIMARY 8 NULL 3 Using index
631
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
632
select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
633
fooID barID fooID
634
10 1 NULL
635
20 2 NULL
636
30 3 30
637
select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
638
fooID barID fooID
639
10 1 NULL
640
20 2 NULL
641
30 3 30
642
drop table t1,t2;
643
create table t1 (i int);
644
create table t2 (i int);
645
create table t3 (i int);
646
insert into t1 values(1),(2);
647
insert into t2 values(2),(3);
648
insert into t3 values(2),(4);
649
select * from t1 natural left join t2 natural left join t3;
650
i
651
1
652
2
653
select * from t1 natural left join t2 where (t2.i is not null)=0;
654
i
655
1
656
select * from t1 natural left join t2 where (t2.i is not null) is not null;
657
i
658
1
659
2
660
select * from t1 natural left join t2 where (i is not null)=0;
661
i
662
select * from t1 natural left join t2 where (i is not null) is not null;
663
i
664
1
665
2
666
drop table t1,t2,t3;
667
create table t1 (f1 integer,f2 integer,f3 integer);
668
create table t2 (f2 integer,f4 integer);
669
create table t3 (f3 integer,f5 integer);
670
select * from t1
671
left outer join t2 using (f2)
672
left outer join t3 using (f3);
673
f3 f2 f1 f4 f5
674
drop table t1,t2,t3;
675
create table t1 (a1 int, a2 int);
676
create table t2 (b1 int not null, b2 int);
677
create table t3 (c1 int, c2 int);
678
insert into t1 values (1,2), (2,2), (3,2);
679
insert into t2 values (1,3), (2,3);
680
insert into t3 values (2,4), (3,4);
681
select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;
682
a1 a2 b1 b2 c1 c2
683
1 2 1 3 NULL NULL
684
2 2 2 3 NULL NULL
685
3 2 NULL NULL 3 4
686
explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;
687
id select_type table type possible_keys key key_len ref rows Extra
688
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
689
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
690
1 SIMPLE t3 ALL NULL NULL NULL NULL 2
691
drop table t1, t2, t3;
692
create table t1 (
693
a int,
694
b char(10),
695
key (a)
696
);
697
insert into t1 (a) values (1),(2),(3),(4);
698
create table t2 (a int);
699
select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a);
700
a b a
701
1 NULL NULL
702
2 NULL NULL
703
3 NULL NULL
704
4 NULL NULL
705
select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a);
706
a b a
707
1 NULL NULL
708
2 NULL NULL
709
3 NULL NULL
710
4 NULL NULL
711
drop table t1,t2;
712
create table t1 (
713
match_id int not null auto_increment,
714
home int default '0',
715
unique key match_id (match_id),
716
key match_id_2 (match_id)
717
);
718
insert into t1 values("1", "2");
719
create table t2 (
720
player_id int default '0',
721
match_1_h int default '0',
722
key player_id (player_id)
723
);
724
insert into t2 values("1", "5");
725
insert into t2 values("2", "9");
726
insert into t2 values("3", "3");
727
insert into t2 values("4", "7");
728
insert into t2 values("5", "6");
729
insert into t2 values("6", "8");
730
insert into t2 values("7", "4");
731
insert into t2 values("8", "12");
732
insert into t2 values("9", "11");
733
insert into t2 values("10", "10");
734
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
735
(t2 s left join t1 m on m.match_id = 1)
736
order by m.match_id desc;
737
id select_type table type possible_keys key key_len ref rows Extra
738
1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
739
1 SIMPLE m const match_id,match_id_2 match_id 4 const 1
740
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
741
(t2 s left join t1 m on m.match_id = 1)
742
order by UUX desc;
743
id select_type table type possible_keys key key_len ref rows Extra
744
1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
745
1 SIMPLE m const match_id,match_id_2 match_id 4 const 1
746
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
747
(t2 s left join t1 m on m.match_id = 1)
748
order by UUX desc;
749
player_id match_1_h * match_id home UUX
750
8 12 * 1 2 10
751
9 11 * 1 2 9
752
10 10 * 1 2 8
753
2 9 * 1 2 7
754
6 8 * 1 2 6
755
4 7 * 1 2 5
756
5 6 * 1 2 4
757
1 5 * 1 2 3
758
7 4 * 1 2 2
759
3 3 * 1 2 1
760
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
761
t2 s straight_join t1 m where m.match_id = 1
762
order by UUX desc;
763
id select_type table type possible_keys key key_len ref rows Extra
764
1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
765
1 SIMPLE m const match_id,match_id_2 match_id 4 const 1
766
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
767
t2 s straight_join t1 m where m.match_id = 1
768
order by UUX desc;
769
player_id match_1_h * match_id home UUX
770
8 12 * 1 2 10
771
9 11 * 1 2 9
772
10 10 * 1 2 8
773
2 9 * 1 2 7
774
6 8 * 1 2 6
775
4 7 * 1 2 5
776
5 6 * 1 2 4
777
1 5 * 1 2 3
778
7 4 * 1 2 2
779
3 3 * 1 2 1
780
drop table t1, t2;
781
create table t1 (a int, b int, unique index idx (a, b));
782
create table t2 (a int, b int, c int, unique index idx (a, b));
783
insert into t1 values (1, 10), (1,11), (2,10), (2,11);
784
insert into t2 values (1,10,3);
785
select t1.a, t1.b, t2.c from t1 left join t2
786
on t1.a=t2.a and t1.b=t2.b and t2.c=3
787
where t1.a=1 and t2.c is null;
788
a b c
789
1 11 NULL
790
drop table t1, t2;
791
CREATE TABLE t1 (
792
ts_id bigint default NULL,
793
inst_id int default NULL,
794
flag_name varchar(64) default NULL,
795
flag_value text,
796
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
797
) ENGINE=MyISAM;
798
CREATE TABLE t2 (
799
ts_id bigint default NULL,
800
inst_id int default NULL,
801
flag_name varchar(64) default NULL,
802
flag_value text,
803
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
804
) ENGINE=MyISAM;
805
INSERT INTO t1 VALUES
806
(111056548820001, 0, 'flag1', NULL),
807
(111056548820001, 0, 'flag2', NULL),
808
(2, 0, 'other_flag', NULL);
809
INSERT INTO t2 VALUES
810
(111056548820001, 3, 'flag1', 'sss');
811
SELECT t1.flag_name,t2.flag_value
812
FROM t1 LEFT JOIN t2
813
ON (t1.ts_id = t2.ts_id AND t1.flag_name = t2.flag_name AND
814
t2.inst_id = 3)
815
WHERE t1.inst_id = 0 AND t1.ts_id=111056548820001 AND
816
t2.flag_value IS NULL;
817
flag_name flag_value
818
flag2 NULL
819
DROP TABLE t1,t2;
820
CREATE TABLE t1 (
821
id int NOT NULL auto_increment,
822
text_id int default NULL,
823
PRIMARY KEY (id)
824
);
825
INSERT INTO t1 VALUES("1", "0");
826
INSERT INTO t1 VALUES("2", "10");
827
CREATE TABLE t2 (
828
text_id char(3) NOT NULL default '',
829
language_id char(3) NOT NULL default '',
830
text_data text,
831
PRIMARY KEY (text_id,language_id)
832
);
833
INSERT INTO t2 VALUES("0", "EN", "0-EN");
834
INSERT INTO t2 VALUES("0", "SV", "0-SV");
835
INSERT INTO t2 VALUES("10", "EN", "10-EN");
836
INSERT INTO t2 VALUES("10", "SV", "10-SV");
837
SELECT t1.id, t1.text_id, t2.text_data
838
FROM t1 LEFT JOIN t2
839
ON t1.text_id = t2.text_id
840
AND t2.language_id = 'SV'
841
WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');
842
id text_id text_data
843
1 0 0-SV
844
2 10 10-SV
845
DROP TABLE t1, t2;
846
CREATE TABLE t0 (a0 int PRIMARY KEY);
847
CREATE TABLE t1 (a1 int PRIMARY KEY);
848
CREATE TABLE t2 (a2 int);
849
CREATE TABLE t3 (a3 int);
850
INSERT INTO t0 VALUES (1);
851
INSERT INTO t1 VALUES (1);
852
INSERT INTO t2 VALUES (1), (2);
853
INSERT INTO t3 VALUES (1), (2);
854
SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
855
a1 a2
856
1 NULL
857
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
858
id select_type table type possible_keys key key_len ref rows Extra
859
1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index
860
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
861
SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
862
a1 a2 a3
863
1 NULL NULL
864
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
865
id select_type table type possible_keys key key_len ref rows Extra
866
1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index
867
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
868
1 SIMPLE t3 ALL NULL NULL NULL NULL 2
869
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
870
a0 a1 a2 a3
871
1 1 NULL NULL
872
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
873
id select_type table type possible_keys key key_len ref rows Extra
874
1 SIMPLE t0 index PRIMARY PRIMARY 4 NULL 1 Using index
875
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a0 1 Using index
876
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
877
1 SIMPLE t3 ALL NULL NULL NULL NULL 2
878
INSERT INTO t0 VALUES (0);
879
INSERT INTO t1 VALUES (0);
880
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
881
a0 a1 a2 a3
882
1 1 NULL NULL
883
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
884
id select_type table type possible_keys key key_len ref rows Extra
885
1 SIMPLE t0 const PRIMARY PRIMARY 4 const 1 Using index
886
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
887
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
888
1 SIMPLE t3 ALL NULL NULL NULL NULL 2
889
drop table t1,t2;
890
create table t1 (a int, b int);
891
insert into t1 values (1,1),(2,2),(3,3);
892
create table t2 (a int, b int);
893
insert into t2 values (1,1), (2,2);
894
select * from t2 right join t1 on t2.a=t1.a;
895
a b a b
896
1 1 1 1
897
2 2 2 2
898
NULL NULL 3 3
899
select straight_join * from t2 right join t1 on t2.a=t1.a;
900
a b a b
901
1 1 1 1
902
2 2 2 2
903
NULL NULL 3 3
904
DROP TABLE t0,t1,t2,t3;
905
CREATE TABLE t1 (a int PRIMARY KEY, b int);
906
CREATE TABLE t2 (a int PRIMARY KEY, b int);
907
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
908
INSERT INTO t2 VALUES (1,2), (2,2);
909
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
910
a b a b
911
1 1 1 2
912
2 1 2 2
913
3 1 NULL NULL
914
4 2 NULL NULL
915
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
916
a b a b
917
1 1 1 2
918
2 1 2 2
919
3 1 NULL NULL
920
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
921
WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
922
a b a b
923
1 1 1 2
924
2 1 2 2
925
3 1 NULL NULL
926
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
927
a b a b
928
1 1 1 2
929
2 1 2 2
930
3 1 NULL NULL
931
4 2 NULL NULL
932
DROP TABLE t1,t2;
933
set group_concat_max_len=5;
934
create table t1 (a int, b varchar(20));
935
create table t2 (a int, c varchar(20));
936
insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
937
insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
938
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
939
group_concat(t1.b,t2.c)
940
aaaaa
941
bbbbb
942
Warnings:
943
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
944
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
945
group_concat(t1.b,t2.c)
946
aaaaa
947
bbbbb
948
Warnings:
949
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
950
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
951
group_concat(t1.b,t2.c)
952
aaaaa
953
bbbbb
954
Warnings:
955
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
956
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
957
group_concat(t1.b,t2.c)
958
aaaaa
959
bbbbb
960
Warnings:
961
Warning 1260 2 line(s) were cut by GROUP_CONCAT()
962
drop table t1, t2;
963
set group_concat_max_len=default;
964
create table t1 (gid int not null, x int not null, y int not null, art int not null, primary key (gid,x,y));
965
insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
966
create table t2 (gid int not null, x int not null, y int not null, id int not null, primary key (gid,id,x,y), key id (id));
967
insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
968
create table t3 ( set_id int not null, id int not null, name char(12) not null, primary key (id,set_id));
969
insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4, 'f'), (1, 5, 'g'), (1, 6, 'h');
970
explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
971
left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
972
and t1.gid =1 and t2.gid =1 and t3.set_id =1;
973
id select_type table type possible_keys key key_len ref rows Extra
974
1 SIMPLE t1 const PRIMARY PRIMARY 12 const,const,const 1
975
1 SIMPLE t2 const PRIMARY,id PRIMARY 16 const,const,const,const 1 Using index
976
1 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1
977
drop tables t1,t2,t3;
978
CREATE TABLE t1 (c11 int);
979
CREATE TABLE t2 (c21 int);
980
INSERT INTO t1 VALUES (30), (40), (50);
981
INSERT INTO t2 VALUES (300), (400), (500);
982
SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
983
c11 c21
984
40 NULL
985
DROP TABLE t1, t2;
986
CREATE TABLE t1 (a int PRIMARY KEY, b int);
987
CREATE TABLE t2 (a int PRIMARY KEY, b int);
988
INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
989
INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
990
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
991
a b a b
992
7 8 7 5
993
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
994
a b a b
995
7 8 7 5
996
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
997
a b a b
998
7 8 7 5
999
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
1000
a b a b
1001
2 1 NULL NULL
1002
3 2 3 0
1003
4 3 4 1
1004
6 5 6 4
1005
8 7 NULL NULL
1006
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
1007
a b a b
1008
2 1 NULL NULL
1009
3 2 3 0
1010
4 3 4 1
1011
6 5 6 4
1012
8 7 NULL NULL
1013
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
1014
a b a b
1015
2 1 NULL NULL
1016
3 2 3 0
1017
4 3 4 1
1018
6 5 6 4
1019
8 7 NULL NULL
1020
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b;
1021
a b a b
1022
2 1 NULL NULL
1023
3 2 3 0
1024
4 3 4 1
1025
6 5 6 4
1026
7 8 7 5
1027
8 7 NULL NULL
1028
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b);
1029
a b a b
1030
2 1 NULL NULL
1031
3 2 3 0
1032
4 3 4 1
1033
6 5 6 4
1034
7 8 7 5
1035
8 7 NULL NULL
1036
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b);
1037
a b a b
1038
3 2 3 0
1039
4 3 4 1
1040
6 5 6 4
1041
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b);
1042
a b a b
1043
3 2 3 0
1044
4 3 4 1
1045
6 5 6 4
1046
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
1047
a b a b
1048
3 2 3 0
1049
4 3 4 1
1050
6 5 6 4
1051
7 8 7 5
1052
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
1053
a b a b
1054
3 2 3 0
1055
4 3 4 1
1056
6 5 6 4
1057
7 8 7 5
1058
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
1059
a b a b
1060
3 2 3 0
1061
4 3 4 1
1062
6 5 6 4
1063
7 8 7 5
1064
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
1065
a b a b
1066
3 2 3 0
1067
4 3 4 1
1068
6 5 6 4
1069
7 8 7 5
1070
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
1071
a b a b
1072
3 2 3 0
1073
4 3 4 1
1074
6 5 6 4
1075
7 8 7 5
1076
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
1077
a b a b
1078
3 2 3 0
1079
4 3 4 1
1080
6 5 6 4
1081
7 8 7 5
1082
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b);
1083
a b a b
1084
3 2 3 0
1085
4 3 4 1
1086
6 5 6 4
1087
7 8 7 5
1088
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b));
1089
a b a b
1090
3 2 3 0
1091
4 3 4 1
1092
6 5 6 4
1093
7 8 7 5
1094
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b;
1095
a b a b
1096
3 2 3 0
1097
4 3 4 1
1098
6 5 6 4
1099
7 8 7 5
1100
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b));
1101
a b a b
1102
3 2 3 0
1103
4 3 4 1
1104
6 5 6 4
1105
7 8 7 5
1106
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
1107
id select_type table type possible_keys key key_len ref rows Extra
1108
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4
1109
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
1110
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
1111
id select_type table type possible_keys key key_len ref rows Extra
1112
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where
1113
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
1114
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1);
1115
id select_type table type possible_keys key key_len ref rows Extra
1116
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where
1117
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
1118
DROP TABLE t1,t2;
1119
CREATE TABLE t1 (a int);
1120
CREATE TABLE t2 (b int);
1121
INSERT INTO t1 VALUES (1), (2), (3), (4);
1122
INSERT INTO t2 VALUES (2), (3);
1123
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
1124
a b
1125
1 NULL
1126
2 2
1127
3 3
1128
4 NULL
1129
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
1130
a b
1131
1 NULL
1132
2 2
1133
3 3
1134
4 NULL
1135
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
1136
a b
1137
1 NULL
1138
2 2
1139
3 3
1140
4 NULL
1141
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
1142
a b
1143
1 NULL
1144
2 2
1145
3 3
1146
4 NULL
1147
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
1148
a b
1149
1 NULL
1150
2 2
1151
3 3
1152
4 NULL
1153
DROP TABLE t1,t2;
1154
CREATE TABLE t1 (
1155
f1 varchar(16) collate utf8_swedish_ci PRIMARY KEY,
1156
f2 varchar(16) collate utf8_swedish_ci
1157
);
1158
CREATE TABLE t2 (
1159
f1 varchar(16) collate utf8_swedish_ci PRIMARY KEY,
1160
f3 varchar(16) collate utf8_swedish_ci
1161
);
1162
INSERT INTO t1 VALUES ('bla','blah');
1163
INSERT INTO t2 VALUES ('bla','sheep');
1164
SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
1165
f1 f2 f3
1166
bla blah sheep
1167
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
1168
f1 f2 f3
1169
bla blah sheep
1170
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
1171
f1 f2 f3
1172
bla blah sheep
1173
DROP TABLE t1,t2;
1174
CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
1175
CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
1176
INSERT INTO t1 VALUES
1177
(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
1178
INSERT INTO t2 VALUES
1179
(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
1180
EXPLAIN
1181
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
1182
id select_type table type possible_keys key key_len ref rows Extra
1183
1 SIMPLE t1 ALL NULL NULL NULL NULL 5
1184
1 SIMPLE t2 ref idx idx 4 test.t1.id 2 Using where; Not exists
1185
flush status;
1186
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
1187
id a
1188
1 aaaaaaa
1189
4 ddddddd
1190
show status like 'Handler_read%';
1191
Variable_name Value
1192
Handler_read_first 0
1193
Handler_read_key 0
1194
Handler_read_next 0
1195
Handler_read_prev 0
1196
Handler_read_rnd 0
1197
Handler_read_rnd_next 0
1198
DROP TABLE t1,t2;
1199
CREATE TABLE t1 (c int PRIMARY KEY, e int NOT NULL);
1200
INSERT INTO t1 VALUES (1,0), (2,1);
1201
CREATE TABLE t2 (d int PRIMARY KEY);
1202
INSERT INTO t2 VALUES (1), (2), (3);
1203
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
1204
id select_type table type possible_keys key key_len ref rows Extra
1205
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1206
1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Not exists
1207
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
1208
c e d
1209
1 0 NULL
1210
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
1211
c e d
1212
1 0 NULL
1213
DROP TABLE t1,t2;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1