MENU


Table Subquery


DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,100),(2,200),(3,300),(4,400);
CREATE TABLE t2 (c INT, d INT);
INSERT INTO t2 VALUES (1,10),(2,20),(3,300),(4,400),(5,500);
SELECT * FROM t2 WHERE (c,d) IN (SELECT a,b FROM t1);

3     300
4     400

DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,100),(2,200),(3,300),(4,400);
CREATE TABLE t2 (c INT, d INT);
INSERT INTO t2 VALUES (1,10),(2,20),(3,300),(4,400),(5,500);
SELECT col1,col2 FROM (SELECT a AS col1,b AS col2 FROM t1) AS t WHERE t.col1>2;

3     300
4     400

SELECT * FROM t1  WHERE col1 = ANY (SELECT col1 FROM t2 WHERE t2.col2 = t1.col2);