join_outer.result 23.7 KB
Newer Older
1 2 3 4 5
drop table if exists t1,t2,t3,t4,t5;
CREATE TABLE t1 (
grp int(11) default NULL,
a bigint(20) unsigned default NULL,
c char(10) NOT NULL default ''
6
) ENGINE=MyISAM;
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
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
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
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
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
51 52 53
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
54
select t1.*,t2.* from t1 left join t2 using (a,c);
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
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
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
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
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
92
explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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
95
explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
96
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
97
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	
98
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.a	1	Using where
99
select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
100 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
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
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
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
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
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
select t1.*,t2.* from t1 natural join t2;
127 128
grp	a	c	id	d
1	1	a	1	1
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
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
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
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
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
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
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,
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,
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
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
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
285 286
drop table t1,t2,t3,test.t4;
CREATE TABLE t1 (
287
id smallint(5) unsigned NOT NULL auto_increment,
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,
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
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
312
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
313
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
314
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
315
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Not exists
316
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
317
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
318
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
319
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
320
select count(*) from t1 left join t2 on (t1.id = t2.owner);
321 322
count(*)
4
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
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
332
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
333
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
334
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
335
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Not exists
336
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
337
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
338
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
339
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
340
select count(*) from t2 right join t1 on (t1.id = t2.owner);
341 342
count(*)
4
343
select t1.name, t2.name, t2.id,t3.id from t2 right join t1 on (t1.id = t2.owner) left join t1 as t3 on t3.id=t2.owner;
344 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
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
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 359 360 361 362 363 364 365 366
name	name	id	owner	id
Antonio Paz	El Gato	1	1	1
Antonio Paz	Perrito	2	1	1
Lilliana Angelovska	NULL	NULL	NULL	1
Thimble Smith	NULL	NULL	NULL	1
Antonio Paz	NULL	NULL	NULL	2
Lilliana Angelovska	NULL	NULL	NULL	2
Thimble Smith	NULL	NULL	NULL	2
Antonio Paz	NULL	NULL	NULL	3
Lilliana Angelovska	NULL	NULL	NULL	3
Thimble Smith	Happy	3	3	3
367 368 369
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");
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
370
select * from t1 where str is not null order by id;
371 372
id	str
3	foo
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
373
4	bar
374
select * from t1 where str is null;
375 376 377
id	str
1	NULL
2	NULL
378 379
drop table t1;
CREATE TABLE t1 (
380
t1_id bigint(21) NOT NULL auto_increment,
381 382 383
PRIMARY KEY (t1_id)
);
CREATE TABLE t2 (
384
t2_id bigint(21) NOT NULL auto_increment,
385 386 387
PRIMARY KEY (t2_id)
);
CREATE TABLE t3 (
388
t3_id bigint(21) NOT NULL auto_increment,
389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408
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;
409
ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
410 411 412 413 414 415 416
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;
417 418 419 420 421
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
422 423
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;
424 425 426 427 428
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
429 430 431 432 433 434 435 436 437 438
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;
439 440
id2
3
441
SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
442 443
id2
3
444 445 446 447 448 449 450 451 452 453 454 455 456
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;
457 458 459 460 461 462
color	name
red	apple
yellow	banana
green	lime
black	grape
blue	blueberry
463
select * from t2;
464 465 466 467 468
count	color
10	green
5	black
15	white
7	green
469
select * from t2 natural join t1;
470 471 472 473
count	color	name
10	green	lime
7	green	lime
5	black	grape
474
select t2.count, t1.name from t2 natural join t1;
475 476 477 478
count	name
10	lime
7	lime
5	grape
479
select t2.count, t1.name from t2 inner join t1 using (color);
480 481 482 483
count	name
10	lime
7	lime
5	grape
484 485 486 487 488 489 490 491 492 493 494 495 496
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;
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 533 534 535 536 537 538
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
539
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;
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 576 577 578 579 580 581
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
582 583 584 585 586 587 588 589 590 591 592
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;
593 594 595
id	pid	rep_del	id	pid	rep_del
1	NULL	NULL	2	1	NULL
2	1	NULL	NULL	NULL	NULL
596 597
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;
598 599 600
id	pid	rep_del	id	pid	rep_del
1	NULL	NULL	2	1	NULL
2	1	NULL	NULL	NULL	NULL
601 602 603 604 605 606 607 608 609 610 611 612 613 614
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;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
615
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
616
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
617
1	SIMPLE	t2	ref	id	id	4	test.t1.id	1	Using where; Using index; Not exists
618
SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
619 620
id	name	id	idx
2	no	NULL	NULL
621
drop table t1,t2;
622 623 624 625 626 627 628 629
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;
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
630 631 632 633 634
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;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
635
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
636
1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	3	Using index
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
637
1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	2	const	1	Using where; Using index
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
638 639 640 641 642 643 644 645 646 647 648
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;
649 650 651 652 653 654 655 656 657 658 659
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;
660 661 662 663 664 665
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);
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
666
ERROR 42S22: Unknown column 'test.t2.f3' in 'on clause'
667
drop table t1,t2,t3;
hf@deer.(none)'s avatar
hf@deer.(none) committed
668 669 670 671 672 673 674 675 676 677 678 679
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;
monty@mysql.com's avatar
monty@mysql.com committed
680 681 682 683
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	
hf@deer.(none)'s avatar
hf@deer.(none) committed
684
drop table t1, t2, t3;