union.result 26 KB
Newer Older
1
drop table if exists t1,t2,t3,t4,t5,t6;
2 3 4 5
CREATE TABLE t1 (a int not null, b char (10) not null);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
CREATE TABLE t2 (a int not null, b char (10) not null);
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
6
select a,b from t1 union distinct select a,b from t2;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
7 8 9 10 11 12 13
a	b
1	a
2	b
3	c
4	d
5	f
6	e
14
select a,b from t1 union all select a,b from t2;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
15 16 17 18 19 20 21 22 23
a	b
1	a
2	b
3	c
3	c
3	c
4	d
5	f
6	e
24
select a,b from t1 union all select a,b from t2 order by b;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
25 26 27 28 29 30 31 32 33
a	b
1	a
2	b
3	c
3	c
3	c
4	d
6	e
5	f
34
select a,b from t1 union all select a,b from t2 union select 7,'g';
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
35 36 37 38 39 40 41 42
a	b
1	a
2	b
3	c
4	d
5	f
6	e
7	g
43
select 0,'#' union select a,b from t1 union all select a,b from t2 union select 7,'gg';
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
44 45 46 47 48 49 50 51
0	#
0	#
1	a
2	b
3	c
4	d
5	f
6	e
52
7	gg
53
select a,b from t1 union select a,b from t1;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
54 55 56 57
a	b
1	a
2	b
3	c
58
select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
59 60 61 62 63 64 65 66
t1	b	count(*)
t1	a	1
t1	b	1
t1	c	2
t2	c	1
t2	d	1
t2	e	1
t2	f	1
67 68 69 70 71 72
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 4;
a	b
1	a
2	b
3	c
4	d
73 74 75
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1);
a	b
1	a
76 77
2	b
3	c
78 79 80 81 82
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by b desc;
a	b
3	c
2	b
1	a
83
(select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by t1.b;
84
ERROR 42000: Table 't1' from one of SELECT's can not be used  in global ORDER clause
85
explain extended (select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by b desc;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
86
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
87
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
88
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	4	Using filesort
89 90
Warnings:
Note	1003	(select high_priority test.t1.a AS `a`,test.t1.b AS `b` from test.t1 limit 2) union all (select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 order by test.t2.a limit 1) order by b desc
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
91 92 93 94 95 96 97
(select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 2;
a	b
1	a
2	b
select found_rows();
found_rows()
6
98 99 100 101 102 103
select sql_calc_found_rows  a,b from t1  union all select a,b from t2 limit 2;
a	b
1	a
2	b
select found_rows();
found_rows()
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
104
8
105
explain select a,b from t1 union all select a,b from t2;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
106
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
107
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
108
2	UNION	t2	ALL	NULL	NULL	NULL	NULL	4	
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
109
explain select xx from t1 union select 1;
110
ERROR 42S22: Unknown column 'xx' in 'field list'
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
111
explain select a,b from t1 union select 1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
112
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
113
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
114
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
115
explain select 1 union select a,b from t1 union select 1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
116
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
117
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
118
2	UNION	t1	ALL	NULL	NULL	NULL	NULL	4	
119
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
120
explain select a,b from t1 union select 1 limit 0;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
121
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
122 123
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
124
select a,b from t1 into outfile 'skr' union select a,b from t2;
125
ERROR HY000: Wrong usage of UNION and INTO
126
select a,b from t1 order by a union select a,b from t2;
127
ERROR HY000: Wrong usage of UNION and ORDER BY
128
insert into t3 select a from t1 order by a union select a from t2;
129
ERROR HY000: Wrong usage of UNION and ORDER BY
130
create table t3 select a,b from t1 union select a from t2;
131
ERROR 21000: The used SELECT statements have a different number of columns
132
select a,b from t1 union select a from t2;
133
ERROR 21000: The used SELECT statements have a different number of columns
134
select * from t1 union select a from t2;
135
ERROR 21000: The used SELECT statements have a different number of columns
136
select a from t1 union select * from t2;
137
ERROR 21000: The used SELECT statements have a different number of columns
138
select * from t1 union select SQL_BUFFER_RESULT * from t2;
139
ERROR 42000: Wrong usage/placement of 'SQL_BUFFER_RESULT'
140 141 142 143
create table t3 select a,b from t1 union all select a,b from t2;
insert into t3 select a,b from t1 union all select a,b from t2;
replace into t3 select a,b as c from t1 union all select a,b from t2;
drop table t1,t2,t3;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
144
select * union select 1;
145
ERROR HY000: No tables used
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
146 147 148 149
select 1 as a,(select a union select a);
a	(select a union select a)
1	1
(select 1) union (select 2) order by 0;
150
ERROR 42S22: Unknown column '0' in 'order clause'
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
151 152 153 154 155
SELECT @a:=1 UNION SELECT @a:=@a+1;
@a:=1
1
2
(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a);
156
ERROR 42S22: Unknown column 'a' in 'field list'
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
157 158 159 160
(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);
1	3
1	3
2	1
161 162 163 164 165 166
CREATE TABLE t1 (
`pseudo` char(35) NOT NULL default '',
`pseudo1` char(35) NOT NULL default '',
`same` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY  (`pseudo1`),
KEY `pseudo` (`pseudo`)
167
) ENGINE=MyISAM;
168 169
INSERT INTO t1 (pseudo,pseudo1,same) VALUES ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1);
SELECT pseudo FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo FROM t1 WHERE pseudo='joce';
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
170 171 172
pseudo
dekad
joce
173
SELECT pseudo1 FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t1 WHERE pseudo='joce';
174 175 176 177
pseudo1
joce
testtt
tsestset
178
SELECT * FROM t1 WHERE pseudo1='joce' UNION SELECT * FROM t1 WHERE pseudo='joce' order by pseudo desc,pseudo1 desc;
179 180
pseudo	pseudo1	same
joce	tsestset	1
181 182
joce	testtt	1
dekad	joce	1
183
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT pseudo FROM t1 WHERE pseudo1='joce';
184 185 186 187
pseudo1
testtt
tsestset
dekad
188
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION ALL SELECT pseudo FROM t1 WHERE pseudo1='joce';
189 190 191 192
pseudo1
testtt
tsestset
dekad
193
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT 1;
194 195 196 197
pseudo1
testtt
tsestset
1
198
drop table t1;
199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
create table t1 (a int);
create table t2 (a int);
insert into t1 values (1),(2),(3),(4),(5);
insert into t2 values (11),(12),(13),(14),(15);
(select * from t1 limit 2) union (select * from t2 limit 3) limit 4;
a
1
2
11
12
(select * from t1 limit 2) union (select * from t2 limit 3);
a
1
2
11
12
13
216 217 218 219 220
(select * from t1 limit 2) union (select * from t2 limit 20,3);
a
1
2
set SQL_SELECT_LIMIT=2;
221
(select * from t1 limit 1) union (select * from t2 limit 3);
222 223
a
1
224
11
225
set SQL_SELECT_LIMIT=DEFAULT;
226
drop table t1,t2;
227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283
CREATE TABLE t1 (
cid smallint(5) unsigned NOT NULL default '0',
cv varchar(250) NOT NULL default '',
PRIMARY KEY  (cid),
UNIQUE KEY cv (cv)
) ;
INSERT INTO t1 VALUES (8,'dummy');
CREATE TABLE t2 (
cid bigint(20) unsigned NOT NULL auto_increment,
cap varchar(255) NOT NULL default '',
PRIMARY KEY  (cid),
KEY cap (cap)
) ;
CREATE TABLE t3 (
gid bigint(20) unsigned NOT NULL auto_increment,
gn varchar(255) NOT NULL default '',
must tinyint(4) default NULL,
PRIMARY KEY  (gid),
KEY gn (gn)
) ;
INSERT INTO t3 VALUES (1,'V1',NULL);
CREATE TABLE t4 (
uid bigint(20) unsigned NOT NULL default '0',
gid bigint(20) unsigned default NULL,
rid bigint(20) unsigned default NULL,
cid bigint(20) unsigned default NULL,
UNIQUE KEY m (uid,gid,rid,cid),
KEY uid (uid),
KEY rid (rid),
KEY cid (cid),
KEY container (gid,rid,cid)
) ;
INSERT INTO t4 VALUES (1,1,NULL,NULL);
CREATE TABLE t5 (
rid bigint(20) unsigned NOT NULL auto_increment,
rl varchar(255) NOT NULL default '',
PRIMARY KEY  (rid),
KEY rl (rl)
) ;
CREATE TABLE t6 (
uid bigint(20) unsigned NOT NULL auto_increment,
un varchar(250) NOT NULL default '',
uc smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY  (uid),
UNIQUE KEY nc (un,uc),
KEY un (un)
) ;
INSERT INTO t6 VALUES (1,'test',8);
SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test";
uid	rl	g1	cid	gg
1	NULL	V1	NULL	1
SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test";
uid	rl	g1	cid	gg
(SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test");
uid	rl	g1	cid	gg
1	NULL	V1	NULL	1
drop table t1,t2,t3,t4,t5,t6;
284 285 286 287 288
CREATE TABLE t1 (a int not null, b char (10) not null);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
CREATE TABLE t2 (a int not null, b char (10) not null);
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
create table t3 select a,b from t1 union select a,b from t2;
289
create table t4 (select a,b from t1) union (select a,b from t2) limit 2;
290
insert into  t4 select a,b from t1 union select a,b from t2;
291
insert into  t3 (select a,b from t1) union (select a,b from t2) limit 2;
292
select * from t3;
293 294 295 296 297 298 299 300 301
a	b
1	a
2	b
3	c
4	d
5	f
6	e
1	a
2	b
302
select * from t4;
303 304 305 306 307 308 309 310 311
a	b
1	a
2	b
1	a
2	b
3	c
4	d
5	f
6	e
312
drop table t1,t2,t3,t4;
313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348
create table t1 (a int);
insert into t1 values (1),(2),(3);
create table t2 (a int);
insert into t2 values (3),(4),(5);
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1;
a
1
select found_rows();
found_rows()
6
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;
a
1
3
select found_rows();
found_rows()
4
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);
a
1
3
4
5
select found_rows();
found_rows()
4
(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);
a
1
2
3
3
select found_rows();
found_rows()
4
(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
349
ERROR 42000: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;
a
1
3
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
a
1
2
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;
a
1
2
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
a
1
2
3
4
5
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
a
1
2
3
4
5
select found_rows();
found_rows()
5
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
a
1
3
4
5
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
a
1
3
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
a
3
410
4
411 412 413 414 415 416 417 418 419 420 421 422 423
select found_rows();
found_rows()
6
SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
a
3
4
5
select found_rows();
found_rows()
5
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
a
424
5
425 426 427 428 429 430 431
(SELECT * FROM t1 ORDER by a) UNION ALL (SELECT * FROM t2 ORDER BY a) ORDER BY A desc LIMIT 4;
a
5
4
3
3
(SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
432
ERROR 42000: Wrong usage/placement of 'SQL_CALC_FOUND_ROWS'
433
create temporary table t1 select a from t1 union select a from t2;
monty@mysql.com's avatar
monty@mysql.com committed
434
drop temporary table t1;
435
create table t1 select a from t1 union select a from t2;
monty@mysql.com's avatar
monty@mysql.com committed
436
ERROR HY000: You can't specify target table 't1' for update in FROM clause
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
437
select a from t1 union select a from t2 order by t2.a;
438
ERROR 42S02: Unknown table 't2' in order clause
439
drop table t1,t2;
440 441 442 443
select length(version()) > 1 as `*` UNION select 2;
*
1
2
444
CREATE TABLE t1 (  id int(3) unsigned default '0') ENGINE=MyISAM;
445
INSERT INTO t1 (id) VALUES("1");
446
CREATE TABLE t2 ( id int(3) unsigned default '0',  id_master int(5) default '0',  text1 varchar(5) default NULL,  text2 varchar(5) default NULL) ENGINE=MyISAM;
447 448 449 450 451 452 453 454 455 456 457
INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1",
"foo1", "bar1");
INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1",
"foo2", "bar2");
INSERT INTO t2 (id, id_master, text1, text2) VALUES("3", "1", NULL,
"bar3");
INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1",
"foo4", "bar4");
SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;
id_master	id	text1	text2
1	1	NULL	ABCDE
458 459
1	1	foo1	bar1
1	2	foo2	bar2
460
1	3	NULL	bar3
461
1	4	foo4	bar4
462 463 464 465 466 467 468 469
SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;
id_master	id	text1	text2
1	1	ABCDE	ABCDE
1	1	foo1	bar1
1	2	foo2	bar2
1	3	NULL	bar3
1	4	foo4	bar4
drop table if exists t1,t2;
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
470 471 472 473
create table t1 (a int not null primary key auto_increment, b int, key(b));
create table t2 (a int not null primary key auto_increment, b int);
insert into t1 (b) values (1),(2),(2),(3);
insert into t2 (b) values (10),(11),(12),(13);
474
explain extended (select * from t1 where a=1) union (select * from t2 where a=1);
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
475 476 477
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	
2	UNION	t2	const	PRIMARY	PRIMARY	4	const	1	
478 479
Warnings:
Note	1003	(select high_priority test.t1.a AS `a`,test.t1.b AS `b` from test.t1 where (test.t1.a = 1)) union (select test.t2.a AS `a`,test.t2.b AS `b` from test.t2 where (test.t2.a = 1))
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
480 481 482 483 484 485 486 487 488 489 490 491 492 493 494
(select * from t1 where a=5) union (select * from t2 where a=1);
a	b
1	10
(select * from t1 where a=5 and a=6) union (select * from t2 where a=1);
a	b
1	10
(select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1);
a	b
1	10
(select * from t1 where a=1) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);
a	b
1	1
2	2
3	3
4	4
495
explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t2.a from t1,t2 where t1.a=t2.a);
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
496 497 498
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	UNION	t1	index	PRIMARY	PRIMARY	4	NULL	4	Using index
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
499
2	UNION	t2	index	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
500 501 502 503 504
explain (select * from t1 where a=1) union (select * from t1 where b=1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	
2	UNION	t1	ref	b	b	5	const	1	Using where
drop table t1,t2;
505 506 507 508 509 510 511 512 513 514 515 516 517
create table t1 (   id int not null auto_increment, primary key (id)   ,user_name text );
create table t2 (    id int not null auto_increment, primary key (id)   ,group_name text );
create table t3 (    id int not null auto_increment, primary key (id)   ,user_id int   ,index user_idx (user_id)   ,foreign key (user_id) references users(id)   ,group_id int   ,index group_idx (group_id)   ,foreign key (group_id) references groups(id) );
insert into t1 (user_name) values ('Tester');
insert into t2 (group_name) values ('Group A');
insert into t2 (group_name) values ('Group B');
insert into t3 (user_id, group_id) values (1,1);
select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION  select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c;
is_in_group	user_name	group_name	id
1	Tester	Group A	1
0	Tester	Group A	NULL
0	Tester	Group B	NULL
drop table t1, t2, t3;
518 519 520 521 522 523 524 525 526 527 528 529 530 531
create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id union SELECT 0, 0;
pla_id	matintnum
100	a
101	a
102	a
103	b
104	b
105	c
0	0
drop table t1, t2;
532 533 534 535 536 537 538 539
create table t1 SELECT "a" as a UNION select "aa" as a;
select * from t1;
a
a
aa
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
540
  `a` char(2) NOT NULL default ''
541
) ENGINE=MyISAM DEFAULT CHARSET=latin1
542 543 544 545 546 547 548 549 550
drop table t1;
create table t1 SELECT 12 as a UNION select "aa" as a;
select * from t1;
a
12
aa
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
551
  `a` char(2) NOT NULL default ''
