DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=xengine; INSERT INTO t1 (a,b) VALUES (100,'foobar'),(1,'z'),(200,'bar'); CREATE TABLE t2 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=xengine; 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 pk 1 z 2 1 z 5 100 foobar 1 100 foobar 4 200 bar 3 200 bar 6 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 t1.* FROM t2, t1 WHERE t1.a <> t2.a; a b pk 1 z 2 1 z 2 1 z 5 1 z 5 100 foobar 1 100 foobar 1 100 foobar 4 100 foobar 4 200 bar 3 200 bar 3 200 bar 6 200 bar 6 SELECT SQL_SMALL_RESULT 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 Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. Warnings: SELECT FOUND_ROWS(); FOUND_ROWS() 3 Warnings: Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. SELECT a+10 AS field1, CONCAT(b,':',b) AS field2 FROM t1 WHERE b > 'b' AND a IS NOT NULL GROUP BY 2, field1 HAVING field1 < 1000 ORDER BY field2, 1 DESC, field1*2 LIMIT 5 OFFSET 1; 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 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 ''''; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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, t1.pk, t2.pk LIMIT 1 INTO DUMPFILE '/select.dump'; 1z2200bar3 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 pk a b pk 1 z 2 1 z 2 1 z 2 1 z 2 1 z 5 1 z 2 1 z 5 1 z 2 100 foobar 1 100 foobar 1 100 foobar 1 100 foobar 1 100 foobar 4 100 foobar 1 100 foobar 4 100 foobar 1 200 bar 3 200 bar 3 200 bar 3 200 bar 3 200 bar 6 200 bar 3 200 bar 6 200 bar 3 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 pk 1 z 2 1 z 2 1 z 2 1 z 5 1 z 5 1 z 5 100 foobar 1 100 foobar 1 100 foobar 1 100 foobar 4 100 foobar 4 100 foobar 4 200 bar 3 200 bar 3 200 bar 3 200 bar 6 200 bar 6 200 bar 6 SELECT t1_2.* FROM t1 t1_1 CROSS JOIN t1 t1_2 ON t1_1.b = t1_2.b; a b pk 1 z 2 1 z 2 1 z 5 1 z 5 100 foobar 1 100 foobar 1 100 foobar 4 100 foobar 4 200 bar 3 200 bar 3 200 bar 6 200 bar 6 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 pk 1 z 2 SELECT t2.* FROM t2 LEFT OUTER JOIN t1 ON t1.a = t2.a WHERE t1.a IS NOT NULL; a b pk 1 z 2 1 z 2 100 foobar 1 100 foobar 1 200 bar 3 200 bar 3 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 pk a b pk 1 z 2 1 z 2 1 z 5 NULL NULL NULL 100 foobar 1 100 foobar 1 100 foobar 4 NULL NULL NULL 200 bar 3 200 bar 3 200 bar 6 NULL NULL NULL SELECT t2_2.* FROM t2 t2_1 NATURAL RIGHT JOIN t2 t2_2 WHERE t2_1.a IN ( SELECT a FROM t1 ); a b pk 1 z 2 100 foobar 1 200 bar 3 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 foobar foobar foobar foobar foobar foobar 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 pk 1 z 2 1 z 2 100 foobar 1 100 foobar 1 200 bar 3 200 bar 3 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 pk 1 z 2 1 z 2 1 z 2 1 z 5 1 z 5 1 z 5 100 foobar 1 100 foobar 1 100 foobar 1 100 foobar 4 100 foobar 4 100 foobar 4 200 bar 3 200 bar 3 200 bar 3 200 bar 6 200 bar 6 200 bar 6 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;