MENU


Column Subquery

A column subquery returns a single column over multiple rows.
SOME() and ANY() are the same.
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (100),(200),(300),(400);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (10),(20),(300),(400),(500);
SELECT b FROM t2 WHERE b > ANY(SELECT a FROM t1);

300
400
500

DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (100),(200),(300),(400);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (10),(20),(300),(400),(500);
SELECT b FROM t2 WHERE b IN (SELECT a FROM t1);

300
400

DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (100),(200),(300),(400);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (10),(20),(300),(400),(500);
SELECT b FROM t2 WHERE b > ALL(SELECT a FROM t1);

500
EXISTS(SELECT…) returns TRUE if one or more rows are returned. The opposite is NOT EXISTS(SELECT…).
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (100),(200),(300),(400);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (10),(20),(300),(400),(500);
SELECT EXISTS (SELECT a FROM t1);

1