552
) ENGINE=MyISAM DEFAULT CHARSET=latin1
553 554 555 556 557 558 559 560 561
drop table t1;
create table t1 SELECT 12 as a UNION select 12.2 as a;
select * from t1;
a
12.0
12.2
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
562
  `a` double(4,1) NOT NULL default '0.0'
563
) ENGINE=MyISAM DEFAULT CHARSET=latin1
564
drop table t1;
565 566
create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text);
insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, 1972, '1972-10-22', '1972-10-22 11:50', 'testc', 'testv', 'tetetetetest', 'teeeeeeeeeeeest');
567 568 569 570 571 572 573 574 575
create table t1 SELECT it2 from t2 UNION select it1 from t2;
select * from t1;
it2
1
NULL
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `it2` tinyint(4) default NULL
576
) ENGINE=MyISAM DEFAULT CHARSET=latin1
577 578 579 580 581 582 583 584 585
drop table t1;
create table t1 SELECT it2 from t2 UNION select i from t2;
select * from t1;
it2
1
3
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
586
  `it2` int(11) NOT NULL default '0'
587
) ENGINE=MyISAM DEFAULT CHARSET=latin1
588 589 590 591 592 593 594 595 596
drop table t1;
create table t1 SELECT i from t2 UNION select f from t2;
select * from t1;
i
3
1.5
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
597
  `i` double default NULL
