join_outer.result 30.4 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
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
48
select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
49 50
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
unknown's avatar
unknown committed
51 52 53
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
54
select t1.*,t2.* from t1 left join t2 using (a,c);
unknown's avatar
unknown committed
55 56 57 58
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
59 60 61 62
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
63
select t1.*,t2.* from t1 left join t2 using (c);
64 65 66 67 68 69 70 71 72
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
unknown's avatar
unknown committed
73
select t1.*,t2.* from t1 natural left outer join t2;
74 75 76 77 78 79 80 81
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);
109
ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
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);
111
ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
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);
113
ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
unknown's avatar
unknown committed
114
select t1.*,t2.* from t1 inner join t2 using (a);
115 116 117 118 119
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
unknown's avatar
unknown committed
126
select t1.*,t2.* from t1 natural join t2;
127 128
grp	a	c	id	d
1	1	a	1	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 203 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
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);
INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Clculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
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;
403
ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
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
unknown's avatar
unknown committed
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;
464 465 466 467
count	color	name
10	green	lime
7	green	lime
5	black	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 650 651 652 653
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;
i	i	i
1	NULL	NULL
2	2	2
drop table t1,t2,t3;
unknown's avatar
unknown committed
654 655 656 657 658 659
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
660
ERROR 42S22: Unknown column 'test.t2.f3' in 'on clause'
unknown's avatar
unknown committed
661
drop table t1,t2,t3;
unknown's avatar
unknown committed
662 663 664 665 666 667 668 669 670 671 672 673
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
674 675 676 677
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
678
drop table t1, t2, t3;
unknown's avatar
unknown committed
679
create table t1 (
680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698
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
699
create table t1 (
unknown's avatar
unknown committed
700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725
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	
726
1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
unknown's avatar
unknown committed
727 728 729 730 731
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
732
1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
unknown's avatar
unknown committed
733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751
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
752
1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
unknown's avatar
unknown committed
753 754 755 756 757 758 759 760 761 762 763 764 765 766 767
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;
768 769 770 771 772 773 774 775 776 777 778 779 780 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
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;
807
CREATE TABLE t1 (
808 809 810 811
id int(11) unsigned NOT NULL auto_increment,
text_id int(10) unsigned default NULL,
PRIMARY KEY  (id)
);
812 813 814
INSERT INTO t1 VALUES("1", "0");
INSERT INTO t1 VALUES("2", "10");
CREATE TABLE t2 (
815 816 817 818 819
text_id char(3) NOT NULL default '',
language_id char(3) NOT NULL default '',
text_data text,
PRIMARY KEY  (text_id,language_id)
);
820 821 822 823 824 825 826 827 828
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 '%');
829 830 831
id	text_id	text_data
1	0	0-SV
2	10	10-SV
832
DROP TABLE t1, t2;
833 834 835 836 837 838 839 840 841 842 843 844 845 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
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	
876 877 878 879 880 881 882 883 884 885 886 887 888 889 890
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
891
DROP TABLE t0,t1,t2,t3;
892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913
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
DROP TABLE t1,t2;