join_outer.result 37 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 1
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 601 602 603 604 605 606 607 608
drop table t1;
CREATE TABLE t1 (
id int(11) DEFAULT '0' NOT NULL,
name tinytext DEFAULT '' NOT NULL,
UNIQUE id (id)
);
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
609
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
610
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
unknown's avatar
unknown committed
611
1	SIMPLE	t2	ref	id	id	4	test.t1.id	1	Using where; Using index; Not exists
unknown's avatar
unknown committed
612
SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
613 614
id	name	id	idx
2	no	NULL	NULL
unknown's avatar
unknown committed
615
drop table t1,t2;
unknown's avatar
unknown committed
616 617 618 619 620 621 622 623
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
624 625 626 627 628
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
629
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
630
1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	3	Using index
631
1	SIMPLE	t1	const	PRIMARY	PRIMARY	2	const	1	Using index
unknown's avatar
unknown committed
632 633 634 635 636 637 638 639 640 641 642
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;
643 644 645 646 647 648 649
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
650 651 652
i
1
2
653 654 655 656 657 658 659
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
660 661 662 663 664 665
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
666
drop table t1,t2,t3;
unknown's avatar
unknown committed
667 668 669 670 671 672
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
673
f3	f2	f1	f4	f5
unknown's avatar
unknown committed
674
drop table t1,t2,t3;
unknown's avatar
unknown committed
675 676 677 678 679 680 681 682 683 684 685 686
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
687 688 689 690
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
691
drop table t1, t2, t3;
unknown's avatar
unknown committed
692
create table t1 (
693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711
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
712
create table t1 (
unknown's avatar
unknown committed
713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738
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
1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	10	
739
1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
unknown's avatar
unknown committed
740 741 742 743 744
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
745
1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
unknown's avatar
unknown committed
746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764
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
765
1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
unknown's avatar
unknown committed
766 767 768 769 770 771 772 773 774 775 776 777 778 779 780
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;
781 782 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
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;
820
CREATE TABLE t1 (
821 822 823 824
id int(11) unsigned NOT NULL auto_increment,
text_id int(10) unsigned default NULL,
PRIMARY KEY  (id)
);
825 826 827
INSERT INTO t1 VALUES("1", "0");
INSERT INTO t1 VALUES("2", "10");
CREATE TABLE t2 (
828 829 830 831 832
text_id char(3) NOT NULL default '',
language_id char(3) NOT NULL default '',
text_data text,
PRIMARY KEY  (text_id,language_id)
);
833 834 835 836 837 838 839 840 841
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 '%');
842 843 844
id	text_id	text_data
1	0	0-SV
2	10	10-SV
845
DROP TABLE t1, t2;
846 847 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
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	
889 890 891 892 893 894 895 896 897 898 899 900 901 902 903
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
904
DROP TABLE t0,t1,t2,t3;
905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925
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
926 927 928 929 930 931
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
932
DROP TABLE t1,t2;
933 934 935 936 937
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");
938 939 940 941 942 943 944 945 946 947 948 949
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:
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
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:
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
unknown's avatar
unknown committed
950
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
951 952 953 954 955
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
unknown's avatar
unknown committed
956
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
957 958 959 960 961 962 963
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
drop table t1, t2;
set group_concat_max_len=default;
964 965 966 967 968 969 970 971 972 973 974 975 976 977
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;
978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994
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
995
DROP VIEW v1;
996
DROP TABLE t1,t2;
997 998 999 1000 1001 1002 1003 1004
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
1005 1006 1007 1008 1009 1010 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
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
1127
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	4	Using where
unknown's avatar
unknown committed
1128 1129 1130
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
1131 1132
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
1133 1134
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
1135 1136
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
1137
DROP TABLE t1,t2;