598
) ENGINE=MyISAM DEFAULT CHARSET=latin1
599 600 601 602 603 604 605 606 607 608
drop table t1;
create table t1 SELECT f from t2 UNION select d from t2;
select * from t1;
f
1.5
2.5
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f` double default NULL
609
) ENGINE=MyISAM DEFAULT CHARSET=latin1
610
drop table t1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
611 612 613 614 615 616 617 618 619
create table t1 SELECT ib from t2 UNION select f from t2;
select * from t1;
ib
4
1.5
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `ib` double default NULL
620
) ENGINE=MyISAM DEFAULT CHARSET=latin1
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
621 622 623 624 625 626 627 628 629 630
drop table t1;
create table t1 SELECT ib from t2 UNION select d from t2;
select * from t1;
ib
4
2.5
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `ib` double default NULL
631
) ENGINE=MyISAM DEFAULT CHARSET=latin1
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
632
drop table t1;
633 634 635 636 637 638 639 640
create table t1 SELECT f from t2 UNION select y from t2;
select * from t1;
f
1.5
1972
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
641
  `f` float default NULL
642
) ENGINE=MyISAM DEFAULT CHARSET=latin1
643 644 645 646 647 648 649 650 651
drop table t1;
create table t1 SELECT f from t2 UNION select da from t2;
select * from t1;
f
1.5
1972-10-22
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
652
  `f` binary(12) default NULL
653
) ENGINE=MyISAM DEFAULT CHARSET=latin1
654 655 656 657 658 659 660 661 662
drop table t1;
create table t1 SELECT y from t2 UNION select da from t2;
select * from t1;
y
1972
1972-10-22
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
663
  `y` binary(10) default NULL
664
) ENGINE=MyISAM DEFAULT CHARSET=latin1
665 666 667 668 669 670 671 672 673
drop table t1;
create table t1 SELECT y from t2 UNION select dt from t2;
select * from t1;
y
1972
1972-10-22 11:50:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
674
  `y` binary(19) default NULL
675
) ENGINE=MyISAM DEFAULT CHARSET=latin1
676 677 678 679 680 681 682 683 684 685
drop table t1;
create table t1 SELECT da from t2 UNION select dt from t2;
select * from t1;
da
1972-10-22 00:00:00
1972-10-22 11:50:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `da` datetime default NULL
686
) ENGINE=MyISAM DEFAULT CHARSET=latin1
687 688 689 690 691 692 693 694 695
drop table t1;
create table t1 SELECT dt from t2 UNION select sc from t2;
select * from t1;
dt
1972-10-22 11:50:00
testc
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
696
  `dt` binary(19) default NULL
697
) ENGINE=MyISAM DEFAULT CHARSET=latin1
698 699 700 701 702 703 704 705 706
drop table t1;
create table t1 SELECT dt from t2 UNION select sv from t2;
select * from t1;
dt
1972-10-22 11:50:00
testv
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
707
  `dt` binary(19) default NULL
708
) ENGINE=MyISAM DEFAULT CHARSET=latin1
709 710 711 712 713 714 715 716 717 718
drop table t1;
create table t1 SELECT sc from t2 UNION select sv from t2;
select * from t1;
sc
testc
testv
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `sc` varchar(10) default NULL
719
) ENGINE=MyISAM DEFAULT CHARSET=latin1
720 721 722 723 724 725 726 727 728
drop table t1;
create table t1 SELECT dt from t2 UNION select b from t2;
select * from t1;
dt
1972-10-22 11:50:00
tetetetetest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
729
  `dt` blob
730
) ENGINE=MyISAM DEFAULT CHARSET=latin1
731 732 733 734 735 736 737 738 739
drop table t1;
create table t1 SELECT sv from t2 UNION select b from t2;
select * from t1;
sv
testv
tetetetetest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
740
  `sv` longblob
741
) ENGINE=MyISAM DEFAULT CHARSET=latin1
742 743 744 745 746 747 748 749 750 751 752
drop table t1;
create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
select * from t1;
i
3
2.5
tetetetetest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` blob
753
) ENGINE=MyISAM DEFAULT CHARSET=latin1
754 755 756 757 758 759 760 761 762 763
drop table t1;
create table t1 SELECT sv from t2 UNION select tx from t2;
select * from t1;
sv
testv
teeeeeeeeeeeest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `sv` text
764
) ENGINE=MyISAM DEFAULT CHARSET=latin1
765 766 767 768 769 770 771 772 773 774
drop table t1;
create table t1 SELECT b from t2 UNION select tx from t2;
select * from t1;
b
tetetetetest
teeeeeeeeeeeest
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `b` longblob
775
) ENGINE=MyISAM DEFAULT CHARSET=latin1
776
drop table t1,t2;
777 778 779 780 781 782 783 784 785 786 787 788 789
create table t1 (d decimal(10,1));
create table t2 (d decimal(10,9));
insert into t1 values ("100000000.0");
insert into t2 values ("1.23456780");
create table t3 select * from t2 union select * from t1;
select * from t3;
d
1.234567800
100000000.0
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `d` decimal(10,9) default NULL
790
) ENGINE=MyISAM DEFAULT CHARSET=latin1
791 792 793 794 795 796 797 798 799
drop table t1,t2,t3;
create table t1 select 1 union select -1;
select * from t1;
1
1
-1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
800
  `1` bigint(1) NOT NULL default '0'
