subselect.test 4.99 KB
Newer Older
1

unknown's avatar
unknown committed
2
select (select 2);
3
SELECT (SELECT 1) UNION SELECT (SELECT 2);
unknown's avatar
unknown committed
4
drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit;
5 6 7 8 9 10 11
create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
create table t4 (a int, b int);
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
12 13
select (select a from t1 where t1.a=t2.a), a from t2;
select (select a from t1 where t1.a=t2.b), a from t2;
14 15 16 17
select (select a from t1), a from t2;
select (select a from t3), a from t2;
select * from t2 where t2.a=(select a from t1);
insert into t3 values (6),(7),(3);
unknown's avatar
unknown committed
18 19
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
20
union (select * from t4 order by a limit 2) limit 3;
unknown's avatar
unknown committed
21 22
select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
unknown's avatar
unknown committed
23 24
explain select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
unknown's avatar
unknown committed
25 26 27
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
(select * from t2 where a>1) as tt;
unknown's avatar
unknown committed
28 29
explain select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
(select * from t2 where a>1) as tt;
unknown's avatar
unknown committed
30 31 32 33
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);
select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
unknown's avatar
unknown committed
34
explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
unknown's avatar
unknown committed
35 36
select * from t3 where exists (select * from t2 where t2.b=t3.a);
select * from t3 where not exists (select * from t2 where t2.b=t3.a);
unknown's avatar
unknown committed
37 38 39 40 41
insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9);
select b,max(a) as ma from t4 group by b having b < (select max(t2.a)
from t2 where t2.b=t4.b);
select b,max(a) as ma from t4 group by b having b >= (select max(t2.a)
from t2 where t2.b=t4.b);
unknown's avatar
unknown committed
42 43 44 45 46 47
create table t5 (a int);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
insert into t5 values (5);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
insert into t5 values (2);
select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
unknown's avatar
unknown committed
48
explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2;
unknown's avatar
unknown committed
49
-- error 1240
unknown's avatar
unknown committed
50
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
51 52 53 54 55 56
create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq));
create table clinic( uq int primary key, name char(25));
insert into clinic values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
insert into attend values (1,1),(1,2),(2,2),(1,3);
select * from attend where exists (select * from clinic where uq = clinic_uq);

57 58 59 60
# not unique fields
-- error 1052
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);

61 62 63 64 65 66 67 68 69 70 71 72 73
# different tipes & group functions
drop table if exists t1,t2,t3;

CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');
INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');
CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');
INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');
CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00');
INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');
SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);
SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);
SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);

unknown's avatar
unknown committed
74 75 76 77 78 79 80 81 82 83 84 85
drop table if exists inscrit;

CREATE TABLE `inscrit` (
  `pseudo` varchar(35) character set latin1 NOT NULL default '',
  `email` varchar(60) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`pseudo`),
  UNIQUE KEY `email` (`email`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;

INSERT INTO inscrit (pseudo,email) VALUES ('joce','test');
INSERT INTO inscrit (pseudo,email) VALUES ('joce1','test1');
INSERT INTO inscrit (pseudo,email) VALUES ('2joce1','2test1');
unknown's avatar
unknown committed
86 87 88
EXPLAIN SELECT pseudo,(SELECT email FROM inscrit WHERE pseudo=(SELECT
pseudo FROM inscrit WHERE pseudo='joce')) FROM inscrit WHERE pseudo=(SELECT
pseudo FROM inscrit WHERE pseudo='joce');
unknown's avatar
unknown committed
89 90 91 92 93 94
-- error 1239
SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM
inscrit WHERE pseudo='joce');
-- error 1239
SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit WHERE
pseudo='joce');
95
SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo='joce');
unknown's avatar
unknown committed
96 97 98
-- error 1240
SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit WHERE pseudo LIKE '%joce%');

99
drop table if exists t1,t2,t3,t4,t5,attend,clinic,inscrit;