join_outer.result 45.9 KB
Newer Older
1
drop table if exists t0,t1,t2,t3,t4,t5;
unknown's avatar
unknown committed
2 3 4 5
CREATE TABLE t1 (
grp int(11) default NULL,
a bigint(20) unsigned default NULL,
c char(10) NOT NULL default ''
unknown's avatar
unknown committed
6
) ENGINE=MyISAM;
unknown's avatar
unknown committed
7 8 9 10
INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a));
insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a;
11 12 13 14 15
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
unknown's avatar
unknown committed
16
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
17 18 19 20 21 22 23 24
grp	a	c	id	a	c	d
NULL	NULL		NULL	NULL	NULL	NULL
1	1	a	1	1	a	1
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
unknown's avatar
unknown committed
25
select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
26 27 28 29 30 31
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
NULL	NULL	NULL	4	7	D	7
unknown's avatar
unknown committed
32
select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2;
33 34 35 36 37 38
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
NULL	NULL	NULL	4	7	D	7
39
select t1.*,t2.* from t1 left join t2 using (a);
40 41 42 43 44 45 46 47
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
NULL	NULL		NULL	NULL	NULL	NULL
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
select t1.*,t2.* from t1 left join t2 using (a,c);
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
3	4	E	NULL	NULL	NULL	NULL
3	5	C	NULL	NULL	NULL	NULL
3	6	D	NULL	NULL	NULL	NULL
NULL	NULL		NULL	NULL	NULL	NULL
select t1.*,t2.* from t1 left join t2 using (c);
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
1	1	a	3	4	A	4
2	2	b	3	5	B	5
2	3	c	3	6	C	6
3	4	E	NULL	NULL	NULL	NULL
3	5	C	3	6	C	6
3	6	D	4	7	D	7
NULL	NULL		NULL	NULL	NULL	NULL
select t1.*,t2.* from t1 natural left outer join t2;
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
3	4	E	NULL	NULL	NULL	NULL
3	5	C	NULL	NULL	NULL	NULL
3	6	D	NULL	NULL	NULL	NULL
NULL	NULL		NULL	NULL	NULL	NULL
unknown's avatar
unknown committed
82
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
83 84 85 86
grp	a	c	id	a	c	d
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
unknown's avatar
unknown committed
87
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
88 89 90 91
grp	a	c	id	a	c	d
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
NULL	NULL		NULL	NULL	NULL	NULL
unknown's avatar
unknown committed
92
explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1;
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
95
explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
unknown's avatar
unknown committed
96
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
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 101 102 103 104 105 106 107
grp	a	c	id	a	c	d	a
1	1	a	1	1	a	1	1
2	2	b	NULL	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL	NULL
3	4	E	3	4	A	4	4
3	5	C	3	5	B	5	5
3	6	D	3	6	C	6	6
NULL	NULL		NULL	NULL	NULL	NULL	NULL
unknown's avatar
unknown committed
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);
unknown's avatar
unknown committed
109
ERROR 42S22: Unknown column 't3.a' in 'on clause'
unknown's avatar
unknown committed
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);
unknown's avatar
unknown committed
111
ERROR 42S22: Unknown column 't3.a' in 'on clause'
unknown's avatar
unknown committed
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);
unknown's avatar
unknown committed
113
ERROR 42S22: Unknown column 't3.a' in 'on clause'
114 115 116 117 118 119
select t1.*,t2.* from t1 inner join t2 using (a);
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
unknown's avatar
unknown committed
120
select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
121 122 123 124 125
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
126 127 128
select t1.*,t2.* from t1 natural join t2;
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
unknown's avatar
unknown committed
129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159
drop table t1,t2;
CREATE TABLE t1 (
usr_id INT unsigned NOT NULL,
uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
start_num INT unsigned NOT NULL DEFAULT 1,
increment INT unsigned NOT NULL DEFAULT 1,
PRIMARY KEY (uniq_id),
INDEX usr_uniq_idx (usr_id, uniq_id),
INDEX uniq_usr_idx (uniq_id, usr_id)
);
CREATE TABLE t2 (
id INT unsigned NOT NULL DEFAULT 0,
usr2_id INT unsigned NOT NULL DEFAULT 0,
max INT unsigned NOT NULL DEFAULT 0,
c_amount INT unsigned NOT NULL DEFAULT 0,
d_max INT unsigned NOT NULL DEFAULT 0,
d_num INT unsigned NOT NULL DEFAULT 0,
orig_time INT unsigned NOT NULL DEFAULT 0,
c_time INT unsigned NOT NULL DEFAULT 0,
active ENUM ("no","yes") NOT NULL,
PRIMARY KEY (id,usr2_id),
INDEX id_idx (id),
INDEX usr2_idx (usr2_id)
);
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);
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;
160 161
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
unknown's avatar
unknown committed
162 163 164 165 166 167
SELECT t1.usr_id,t1.uniq_id,t1.increment,
t2.usr2_id,t2.c_amount,t2.max
FROM t2
RIGHT JOIN t1 ON t2.id = t1.uniq_id
WHERE t1.uniq_id = 4
ORDER BY t2.c_amount;
168 169
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
unknown's avatar
unknown committed
170 171
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
172
ERROR 23000: Duplicate entry '2-3' for key 'PRIMARY'
unknown's avatar
unknown committed
173 174
INSERT INTO t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
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 176
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
unknown's avatar
unknown committed
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 179
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
unknown's avatar
unknown committed
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 182
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
unknown's avatar
unknown committed
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202
drop table t1,t2;
CREATE TABLE t1 (
cod_asig int(11) DEFAULT '0' NOT NULL,
desc_larga_cat varchar(80) DEFAULT '' NOT NULL,
desc_larga_cas varchar(80) DEFAULT '' NOT NULL,
desc_corta_cat varchar(40) DEFAULT '' NOT NULL,
desc_corta_cas varchar(40) DEFAULT '' NOT NULL,
cred_total double(3,1) DEFAULT '0.0' NOT NULL,
pre_requisit int(11),
co_requisit int(11),
preco_requisit int(11),
PRIMARY KEY (cod_asig)
);
INSERT INTO t1 VALUES (10360,'asdfggfg','Introduccion a los  Ordenadores I','asdfggfg','Introduccio Ordinadors I',6.0,NULL,NULL,NULL);
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);
INSERT INTO t1 VALUES (10362,'Laboratori d`Ordinadors','Laboratorio de Ordenadores','Laboratori d`Ordinadors','Laboratori Ordinadors',4.5,NULL,NULL,NULL);
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);
INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
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);
unknown's avatar
unknown committed
204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236
CREATE TABLE t2 (
idAssignatura int(11) DEFAULT '0' NOT NULL,
Grup int(11) DEFAULT '0' NOT NULL,
Places smallint(6) DEFAULT '0' NOT NULL,
PlacesOcupades int(11) DEFAULT '0',
PRIMARY KEY (idAssignatura,Grup)
);
INSERT INTO t2 VALUES (10360,12,333,0);
INSERT INTO t2 VALUES (10361,30,2,0);
INSERT INTO t2 VALUES (10361,40,3,0);
INSERT INTO t2 VALUES (10360,45,10,0);
INSERT INTO t2 VALUES (10362,10,12,0);
INSERT INTO t2 VALUES (10360,55,2,0);
INSERT INTO t2 VALUES (10360,70,0,0);
INSERT INTO t2 VALUES (10360,565656,0,0);
INSERT INTO t2 VALUES (10360,32767,7,0);
INSERT INTO t2 VALUES (10360,33,8,0);
INSERT INTO t2 VALUES (10360,7887,85,0);
INSERT INTO t2 VALUES (11405,88,8,0);
INSERT INTO t2 VALUES (10360,0,55,0);
INSERT INTO t2 VALUES (10360,99,0,0);
INSERT INTO t2 VALUES (11411,30,10,0);
INSERT INTO t2 VALUES (11404,0,0,0);
INSERT INTO t2 VALUES (10362,11,111,0);
INSERT INTO t2 VALUES (10363,33,333,0);
INSERT INTO t2 VALUES (11412,55,0,0);
INSERT INTO t2 VALUES (50003,66,6,0);
INSERT INTO t2 VALUES (11403,5,0,0);
INSERT INTO t2 VALUES (11406,11,11,0);
INSERT INTO t2 VALUES (11410,11410,131,0);
INSERT INTO t2 VALUES (11416,11416,32767,0);
INSERT INTO t2 VALUES (11409,0,0,0);
CREATE TABLE t3 (
237
id int(11) NOT NULL auto_increment,
unknown's avatar
unknown committed
238 239 240 241 242 243 244 245 246 247 248 249
dni_pasaporte char(16) DEFAULT '' NOT NULL,
idPla int(11) DEFAULT '0' NOT NULL,
cod_asig int(11) DEFAULT '0' NOT NULL,
any smallint(6) DEFAULT '0' NOT NULL,
quatrimestre smallint(6) DEFAULT '0' NOT NULL,
estat char(1) DEFAULT 'M' NOT NULL,
PRIMARY KEY (id),
UNIQUE dni_pasaporte (dni_pasaporte,idPla),
UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre)
);
INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
CREATE TABLE t4 (
250
id int(11) NOT NULL auto_increment,
unknown's avatar
unknown committed
251 252 253 254 255 256 257 258 259 260 261
papa int(11) DEFAULT '0' NOT NULL,
fill int(11) DEFAULT '0' NOT NULL,
idPla int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
KEY papa (idPla,papa),
UNIQUE papa_2 (idPla,papa,fill)
);
INSERT INTO t4 VALUES (1,-1,10360,1);
INSERT INTO t4 VALUES (2,-1,10361,1);
INSERT INTO t4 VALUES (3,-1,10362,1);
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 263 264 265 266 267 268 269 270 271 272 273
fill	desc_larga_cat	cred_total	Grup	Places	PlacesOcupades
10360	asdfggfg	6.0	0	55	0
10360	asdfggfg	6.0	12	333	0
10360	asdfggfg	6.0	33	8	0
10360	asdfggfg	6.0	45	10	0
10360	asdfggfg	6.0	55	2	0
10360	asdfggfg	6.0	7887	85	0
10360	asdfggfg	6.0	32767	7	0
10361	Components i Circuits Electronics I	6.0	30	2	0
10361	Components i Circuits Electronics I	6.0	40	3	0
10362	Laboratori d`Ordinadors	4.5	10	12	0
10362	Laboratori d`Ordinadors	4.5	11	111	0
unknown's avatar
unknown committed
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 276 277 278
fill	idPla
10360	NULL
10361	NULL
10362	NULL
unknown's avatar
unknown committed
279 280
INSERT INTO t3 VALUES (3,'1234',1,10360,98,1,'S');
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 282 283 284
fill	idPla
10360	1
10361	NULL
10362	NULL
unknown's avatar
unknown committed
285 286
drop table t1,t2,t3,test.t4;
CREATE TABLE t1 (
287
id smallint(5) unsigned NOT NULL auto_increment,
unknown's avatar
unknown committed
288 289 290 291 292 293 294
name char(60) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t1 VALUES (1,'Antonio Paz');
INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
INSERT INTO t1 VALUES (3,'Thimble Smith');
CREATE TABLE t2 (
295
id smallint(5) unsigned NOT NULL auto_increment,
unknown's avatar
unknown committed
296 297 298 299 300 301 302 303
owner smallint(5) unsigned DEFAULT '0' NOT NULL,
name char(60),
PRIMARY KEY (id)
);
INSERT INTO t2 VALUES (1,1,'El Gato');
INSERT INTO t2 VALUES (2,1,'Perrito');
INSERT INTO t2 VALUES (3,3,'Happy');
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner);
304 305 306 307 308
name	name	id
Antonio Paz	El Gato	1
Antonio Paz	Perrito	2
Lilliana Angelovska	NULL	NULL
Thimble Smith	Happy	3
unknown's avatar
unknown committed
309
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
310 311
name	name	id
Lilliana Angelovska	NULL	NULL
unknown's avatar
unknown committed
312
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
unknown's avatar
unknown committed
313
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
314
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
unknown's avatar
unknown committed
315
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Not exists
unknown's avatar
unknown committed
316
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
unknown's avatar
unknown committed
317
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
318
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
unknown's avatar
unknown committed
319
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
unknown's avatar
unknown committed
320
select count(*) from t1 left join t2 on (t1.id = t2.owner);
321 322
count(*)
4
unknown's avatar
unknown committed
323
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner);
324 325 326 327 328
name	name	id
Antonio Paz	El Gato	1
Antonio Paz	Perrito	2
Lilliana Angelovska	NULL	NULL
Thimble Smith	Happy	3
unknown's avatar
unknown committed
329
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
330 331
name	name	id
Lilliana Angelovska	NULL	NULL
unknown's avatar
unknown committed
332
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
unknown's avatar
unknown committed
333
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
334
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
unknown's avatar
unknown committed
335
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Not exists
unknown's avatar
unknown committed
336
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
unknown's avatar
unknown committed
337
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
338
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
unknown's avatar
unknown committed
339
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
unknown's avatar
unknown committed
340
select count(*) from t2 right join t1 on (t1.id = t2.owner);
341 342
count(*)
4
unknown's avatar
unknown committed
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 345 346 347 348
name	name	id	id
Antonio Paz	El Gato	1	1
Antonio Paz	Perrito	2	1
Lilliana Angelovska	NULL	NULL	NULL
Thimble Smith	Happy	3	3
unknown's avatar
unknown committed
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 351 352
name	name	id	id
Antonio Paz	El Gato	1	1
Antonio Paz	Perrito	2	1
353
NULL	NULL	NULL	2
354
Thimble Smith	Happy	3	3
unknown's avatar
unknown committed
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 357 358
name	name	id	owner	id
Antonio Paz	El Gato	1	1	1
Antonio Paz	Perrito	2	1	1
359
NULL	NULL	NULL	NULL	2
360
Thimble Smith	Happy	3	3	3
unknown's avatar
unknown committed
361 362 363
drop table t1,t2;
create table t1 (id int not null, str char(10), index(str));
insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
unknown's avatar
unknown committed
364
select * from t1 where str is not null order by id;
365 366
id	str
3	foo
unknown's avatar
unknown committed
367
4	bar
unknown's avatar
unknown committed
368
select * from t1 where str is null;
369 370 371
id	str
1	NULL
2	NULL
unknown's avatar
unknown committed
372 373
drop table t1;
CREATE TABLE t1 (
374
t1_id bigint(21) NOT NULL auto_increment,
unknown's avatar
unknown committed
375 376 377
PRIMARY KEY (t1_id)
);
CREATE TABLE t2 (
378
t2_id bigint(21) NOT NULL auto_increment,
unknown's avatar
unknown committed
379 380 381
PRIMARY KEY (t2_id)
);
CREATE TABLE t3 (
382
t3_id bigint(21) NOT NULL auto_increment,
unknown's avatar
unknown committed
383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402
PRIMARY KEY (t3_id)
);
CREATE TABLE t4 (
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
KEY seq_0_id (seq_0_id),
KEY seq_1_id (seq_1_id)
);
CREATE TABLE t5 (
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
KEY seq_1_id (seq_1_id),
KEY seq_0_id (seq_0_id)
);
insert into t1 values (1);
insert into t2 values (1);
insert into t3 values (1);
insert into t4 values (1,1);
insert into t5 values (1,1);
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;
unknown's avatar
unknown committed
403
ERROR 42S22: Unknown column 't2.t2_id' in 'on clause'
unknown's avatar
unknown committed
404 405 406 407 408 409 410
drop table t1,t2,t3,t4,t5;
create table t1 (n int, m int, o int, key(n));
create table t2 (n int not null, m int, o int, primary key(n));
insert into t1 values (1, 2, 11), (1, 2, 7), (2, 2, 8), (1,2,9),(1,3,9);
insert into t2 values (1, 2, 3),(2, 2, 8), (4,3,9),(3,2,10);
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
t1.m = t2.m where t1.n = 1;
411 412 413 414 415
n	m	o	n	m	o
1	2	11	1	2	3
1	2	7	1	2	3
1	2	9	1	2	3
1	3	9	NULL	NULL	NULL
unknown's avatar
unknown committed
416 417
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
t1.m = t2.m where t1.n = 1 order by t1.o;
418 419 420 421 422
n	m	o	n	m	o
1	2	7	1	2	3
1	2	9	1	2	3
1	3	9	NULL	NULL	NULL
1	2	11	1	2	3
unknown's avatar
unknown committed
423 424 425 426 427 428 429 430 431 432
drop table t1,t2;
CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
INSERT INTO t1 VALUES (1,'a',1);
INSERT INTO t1 VALUES (2,'b',1);
INSERT INTO t1 VALUES (3,'c',2);
CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));
INSERT INTO t2 VALUES (1,'x');
INSERT INTO t2 VALUES (2,'y');
INSERT INTO t2 VALUES (3,'z');
SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
433 434
id2
3
435
SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
436 437
id2
3
unknown's avatar
unknown committed
438 439 440 441 442 443 444 445 446 447 448 449 450
drop table t1,t2;
create table t1 ( color varchar(20), name varchar(20) );
insert into t1 values ( 'red', 'apple' );
insert into t1 values ( 'yellow', 'banana' );
insert into t1 values ( 'green', 'lime' );
insert into t1 values ( 'black', 'grape' );
insert into t1 values ( 'blue', 'blueberry' );
create table t2 ( count int, color varchar(20) );
insert into t2 values (10, 'green');
insert into t2 values (5, 'black');
insert into t2 values (15, 'white');
insert into t2 values (7, 'green');
select * from t1;
451 452 453 454 455 456
color	name
red	apple
yellow	banana
green	lime
black	grape
blue	blueberry
unknown's avatar
unknown committed
457
select * from t2;
458 459 460 461 462
count	color
10	green
5	black
15	white
7	green
unknown's avatar
unknown committed
463
select * from t2 natural join t1;
unknown's avatar
unknown committed
464 465 466 467
color	count	name
green	10	lime
green	7	lime
black	5	grape
unknown's avatar
unknown committed
468
select t2.count, t1.name from t2 natural join t1;
469 470 471 472
count	name
10	lime
7	lime
5	grape
unknown's avatar
unknown committed
473
select t2.count, t1.name from t2 inner join t1 using (color);
474 475 476 477
count	name
10	lime
7	lime
5	grape
unknown's avatar
unknown committed
478 479 480 481 482 483 484 485 486 487 488 489 490
drop table t1;
drop table t2;
CREATE TABLE t1 (
pcode varchar(8) DEFAULT '' NOT NULL
);
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');
CREATE TABLE t2 (
pcode varchar(8) DEFAULT '' NOT NULL,
KEY pcode (pcode)
);
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');
SELECT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1
LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;
491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532
pcode	count
kld2000	1
klw1000	0
klw1020	0
klw1500	0
klw2000	0
klw2001	0
klw2002	0
klw2500	0
kmw1000	0
kmw1500	0
kmw2000	0
kmw2001	0
kmw2100	0
kmw3000	0
kmw3200	0
kvw2000	26
kvw2001	0
kvw3000	36
kvw3001	0
kvw3002	0
kvw3500	26
kvw3501	0
kvw3502	0
kvw3800	0
kvw3801	0
kvw3802	0
kvw3900	0
kvw3901	0
kvw3902	0
kvw4000	0
kvw4001	0
kvw4002	0
kvw4200	0
kvw4500	0
kvw5000	0
kvw5001	0
kvw5500	0
kvw5510	0
kvw5600	0
kvw5601	0
kvw6000	2
unknown's avatar
unknown committed
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 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575
pcode	count
kld2000	1
klw1000	0
klw1020	0
klw1500	0
klw2000	0
klw2001	0
klw2002	0
klw2500	0
kmw1000	0
kmw1500	0
kmw2000	0
kmw2001	0
kmw2100	0
kmw3000	0
kmw3200	0
kvw2000	26
kvw2001	0
kvw3000	36
kvw3001	0
kvw3002	0
kvw3500	26
kvw3501	0
kvw3502	0
kvw3800	0
kvw3801	0
kvw3802	0
kvw3900	0
kvw3901	0
kvw3902	0
kvw4000	0
kvw4001	0
kvw4002	0
kvw4200	0
kvw4500	0
kvw5000	0
kvw5001	0
kvw5500	0
kvw5510	0
kvw5600	0
kvw5601	0
kvw6000	2
unknown's avatar
unknown committed
576 577 578 579 580 581 582 583 584 585 586
drop table t1,t2;
CREATE TABLE t1 (
id int(11),
pid int(11),
rep_del tinyint(4),
KEY id (id),
KEY pid (pid)
);
INSERT INTO t1 VALUES (1,NULL,NULL);
INSERT INTO t1 VALUES (2,1,NULL);
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
587 588 589
id	pid	rep_del	id	pid	rep_del
1	NULL	NULL	2	1	NULL
2	1	NULL	NULL	NULL	NULL
unknown's avatar
unknown committed
590 591
create index rep_del ON t1(rep_del);
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
592 593 594
id	pid	rep_del	id	pid	rep_del
1	NULL	NULL	2	1	NULL
2	1	NULL	NULL	NULL	NULL
unknown's avatar
unknown committed
595 596 597 598 599 600
drop table t1;
CREATE TABLE t1 (
id int(11) DEFAULT '0' NOT NULL,
name tinytext DEFAULT '' NOT NULL,
UNIQUE id (id)
);
601 602
Warnings:
Warning	1101	BLOB/TEXT column 'name' can't have a default value
unknown's avatar
unknown committed
603 604 605 606 607 608 609 610
INSERT INTO t1 VALUES (1,'yes'),(2,'no');
CREATE TABLE t2 (
id int(11) DEFAULT '0' NOT NULL,
idx int(11) DEFAULT '0' NOT NULL,
UNIQUE id (id,idx)
);
INSERT INTO t2 VALUES (1,1);
explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
unknown's avatar
unknown committed
611
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
612
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
unknown's avatar
unknown committed
613
1	SIMPLE	t2	ref	id	id	4	test.t1.id	1	Using where; Using index; Not exists
unknown's avatar
unknown committed
614
SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
615 616
id	name	id	idx
2	no	NULL	NULL
unknown's avatar
unknown committed
617
drop table t1,t2;
unknown's avatar
unknown committed
618 619 620 621 622 623 624 625
create table t1 (bug_id mediumint, reporter mediumint);
create table t2 (bug_id mediumint, who mediumint, index(who));
insert into t2 values (1,1),(1,2);
insert into t1 values (1,1),(2,1);
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);
bug_id	reporter	bug_id	who
1	1	1	2
drop table t1,t2;
unknown's avatar
unknown committed
626 627 628 629 630
create table t1 (fooID smallint unsigned auto_increment, primary key (fooID));
create table t2 (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID));
insert into t1 (fooID) values (10),(20),(30);
insert into t2 values (10,1),(20,2),(30,3);
explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
unknown's avatar
unknown committed
631
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
632
1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	3	Using index
633
1	SIMPLE	t1	const	PRIMARY	PRIMARY	2	const	1	Using index
unknown's avatar
unknown committed
634 635 636 637 638 639 640 641 642 643 644
select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
fooID	barID	fooID
10	1	NULL
20	2	NULL
30	3	30
select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
fooID	barID	fooID
10	1	NULL
20	2	NULL
30	3	30
drop table t1,t2;
645 646 647 648 649 650 651
create table t1 (i int);
create table t2 (i int);
create table t3 (i int);
insert into t1 values(1),(2);
insert into t2 values(2),(3);
insert into t3 values(2),(4);
select * from t1 natural left join t2 natural left join t3;
unknown's avatar
unknown committed
652 653 654
i
1
2
655 656 657 658 659 660 661
select * from t1 natural left join t2 where (t2.i is not null)=0;
i
1
select * from t1 natural left join t2 where (t2.i is not null) is not null;
i
1
2
unknown's avatar
unknown committed
662 663 664 665 666 667
select * from t1 natural left join t2 where (i is not null)=0;
i
select * from t1 natural left join t2 where (i is not null) is not null;
i
1
2
668
drop table t1,t2,t3;
unknown's avatar
unknown committed
669 670 671 672 673 674
create table t1 (f1 integer,f2 integer,f3 integer);
create table t2 (f2 integer,f4 integer);
create table t3 (f3 integer,f5 integer);
select * from t1
left outer join t2 using (f2)
left outer join t3 using (f3);
unknown's avatar
unknown committed
675
f3	f2	f1	f4	f5
unknown's avatar
unknown committed
676
drop table t1,t2,t3;
unknown's avatar
unknown committed
677 678 679 680 681 682 683 684 685 686 687 688
create table t1 (a1 int, a2 int);
create table t2 (b1 int not null, b2 int);
create table t3 (c1 int, c2 int);
insert into t1 values (1,2), (2,2), (3,2);
insert into t2 values (1,3), (2,3);
insert into t3 values (2,4),        (3,4);
select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
a1	a2	b1	b2	c1	c2
1	2	1	3	NULL	NULL
2	2	2	3	NULL	NULL
3	2	NULL	NULL	3	4
explain select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
unknown's avatar
unknown committed
689 690 691 692
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
unknown's avatar
unknown committed
693
drop table t1, t2, t3;
unknown's avatar
unknown committed
694
create table t1 (
695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713
a int(11),
b char(10),
key (a)
);
insert into t1 (a) values (1),(2),(3),(4);
create table t2 (a int);
select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a);
a	b	a
1	NULL	NULL
2	NULL	NULL
3	NULL	NULL
4	NULL	NULL
select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a);
a	b	a
1	NULL	NULL
2	NULL	NULL
3	NULL	NULL
4	NULL	NULL
drop table t1,t2;
unknown's avatar
Merge  
unknown committed
714
create table t1 (
unknown's avatar
unknown committed
715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739
match_id tinyint(3) unsigned not null auto_increment,
home tinyint(3) unsigned default '0',
unique key match_id (match_id),
key match_id_2 (match_id)
);
insert into t1 values("1", "2");
create table t2 (
player_id tinyint(3) unsigned default '0',
match_1_h tinyint(3) unsigned default '0',
key player_id (player_id)
);
insert into t2 values("1", "5");
insert into t2 values("2", "9");
insert into t2 values("3", "3");
insert into t2 values("4", "7");
insert into t2 values("5", "6");
insert into t2 values("6", "8");
insert into t2 values("7", "4");
insert into t2 values("8", "12");
insert into t2 values("9", "11");
insert into t2 values("10", "10");
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
(t2 s left join t1 m on m.match_id = 1) 
order by m.match_id desc;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
740
1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
741
1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
unknown's avatar
unknown committed
742 743 744 745 746
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
(t2 s left join t1 m on m.match_id = 1) 
order by UUX desc;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
747
1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
unknown's avatar
unknown committed
748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
(t2 s left join t1 m on m.match_id = 1) 
order by UUX desc;
player_id	match_1_h	*	match_id	home	UUX
8	12	*	1	2	10
9	11	*	1	2	9
10	10	*	1	2	8
2	9	*	1	2	7
6	8	*	1	2	6
4	7	*	1	2	5
5	6	*	1	2	4
1	5	*	1	2	3
7	4	*	1	2	2
3	3	*	1	2	1
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
t2 s straight_join t1 m where m.match_id = 1 
order by UUX desc;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
767
1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
unknown's avatar
unknown committed
768 769 770 771 772 773 774 775 776 777 778 779 780 781 782
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
t2 s straight_join t1 m where m.match_id = 1 
order by UUX desc;
player_id	match_1_h	*	match_id	home	UUX
8	12	*	1	2	10
9	11	*	1	2	9
10	10	*	1	2	8
2	9	*	1	2	7
6	8	*	1	2	6
4	7	*	1	2	5
5	6	*	1	2	4
1	5	*	1	2	3
7	4	*	1	2	2
3	3	*	1	2	1
drop table t1, t2;
783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821
create table t1 (a int, b int, unique index idx (a, b));
create table t2 (a int, b int, c int, unique index idx (a, b));
insert into t1 values (1, 10), (1,11), (2,10), (2,11);
insert into t2 values (1,10,3);
select t1.a, t1.b, t2.c from t1 left join t2
on t1.a=t2.a and t1.b=t2.b and t2.c=3
where t1.a=1 and t2.c is null;
a	b	c
1	11	NULL
drop table t1, t2;
CREATE TABLE t1 (
ts_id bigint(20) default NULL,
inst_id tinyint(4) default NULL,
flag_name varchar(64) default NULL,
flag_value text,
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE t2 (
ts_id bigint(20) default NULL,
inst_id tinyint(4) default NULL,
flag_name varchar(64) default NULL,
flag_value text,
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES
(111056548820001, 0, 'flag1', NULL),
(111056548820001, 0, 'flag2', NULL),
(2, 0, 'other_flag', NULL);
INSERT INTO t2 VALUES
(111056548820001, 3, 'flag1', 'sss');
SELECT t1.flag_name,t2.flag_value 
FROM t1 LEFT JOIN t2 
ON (t1.ts_id = t2.ts_id AND t1.flag_name = t2.flag_name AND
t2.inst_id = 3) 
WHERE t1.inst_id = 0 AND t1.ts_id=111056548820001 AND
t2.flag_value IS  NULL;
flag_name	flag_value
flag2	NULL
DROP TABLE t1,t2;
822
CREATE TABLE t1 (
823 824 825 826
id int(11) unsigned NOT NULL auto_increment,
text_id int(10) unsigned default NULL,
PRIMARY KEY  (id)
);
827 828 829
INSERT INTO t1 VALUES("1", "0");
INSERT INTO t1 VALUES("2", "10");
CREATE TABLE t2 (
830 831 832 833 834
text_id char(3) NOT NULL default '',
language_id char(3) NOT NULL default '',
text_data text,
PRIMARY KEY  (text_id,language_id)
);
835 836 837 838 839 840 841 842 843
INSERT INTO t2 VALUES("0", "EN", "0-EN");
INSERT INTO t2 VALUES("0", "SV", "0-SV");
INSERT INTO t2 VALUES("10", "EN", "10-EN");
INSERT INTO t2 VALUES("10", "SV", "10-SV");
SELECT t1.id, t1.text_id, t2.text_data
FROM t1 LEFT JOIN t2
ON t1.text_id = t2.text_id
AND t2.language_id = 'SV'
  WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');
844 845 846
id	text_id	text_data
1	0	0-SV
2	10	10-SV
847
DROP TABLE t1, t2;
848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890
CREATE TABLE t0 (a0 int PRIMARY KEY);
CREATE TABLE t1 (a1 int PRIMARY KEY);
CREATE TABLE t2 (a2 int);
CREATE TABLE t3 (a3 int);
INSERT INTO t0 VALUES (1);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
a1	a2
1	NULL
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
a1	a2	a3
1	NULL	NULL
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
a0	a1	a2	a3
1	1	NULL	NULL
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	system	PRIMARY	NULL	NULL	NULL	1	
1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
INSERT INTO t0 VALUES (0);
INSERT INTO t1 VALUES (0);
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
a0	a1	a2	a3
1	1	NULL	NULL
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	const	PRIMARY	PRIMARY	4	const	1	Using index
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
891 892 893 894 895 896 897 898 899 900 901 902 903 904 905
drop table t1,t2;
create table t1 (a int, b int);
insert into t1 values (1,1),(2,2),(3,3);
create table t2 (a int, b int);
insert into t2 values (1,1), (2,2);
select * from t2 right join t1 on t2.a=t1.a;
a	b	a	b
1	1	1	1
2	2	2	2
NULL	NULL	3	3
select straight_join * from t2 right join t1 on t2.a=t1.a;
a	b	a	b
1	1	1	1
2	2	2	2
NULL	NULL	3	3
906
DROP TABLE t0,t1,t2,t3;
907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927
CREATE TABLE t1 (a int PRIMARY KEY, b int);
CREATE TABLE t2 (a int PRIMARY KEY, b int);
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
INSERT INTO t2 VALUES (1,2), (2,2);
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
a	b	a	b
1	1	1	2
2	1	2	2
3	1	NULL	NULL
4	2	NULL	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
a	b	a	b
1	1	1	2
2	1	2	2
3	1	NULL	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
a	b	a	b
1	1	1	2
2	1	2	2
3	1	NULL	NULL
928 929 930 931 932 933
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
a	b	a	b
1	1	1	2
2	1	2	2
3	1	NULL	NULL
4	2	NULL	NULL
934
DROP TABLE t1,t2;
935 936 937 938 939
set group_concat_max_len=5;
create table t1 (a int, b varchar(20));
create table t2 (a int, c varchar(20));
insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
940 941 942 943 944
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Marc Alff's avatar
Marc Alff committed
945 946
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
947 948 949 950 951
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Marc Alff's avatar
Marc Alff committed
952 953
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
unknown's avatar
unknown committed
954
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
955 956 957 958
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Marc Alff's avatar
Marc Alff committed
959 960
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
unknown's avatar
unknown committed
961
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
962 963 964 965
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Marc Alff's avatar
Marc Alff committed
966 967
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
968 969
drop table t1, t2;
set group_concat_max_len=default;
970 971 972 973 974 975 976 977 978 979 980 981 982 983
create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, art int(11) not null, primary key  (gid,x,y));
insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, id int(11) not null, primary key  (gid,id,x,y), key id (id));
insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null, name char(12) not null, primary key  (id,set_id));
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');
explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
and t1.gid =1 and t2.gid =1 and t3.set_id =1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	PRIMARY	PRIMARY	10	const,const,const	1	
1	SIMPLE	t2	const	PRIMARY,id	PRIMARY	14	const,const,const,const	1	Using index
1	SIMPLE	t3	const	PRIMARY	PRIMARY	3	const,const	1	
drop tables t1,t2,t3;
984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000
CREATE TABLE t1 (EMPNUM INT, GRP INT);
INSERT INTO t1 VALUES (0, 10);
INSERT INTO t1 VALUES (2, 30);
CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
INSERT INTO t2 VALUES (0, 'KERI');
INSERT INTO t2 VALUES (9, 'BARRY');
CREATE VIEW v1 AS
SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
SELECT * FROM v1;
EMPNUM	NAME	GRP
0	KERI	10
9	BARRY	NULL
SELECT * FROM v1 WHERE EMPNUM < 10;
EMPNUM	NAME	GRP
0	KERI	10
9	BARRY	NULL
1001
DROP VIEW v1;
1002
DROP TABLE t1,t2;
1003 1004 1005 1006 1007 1008 1009 1010
CREATE TABLE t1 (c11 int);
CREATE TABLE t2 (c21 int);
INSERT INTO t1 VALUES (30), (40), (50);
INSERT INTO t2 VALUES (300), (400), (500);
SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
c11	c21
40	NULL
DROP TABLE t1, t2;
unknown's avatar
unknown committed
1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132
CREATE TABLE t1 (a int PRIMARY KEY, b int);
CREATE TABLE t2 (a int PRIMARY KEY, b int);
INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
a	b	a	b
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
a	b	a	b
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
a	b	a	b
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
8	7	NULL	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
8	7	NULL	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
8	7	NULL	NULL
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;
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
8	7	NULL	NULL
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);
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
8	7	NULL	NULL
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);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
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);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
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);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
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));
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
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;
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
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));
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1133
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	4	
unknown's avatar
unknown committed
1134 1135 1136
1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1137 1138
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	4	Using where
1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	
unknown's avatar
unknown committed
1139 1140
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);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1141 1142
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	4	Using where
1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	
unknown's avatar
unknown committed
1143
DROP TABLE t1,t2;
1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165
DROP VIEW IF EXISTS v1,v2;
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a int);
CREATE table t2 (b int);
INSERT INTO t1 VALUES (1), (2), (3), (4), (1), (1), (3);
INSERT INTO t2 VALUES (2), (3);
CREATE VIEW v1 AS SELECT a FROM t1 JOIN t2 ON t1.a=t2.b;
CREATE VIEW v2 AS SELECT b FROM t2 JOIN t1 ON t2.b=t1.a;
SELECT v1.a, v2. b 
FROM v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3)
GROUP BY v1.a;
a	b
2	NULL
3	3
SELECT v1.a, v2. b 
FROM { OJ v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3) }
GROUP BY v1.a;
a	b
2	NULL
3	3
DROP VIEW v1,v2;
DROP TABLE t1,t2;
unknown's avatar
unknown committed
1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200
CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t2 VALUES (2), (3);
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
a	b
1	NULL
2	2
3	3
4	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
a	b
1	NULL
2	2
3	3
4	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
a	b
1	NULL
2	2
3	3
4	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
a	b
1	NULL
2	2
3	3
4	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
a	b
1	NULL
2	2
3	3
4	NULL
DROP TABLE t1,t2;
unknown's avatar
unknown committed
1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220
CREATE TABLE t1 (
f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
f2 varchar(16) collate latin1_swedish_ci
);
CREATE TABLE t2 (
f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
f3 varchar(16) collate latin1_swedish_ci
);
INSERT INTO t1 VALUES ('bla','blah');
INSERT INTO t2 VALUES ('bla','sheep');
SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
f1	f2	f3
bla	blah	sheep
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
f1	f2	f3
bla	blah	sheep
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
f1	f2	f3
bla	blah	sheep
DROP TABLE t1,t2;
1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240
CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
INSERT INTO t1 VALUES
(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
INSERT INTO t2 VALUES
(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
EXPLAIN
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
1	SIMPLE	t2	ref	idx	idx	4	test.t1.id	2	Using where; Not exists
flush status;
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
id	a
1	aaaaaaa
4	ddddddd
show status like 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	5
1241
Handler_read_last	0
1242 1243 1244 1245 1246
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	6
DROP TABLE t1,t2;
1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261
CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
INSERT INTO t1 VALUES (1,0), (2,1);
CREATE TABLE t2 (d int PRIMARY KEY);
INSERT INTO t2 VALUES (1), (2), (3);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	3	Using where; Using index; Not exists
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
c	e	d
1	0	NULL
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
c	e	d
1	0	NULL
DROP TABLE t1,t2;
1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296
#
# Bug#47650: using group by with rollup without indexes returns incorrect 
# results with where
#
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 ( a INT, b INT );
INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5);
EXPLAIN
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 LEFT JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 LEFT JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
a	COUNT( t2.b )	SUM( t2.b )	MAX( t2.b )
1	3	6	3
NULL	3	6	3
EXPLAIN
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using filesort
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
a	COUNT( t2.b )	SUM( t2.b )	MAX( t2.b )
1	3	6	3
NULL	3	6	3
DROP TABLE t1, t2;
1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309
#
# Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison
#
CREATE TABLE t1(f1 INT, f2 INT, f3 INT);
INSERT INTO t1 VALUES (1, NULL, 3);
CREATE TABLE t2(f1 INT, f2 INT);
INSERT INTO t2 VALUES (2, 1);
EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
Warnings:
1310
Note	1003	select '1' AS `f1`,NULL AS `f2`,'3' AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where ((coalesce('1',NULL),'3') in ((1,3),(2,2)))
1311 1312 1313 1314 1315
SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
f1	f2	f3	f1	f2
1	NULL	3	NULL	NULL
DROP TABLE t1, t2;
Martin Hansson's avatar
Martin Hansson committed
1316
#
1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342
# Bug#52357: Assertion failed: join->best_read in greedy_search 
# optimizer_search_depth=0
#
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
SET optimizer_search_depth = 0;
# Should not core dump on query preparation
EXPLAIN
SELECT 1
FROM t1 tt3 LEFT  OUTER JOIN t1 tt4 ON 1
LEFT  OUTER JOIN t1 tt5 ON 1
LEFT  OUTER JOIN t1 tt6 ON 1
LEFT  OUTER JOIN t1 tt7 ON 1
LEFT  OUTER JOIN t1 tt8 ON 1
RIGHT OUTER JOIN t1 tt2 ON 1
RIGHT OUTER JOIN t1 tt1 ON 1
STRAIGHT_JOIN    t1 tt9 ON 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tt1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	tt2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	tt3	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	tt4	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	tt5	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	tt6	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	tt7	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	tt8	ALL	NULL	NULL	NULL	NULL	2	
Martin Hansson's avatar
Martin Hansson committed
1343
1	SIMPLE	tt9	ALL	NULL	NULL	NULL	NULL	2	Using join buffer
1344 1345
SET optimizer_search_depth = DEFAULT;
DROP TABLE t1;
1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369
#
# Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result
#
CREATE TABLE t1 (f1 INT NOT NULL);
INSERT INTO t1 VALUES (9),(0);
CREATE TABLE t2 (f1 INT NOT NULL);
INSERT INTO t2 VALUES
(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1);
SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1
RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
COUNT(*)
476
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1
RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	TA2	ALL	NULL	NULL	NULL	NULL	20	Using where
1	SIMPLE	TA3	ALL	NULL	NULL	NULL	NULL	20	Using join buffer
1	SIMPLE	TA1	ALL	NULL	NULL	NULL	NULL	2	
DROP TABLE t1, t2;
#
# Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990
#
CREATE TABLE t1(f1 INT, PRIMARY KEY (f1));
INSERT INTO t1 VALUES (1),(2);
Sergey Glukhov's avatar
Sergey Glukhov committed
1370 1371 1372 1373 1374
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
LEFT JOIN t1 AS jt2
RIGHT JOIN t1 AS jt3
JOIN t1 AS jt4 ON 1
LEFT JOIN t1 AS jt5 ON 1
1375
ON 1
Sergey Glukhov's avatar
Sergey Glukhov committed
1376
RIGHT JOIN t1 AS jt6 ON jt6.f1
1377 1378
ON 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
Sergey Glukhov's avatar
Sergey Glukhov committed
1379 1380 1381 1382 1383 1384
1	SIMPLE	jt1	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt6	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt3	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt4	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt5	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt2	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1385
Warnings:
Sergey Glukhov's avatar
Sergey Glukhov committed
1386 1387 1388 1389 1390 1391
Note	1003	select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
RIGHT JOIN t1 AS jt2
RIGHT JOIN t1 AS jt3
JOIN t1 AS jt4 ON 1
LEFT JOIN t1 AS jt5 ON 1
1392
ON 1
Sergey Glukhov's avatar
Sergey Glukhov committed
1393
RIGHT JOIN t1 AS jt6 ON jt6.f1
1394 1395
ON 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
Sergey Glukhov's avatar
Sergey Glukhov committed
1396 1397 1398 1399 1400 1401
1	SIMPLE	jt6	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt3	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt4	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt5	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt2	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt1	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1402
Warnings:
Sergey Glukhov's avatar
Sergey Glukhov committed
1403
Note	1003	select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1
1404
DROP TABLE t1;
1405
End of 5.1 tests