select (select 2); (select 2) 2 SELECT (SELECT 1) UNION SELECT (SELECT 2); (SELECT 1) 1 2 SELECT (SELECT (SELECT 0 UNION SELECT 0)); (SELECT (SELECT 0 UNION SELECT 0)) 0 SELECT (SELECT 1 FROM (SELECT 1) HAVING a=1) as a; Reference 'a' not supported (forward reference in item list) SELECT (SELECT 1 FROM (SELECT 1) HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) HAVING a=1) as b; Reference 'b' not supported (forward reference in item list) SELECT (SELECT 1),MAX(1) FROM (SELECT 1); (SELECT 1) MAX(1) 1 1 SELECT (SELECT a) as a; Reference 'a' not supported (forward reference in item list) EXPLAIN SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 3 DEPENDENT SUBSELECT No tables used 2 DERIVED No tables used SELECT 1 FROM (SELECT 1 as a) HAVING (SELECT a)=1; 1 1 SELECT (SELECT 1), a; Unknown column 'a' in 'field list' SELECT 1 as a FROM (SELECT 1) HAVING (SELECT a)=1; a 1 SELECT 1 FROM (SELECT (SELECT a)); Unknown column 'a' in 'field list' drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; 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); select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1; Reference 'a1' not supported (forward reference in item list) select (select a from t1 where t1.a=t2.a), a from t2; (select a from t1 where t1.a=t2.a) a NULL 1 2 2 select (select a from t1 where t1.a=t2.b), a from t2; (select a from t1 where t1.a=t2.b) a NULL 1 NULL 2 select (select a from t1), a from t2; (select a from t1) a 2 1 2 2 select (select a from t3), a from t2; (select a from t3) a NULL 1 NULL 2 select * from t2 where t2.a=(select a from t1); a b 2 7 insert into t3 values (6),(7),(3); select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); a b 1 7 2 7 select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) union (select * from t4 order by a limit 2) limit 3; a b 1 7 2 7 3 8 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); a b 1 7 2 7 3 8 4 8 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); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where 2 SUBSELECT t3 ALL NULL NULL NULL NULL 3 Using filesort 3 UNION t4 ALL NULL NULL NULL NULL 3 Using where; Using filesort 4 SUBSELECT t2 ALL NULL NULL NULL NULL 2 select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; (select a from t3 where a<t2.a*4 order by 1 desc limit 1) a 3 1 7 2 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; (select t3.a from t3 where a<8 order by 1 desc limit 1) a 7 2 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; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived3> system NULL NULL NULL NULL 1 3 DERIVED t2 ALL NULL NULL NULL NULL 2 Using where 2 SUBSELECT t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort 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); a 2 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); a 2 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); a select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) 8 7.5000 8 4.5000 9 7.5000 explain select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBSELECT t2 ALL NULL NULL NULL NULL 2 3 DEPENDENT SUBSELECT t3 ALL NULL NULL NULL NULL 3 Using where select * from t3 where exists (select * from t2 where t2.b=t3.a); a 7 select * from t3 where not exists (select * from t2 where t2.b=t3.a); a 6 3 select * from t3 where a in (select b from t2); a 7 select * from t3 where a not in (select b from t2); a 6 3 select * from t3 where a = some (select b from t2); a 7 select * from t3 where a <> any (select b from t2); a 6 3 select * from t3 where a = all (select b from t2); a 7 select * from t3 where a <> all (select b from t2); a 6 3 insert into t2 values (100, 5); select * from t3 where a < any (select b from t2); a 6 3 select * from t3 where a < all (select b from t2); a 3 select * from t3 where a >= any (select b from t2); a 6 7 select * from t3 where a >= all (select b from t2); a 7 delete from t2 where a=100; select * from t3 where a in (select a,b from t2); Subselect returns more than 1 field select * from t3 where a in (select * from t2); Subselect returns more than 1 field 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); b ma select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b); b ma 7 12 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; (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a NULL 1 2 2 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; (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a NULL 1 2 2 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; (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) a NULL 1 2 2 explain select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBSELECT t1 system NULL NULL NULL NULL 1 Using where 3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; Subselect returns more than 1 record create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); create table t7( uq int primary key, name char(25)); insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta"); insert into t6 values (1,1),(1,2),(2,2),(1,3); select * from t6 where exists (select * from t7 where uq = clinic_uq); patient_uq clinic_uq 1 1 1 2 2 2 select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); Column: 'a' in field list is ambiguous 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); a b W 1732-02-22 SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2); a b W 1 SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3); a b W a drop table if exists t8; CREATE TABLE `t8` ( `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 t8 (pseudo,email) VALUES ('joce','test'); INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t8 const PRIMARY PRIMARY 35 const 1 4 SUBSELECT t8 const PRIMARY PRIMARY 35 const 1 2 SUBSELECT t8 const PRIMARY PRIMARY 35 const 1 3 SUBSELECT t8 const PRIMARY PRIMARY 35 const 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); Subselect returns more than 1 field SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE pseudo='joce'); Subselect returns more than 1 field SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); pseudo joce SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%'); Subselect returns more than 1 record drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; CREATE TABLE `t1` ( `topic` mediumint(8) unsigned NOT NULL default '0', `date` date NOT NULL default '0000-00-00', `pseudo` varchar(35) character set latin1 NOT NULL default '', PRIMARY KEY (`pseudo`,`date`,`topic`), KEY `topic` (`topic`) ) TYPE=MyISAM ROW_FORMAT=DYNAMIC; INSERT INTO t1 (topic,date,pseudo) VALUES ('43506','2002-10-02','joce'),('40143','2002-08-03','joce'); EXPLAIN SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 41 NULL 2 Using where; Using index EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY No tables used 2 SUBSELECT t1 index NULL PRIMARY 41 NULL 2 Using where; Using index SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; date 2002-08-03 SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03') 2002-08-03 SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1; 1 1 1 1 SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1; Subselect returns more than 1 record EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL topic 3 NULL 2 Using index 2 SUBSELECT No tables used 3 UNION No tables used drop table t1; CREATE TABLE `t1` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, `maxnumrep` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`numeropost`), UNIQUE KEY `maxnumrep` (`maxnumrep`) ) TYPE=MyISAM ROW_FORMAT=FIXED; INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2); CREATE TABLE `t2` ( `mot` varchar(30) NOT NULL default '', `topic` mediumint(8) unsigned NOT NULL default '0', `date` date NOT NULL default '0000-00-00', `pseudo` varchar(35) NOT NULL default '', PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`) ) TYPE=MyISAM ROW_FORMAT=DYNAMIC; INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce'); select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); a 40143 SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20; numeropost maxnumrep 43506 2 40143 1 SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1); Unknown column 'a' in 'having clause' SELECT 1 IN (SELECT 1 FROM t2 HAVING a); Unknown column 'a' in 'having clause' SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date); mot topic date pseudo joce 40143 2002-10-22 joce joce 43506 2002-10-22 joce SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100); mot topic date pseudo joce 43506 2002-10-22 joce SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1); mot topic date pseudo SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date); mot topic date pseudo joce 40143 2002-10-22 joce joce 43506 2002-10-22 joce SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100); mot topic date pseudo joce 43506 2002-10-22 joce SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1); mot topic date pseudo SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date); mot topic date pseudo joce 40143 2002-10-22 joce SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY date HAVING topic < 4100); mot topic date pseudo joce 40143 2002-10-22 joce SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2); mot topic date pseudo SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2); mot topic date pseudo joce 40143 2002-10-22 joce joce 43506 2002-10-22 joce drop table t1,t2; CREATE TABLE `t1` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, `maxnumrep` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`numeropost`), UNIQUE KEY `maxnumrep` (`maxnumrep`) ) TYPE=MyISAM ROW_FORMAT=FIXED; INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1); select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); Subselect returns more than 1 record select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1); Subselect returns more than 1 record drop table t1; CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b'); INSERT INTO t1 VALUES (); SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) HAVING field='b'); Subselect returns more than 1 record drop table t1; CREATE TABLE `t1` ( `numeropost` mediumint(8) unsigned NOT NULL default '0', `numreponse` int(10) unsigned NOT NULL auto_increment, `pseudo` varchar(35) NOT NULL default '', PRIMARY KEY (`numeropost`,`numreponse`), UNIQUE KEY `numreponse` (`numreponse`), KEY `pseudo` (`pseudo`,`numeropost`) ) TYPE=MyISAM; SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a; Reference 'numreponse' not supported (forward reference in item list) SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; Unknown column 'a' in 'having clause' SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); Subselect returns more than 1 record EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Select tables optimized away EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 2 SUBSELECT Select tables optimized away drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); SELECT 1 FROM (SELECT a FROM t1) HAVING (SELECT a)=1; 1 1 drop table t1; create table t1 (a int NOT NULL, b int, primary key (a)); create table t2 (a int NOT NULL, b int, primary key (a)); insert into t1 values (0, 10),(1, 11),(2, 12); insert into t2 values (1, 21),(2, 22),(3, 23); select * from t1; a b 0 10 1 11 2 12 update t1 set b= (select b from t2 where t1.a = t2.a); select * from t1; a b 0 NULL 1 21 2 22 drop table t1, t2; create table t1 (a int NOT NULL, b int, primary key (a)); create table t2 (a int NOT NULL, b int, primary key (a)); insert into t1 values (0, 10),(1, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); select * from t1; a b 0 10 1 11 2 12 select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b = (select b from t2 where t1.a = t2.a); select * from t1; a b 0 10 1 11 drop table t1, t2; drop table if exists t11, t12, t2; create table t11 (a int NOT NULL, b int, primary key (a)); create table t12 (a int NOT NULL, b int, primary key (a)); create table t2 (a int NOT NULL, b int, primary key (a)); insert into t11 values (0, 10),(1, 11),(2, 12); insert into t12 values (33, 10),(22, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); select * from t11; a b 0 10 1 11 2 12 select * from t12; a b 33 10 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); select * from t11; a b 0 10 1 11 select * from t12; a b 33 10 22 11 drop table t11, t12, t2; CREATE TABLE t1 (x int); create table t2 (a int); insert into t2 values (1); INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); select * from t1; x 1 insert into t2 values (1); INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2)); select * from t1; x 1 2 INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2; select * from t1; x 1 2 3 3 INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2; INSERT TABLE 't1' isn't allowed in FROM table list INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t1)); select * from t1; x 1 2 3 3 9 drop table t1, t2; CREATE TABLE t1 (x int not null, y int, primary key (x)); create table t2 (a int); insert into t2 values (1); select * from t1; x y replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); select * from t1; x y 1 2 replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2)); select * from t1; x y 1 3 replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2)); select * from t1; x y 1 3 4 1 replace DELAYED into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2)); select * from t1; x y 1 3 4 2 replace LOW_PRIORITY into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2)); select * from t1; x y 1 3 4 2 2 1 drop table t1, t2; SELECT * FROM (SELECT 1) WHERE 1 IN (SELECT *); No tables used drop table if exists t; CREATE TABLE t (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1; INSERT INTO t VALUES (1),(2); SELECT * FROM t WHERE id IN (SELECT 1); id 1 EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ref id id 5 const 1 Using where; Using index Warnings: Note 1246 Select 2 was reduced during optimisation SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3); id 1 EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index NULL id 5 NULL 2 Using where; Using index 2 DEPENDENT SUBSELECT No tables used 3 UNION No tables used SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2); id 2 drop table if exists t;