MENU


Subquery

A subquery is a bracketed SELECT statement nested inside another outer statement. The outer statement can be a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement. A subquery is sometimes the only way to formulate a query that cannot otherwise be expressed with a JOIN, and can sometimes execute faster than an equivalent join.

Scalar Subquery

A scalar subquery returns a single row containing a single column.
SELECT (SELECT 500);
-- 500

CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (200);
CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (200),(300),(400);
SELECT * FROM t2 WHERE b>(SELECT * FROM t1);
-- 300, 400
SELECT * FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);

SELECT * FROM t1 AS t
WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

SELECT (SELECT 500);

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (200);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (200),(300),(400);

SELECT * FROM t2 WHERE b > (SELECT * FROM t1);

(SELECT 500)
500
b
300
400

Column Subquery

A column subquery returns a single column over multiple rows. SOME() and ANY() are synonyms.
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
SELECT a FROM t1 WHERE b > ANY (TABLE t2);
-- equivalent to:
SELECT a FROM t1 WHERE b > ANY (SELECT * FROM t2);
IN tests column membership:
SELECT b FROM t2 WHERE b IN (SELECT a FROM t1);
-- 300, 400
SHOW DATABASES WHERE `database` IN
('mysql', 'information_schema', 'performance_schema');
ALL requires the comparison to hold against every row returned by the subquery:
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...).
SELECT EXISTS (SELECT a FROM t1);
-- 1

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);

SELECT b FROM t2 WHERE b IN (SELECT a FROM t1);

SELECT b FROM t2 WHERE b > ALL(SELECT a FROM t1);

SELECT EXISTS (SELECT a FROM t1);

SELECT b FROM t2 WHERE b > ANY(SELECT a FROM t1):
b
300
400
500
SELECT b FROM t2 WHERE b IN (SELECT a FROM t1):
b
300
400
SELECT b FROM t2 WHERE b > ALL(SELECT a FROM t1):
b
500
SELECT EXISTS (SELECT a FROM t1):
EXISTS (SELECT a FROM t1)
1
From a HackerRank challenge — select city names from STATION whose first and last characters are both vowels, without duplicates:
SELECT DISTINCT CITY FROM STATION
WHERE LEFT(CITY,1) IN ('a','e','i','o','u') AND
RIGHT(CITY,1) IN ('a','e','i','o','u');

Table Subquery

A table subquery returns multiple columns and rows, and can be compared against a row constructor:
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
A table subquery may also appear in the FROM clause of a SELECT statement, in addition to the WHERE / HAVING clause:
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);

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);

SELECT col1,col2 FROM
  (SELECT a AS col1,b AS col2 FROM t1) AS t WHERE t.col1>2;

SELECT * FROM t2 WHERE (c,d) IN (SELECT a,b FROM t1):
cd
3300
4400
SELECT col1,col2 FROM (SELECT a AS col1,b AS col2 FROM t1) AS t WHERE t.col1>2:
col1col2
3300
4400

Lateral Derived Tables

A lateral derived table is a derived table that can reference columns from preceding tables in the same FROM clause, allowing calculations or aggregations against data from multiple tables in a single query:
FROM table1 JOIN LATERAL (subquery)
The subquery inside the lateral derived table can reference columns from table1, and can use those values to perform calculations or aggregations that feed back into the outer query:
SELECT customer_id, total_sales
FROM customers C,
LATERAL (
SELECT SUM(amount) AS total_sales
FROM orders
WHERE customer_id = C.customer_id
) AS O;
Without the LATERAL keyword, an error is thrown, because C would not be recognized inside the subquery.

See also CTE for a related way to factor out reusable query blocks, and Data Retrieval for JOIN syntax. 

CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(64));
CREATE TABLE orders (order_id INT, customer_id INT, amount DECIMAL(10,2));

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO orders VALUES (1, 1, 50.00), (2, 1, 75.00), (3, 2, 20.00);

-- The subquery inside LATERAL(...) can reference C.customer_id,
-- a preceding table in the same FROM clause.
SELECT customer_id, total_sales
FROM customers C,
LATERAL (
  SELECT SUM(amount) AS total_sales
  FROM orders
  WHERE customer_id = C.customer_id
) AS O;

customer_idtotal_sales
1125.00
220.00