Subquery Basics
In this page:
SELECT column1
FROM table_name
WHERE column2 operator (SELECT column FROM other_table);What is a Subquery?
A subquery is a nested query written inside another SQL query, wrapped in parentheses. The inner query runs first and passes its results to the outer query, which then uses that result as if it were a literal value or table.
Example: What is a Subquery?
CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 10), (2, 50), (3, 90);
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);Scalar Subqueries
A scalar subquery returns exactly one value (one row and one column), such as the average price across all products. You can use standard comparison operators like =, <, or > with scalar subqueries directly in a WHERE clause.
Example: Scalar Subqueries
CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 10), (2, 50), (3, 90);
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);Multi-Row Subqueries
A multi-row subquery returns a list of values instead of a single one, such as all customer IDs from a specific region. Because it returns multiple rows, you must use operators like IN, ANY, or ALL to compare values against the whole list.
Example: Multi-Row Subqueries
CREATE TABLE customers (id INT, region TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'North'), (2, 'South');
INSERT INTO orders VALUES (101, 1), (102, 2);
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'North');Subqueries in the SELECT Clause
You can use a subquery inside your SELECT clause to perform row-by-row calculations. This is useful for fetching counts from related tables, like showing each customer alongside their total number of orders.
Example: Subqueries in the SELECT Clause
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1), (102, 1);
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count
FROM customers;Subqueries in the FROM Clause
You can use a subquery as a temporary table in your FROM clause, useful for pre-filtering or pre-aggregating data before joining it. In MySQL, you must assign an alias name to subqueries used in the FROM clause, or the query will fail.
Example: Subqueries in the FROM Clause
CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO orders VALUES (1, 1, 50), (2, 1, 70), (3, 2, 20);
SELECT customer_id, spend
FROM (SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id) AS totals
WHERE spend > 30;- Using a scalar subquery that returns more than one row with
=, which gives an error. - Forgetting the parentheses around the subquery.
- Writing a subquery that returns several columns where one is expected.
Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: