DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (a , b ) ENGINE= ; INSERT INTO t1 (a,b) VALUES (100,'foobar'),(1,'z'),(200,'bar'); CREATE TABLE t2 (a , b ) ENGINE= ; INSERT INTO t2 (a,b) SELECT a, b FROM t1; INSERT INTO t1 (a,b) SELECT a, b FROM t2; SELECT * FROM t1; a b 1 z 1 z 100 foobar 100 foobar 200 bar 200 bar SELECT DISTINCT a FROM t1; a 1 100 200 SELECT ALL b, a FROM t1; b a bar 200 bar 200 foobar 100 foobar 100 z 1 z 1 SELECT STRAIGHT_JOIN SQL_CACHE t1.* FROM t2, t1 WHERE t1.a <> t2.a; a b 1 z 1 z 1 z 1 z 100 foobar 100 foobar 100 foobar 100 foobar 200 bar 200 bar 200 bar 200 bar SELECT SQL_SMALL_RESULT SQL_NO_CACHE t1.a FROM t1, t2; a 1 1 1 1 1 1 100 100 100 100 100 100 200 200 200 200 200 200 SELECT SQL_BIG_RESULT SQL_CALC_FOUND_ROWS DISTINCT(t2.a) FROM t1 t1_1, t2, t1 t1_2; a 1 100 200 SELECT FOUND_ROWS(); FOUND_ROWS() 3 SET GLOBAL query_cache_type = ON; SET query_cache_type = ON; SELECT SQL_CACHE * FROM t1, t2; a b a b 1 z 1 z 1 z 1 z 1 z 100 foobar 1 z 100 foobar 1 z 200 bar 1 z 200 bar 100 foobar 1 z 100 foobar 1 z 100 foobar 100 foobar 100 foobar 100 foobar 100 foobar 200 bar 100 foobar 200 bar 200 bar 1 z 200 bar 1 z 200 bar 100 foobar 200 bar 100 foobar 200 bar 200 bar 200 bar 200 bar SET GLOBAL query_cache_type = DEFAULT; SELECT a+10 AS field1, CONCAT(b,':',b) AS field2 FROM t1 WHERE b > 'b' AND a IS NOT NULL GROUP BY 2 DESC, field1 ASC HAVING field1 < 1000 ORDER BY field2, 1 DESC, field1*2 LIMIT 5 OFFSET 1 ROWS EXAMINED 100000; field1 field2 11 z:z 110 foobar:foobar SELECT SUM(a), MAX(a), b FROM t1 GROUP BY b WITH ROLLUP; SUM(a) MAX(a) b 2 1 z 200 100 foobar 400 200 bar 602 200 NULL SELECT * FROM t2 WHERE a>0 PROCEDURE ANALYSE(); Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype test.t2.a 1 200 1 3 0 0 100.3333 81.2418 ENUM('1','100','200') NOT NULL test.t2.b bar z 1 6 0 0 3.3333 NULL ENUM('bar','foobar','z') NOT NULL SELECT t1.a, t2.b FROM t2, t1 WHERE t1.a = t2.a ORDER BY t2.b, t1.a INTO OUTFILE '/select.out' CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''''; 200,'bar' 200,'bar' 100,'foobar' 100,'foobar' 1,'z' 1,'z' SELECT t1.a, t2.b FROM t2, t1 WHERE t1.a = t2.a ORDER BY t2.b, t1.a INTO DUMPFILE '/select.dump'; ERROR 42000: Result consisted of more than one row SELECT t1.*, t2.* FROM t1, t2 ORDER BY t2.b, t1.a, t2.a, t1.b LIMIT 1 INTO DUMPFILE '/select.dump'; 1z200bar SELECT MIN(a), MAX(a) FROM t1 INTO @min, @max; SELECT @min, @max; @min @max 1 200 SELECT t1_1.*, t2.* FROM t2, t1 AS t1_1, t1 AS t1_2 WHERE t1_1.a = t1_2.a AND t2.a = t1_1.a; a b a b 1 z 1 z 1 z 1 z 1 z 1 z 1 z 1 z 100 foobar 100 foobar 100 foobar 100 foobar 100 foobar 100 foobar 100 foobar 100 foobar 200 bar 200 bar 200 bar 200 bar 200 bar 200 bar 200 bar 200 bar SELECT alias1.* FROM ( SELECT a,b FROM t1 ) alias1, t2 WHERE t2.a IN (100,200); a b 1 z 1 z 1 z 1 z 100 foobar 100 foobar 100 foobar 100 foobar 200 bar 200 bar 200 bar 200 bar SELECT t1.a FROM { OJ t1 LEFT OUTER JOIN t2 ON t1.a = t2.a+10 }; a 1 1 100 100 200 200 SELECT t1.* FROM t2 INNER JOIN t1; a b 1 z 1 z 1 z 1 z 1 z 1 z 100 foobar 100 foobar 100 foobar 100 foobar 100 foobar 100 foobar 200 bar 200 bar 200 bar 200 bar 200 bar 200 bar SELECT t1_2.* FROM t1 t1_1 CROSS JOIN t1 t1_2 ON t1_1.b = t1_2.b; a b 1 z 1 z 1 z 1 z 100 foobar 100 foobar 100 foobar 100 foobar 200 bar 200 bar 200 bar 200 bar SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 WHERE t1.b > t2.b; a b 1 bar 1 bar 1 foobar 1 foobar 100 bar 100 bar SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 ON t1.b > t2.b ORDER BY t1.a, t2.b; a b 1 bar 1 bar 1 foobar 1 foobar 100 bar 100 bar SELECT t2.* FROM t1 LEFT JOIN t2 USING (a) ORDER BY t2.a, t2.b LIMIT 1; a b 1 z SELECT t2.* FROM t2 LEFT OUTER JOIN t1 ON t1.a = t2.a WHERE t1.a IS NOT NULL; a b 1 z 1 z 100 foobar 100 foobar 200 bar 200 bar SELECT SUM(t2.a) FROM t1 RIGHT JOIN t2 ON t2.b = t1.b; SUM(t2.a) 602 SELECT MIN(t2.a) FROM t1 RIGHT OUTER JOIN t2 USING (b,a); MIN(t2.a) 1 SELECT alias.b FROM t1 NATURAL JOIN ( SELECT a,b FROM t1 ) alias WHERE b > ''; b bar bar bar bar foobar foobar foobar foobar z z z z SELECT t2.b FROM ( SELECT a,b FROM t1 ) alias NATURAL LEFT JOIN t2 WHERE b IS NOT NULL; b bar bar foobar foobar z z SELECT t1.*, t2.* FROM t1 NATURAL LEFT OUTER JOIN t2; a b a b 1 z 1 z 1 z 1 z 100 foobar 100 foobar 100 foobar 100 foobar 200 bar 200 bar 200 bar 200 bar SELECT t2_2.* FROM t2 t2_1 NATURAL RIGHT JOIN t2 t2_2 WHERE t2_1.a IN ( SELECT a FROM t1 ); a b 1 z 100 foobar 200 bar SELECT t1_2.b FROM t1 t1_1 NATURAL RIGHT OUTER JOIN t1 t1_2 INNER JOIN t2; b bar bar bar bar bar bar bar bar bar bar bar bar foobar foobar foobar foobar foobar foobar foobar foobar foobar foobar foobar foobar z z z z z z z z z z z z SELECT ( SELECT MIN(a) FROM ( SELECT a,b FROM t1 ) alias1 ) AS min_a FROM t2; min_a 1 1 1 SELECT a,b FROM t2 WHERE a = ( SELECT MIN(a) FROM t1 ); a b 1 z SELECT a,b FROM t2 WHERE b LIKE ( SELECT b FROM t1 ORDER BY b LIMIT 1 ); a b 200 bar SELECT t2.* FROM t1 t1_outer, t2 WHERE ( t1_outer.a, t2.b ) IN ( SELECT a, b FROM t2 WHERE a = t1_outer.a ); a b 1 z 1 z 100 foobar 100 foobar 200 bar 200 bar SELECT a,b FROM t2 WHERE b = ANY ( SELECT b FROM t1 WHERE a > 1 ); a b 100 foobar 200 bar SELECT a,b FROM t2 WHERE b > ALL ( SELECT b FROM t1 WHERE b < 'foo' ); a b 1 z 100 foobar SELECT a,b FROM t1 WHERE ROW(a, b) = ( SELECT a, b FROM t2 ORDER BY a, b LIMIT 1 ); a b 1 z 1 z SELECT a,b FROM t1 WHERE EXISTS ( SELECT a,b FROM t2 WHERE t2.b > t1.b ); a b 100 foobar 100 foobar 200 bar 200 bar SELECT t1.* FROM t1, t2 ORDER BY ( SELECT b FROM t1 WHERE a IS NULL ORDER BY b LIMIT 1 ) DESC; a b 1 z 1 z 1 z 1 z 1 z 1 z 100 foobar 100 foobar 100 foobar 100 foobar 100 foobar 100 foobar 200 bar 200 bar 200 bar 200 bar 200 bar 200 bar SELECT a, b FROM t1 HAVING a IN ( SELECT a FROM t2 WHERE b = t1.b ); a b 1 z 1 z 100 foobar 100 foobar 200 bar 200 bar SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION DISTINCT SELECT a,b FROM t1; a b 1 z 100 foobar 200 bar SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION ALL SELECT a,b FROM t1; a b 1 z 1 z 1 z 100 foobar 100 foobar 100 foobar 200 bar 200 bar 200 bar DROP TABLE t1, t2;