801
) ENGINE=MyISAM DEFAULT CHARSET=latin1
802 803 804 805 806 807 808
drop table t1;
create table t1 select _latin1"test" union select _latin2"testt" ;
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'UNION'
create table t1 select _latin2"test" union select _latin2"testt" ;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
809
  `test` char(5) character set latin2 NOT NULL default ''
810
) ENGINE=MyISAM DEFAULT CHARSET=latin1
811
drop table t1;
812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831
create table t1 (s char(200));
insert into t1 values (repeat("1",200));
create table t2 select * from t1;
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
set local tmp_table_size=1024;
select count(*) from (select * from t1 union all select * from t2 order by 1) b;
count(*)
21
select count(*) from t1;
count(*)
8
select count(*) from t2;
count(*)
13
drop table t1,t2;
set local tmp_table_size=default;
832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856
create table t1 (a int, index (a), b int);
insert t1 values (1,1),(2,2),(3,3),(4,4),(5,5);
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
insert t1 select a+1, a+b from t1;
FLUSH STATUS;
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	0
select count(*) from t1 where a=7;
count(*)
26
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	0
select count(*) from t1 where b=13;
count(*)
10
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	1
select count(*) from t1 where b=13 union select count(*) from t1 where a=7;
count(*)
857
10
858 859 860 861 862 863 864 865 866 867 868 869
26
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	2
select count(*) from t1 where a=7 union select count(*) from t1 where b=13;
count(*)
26
10
show status like 'Slow_queries';
Variable_name	Value
Slow_queries	3
drop table t1;
870 871 872 873 874 875 876 877 878 879
create table t1 (   RID int(11) not null default '0',   IID int(11) not null default '0',    nada varchar(50)  not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM;
insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');
select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
NAME	PHONE	NAME	PHONE
a	111	NULL	NULL
b	222	NULL	NULL
d	444	d	454
NULL	NULL	f	666
NULL	NULL	g	777
drop  table t1;
880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907
create table t1 (col1 tinyint unsigned, col2 tinyint unsigned);
insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);
select col1 n from t1 union select col2 n from t1 order by n;
n
1
2
3
4
5
6
7
8
9
10
alter table t1 add index myindex (col2);
select col1 n from t1 union select col2 n from t1 order by n;
n
1
2
3
4
5
6
7
8
9
10
drop  table t1;
908 909 910 911 912 913 914 915 916 917 918 919 920 921
create table t1 (i int);
insert into t1 values (1);
select * from t1 UNION select * from t1;
i
1
select * from t1 UNION ALL select * from t1;
i
1
1
select * from t1 UNION select * from t1 UNION ALL select * from t1;
i
1
1
drop table t1;
922 923 924 925 926
select 1 as a union all select 1 union all select 2 union select 1 union all select 2;
a
1
2
2
927 928 929 930 931 932 933 934 935 936 937
set sql_select_limit=1;
select 1 union select 2;
1
1
(select 1) union (select 2);
1
1
(select 1) union (select 2) union (select 3) limit 2;
1
1
2
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
938
set sql_select_limit=default;
939 940 941 942 943 944 945 946 947 948 949 950 951 952
create table t1 (a int);
insert into t1 values (100), (1);
create table t2 (a int);
insert into t2 values (100);
select a from t1 union select a from t2 order by a;
a
1
100
SET SQL_SELECT_LIMIT=1;
select a from t1 union select a from t2 order by a;
a
1
drop table t1, t2;
set sql_select_limit=default;