Subquery in WHERE
In this page:
Filtering with Scalar Values
You can use a scalar subquery inside your WHERE clause to filter rows dynamically. The outer query compares column values against the single value returned by the inner query, such as finding products priced above the overall average.
Example: Filtering with Scalar Values
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);Using Subqueries with IN
You can combine a subquery with the IN operator inside your WHERE clause instead of hardcoding a list. This filters for rows where a column value matches any item in the subquery's result list, keeping the filter data-driven.
Example: Using Subqueries with IN
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');Using Subqueries with NOT IN
You can use NOT IN to exclude rows based on a subquery's results. This returns rows where a column value does not exist in the list returned by your subquery, such as customers who have never placed an order.
Example: Using Subqueries with NOT IN
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1);
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);Correlated Subqueries in WHERE
A correlated subquery is an inner query that references columns from the outer query, creating a dependency between the two. It executes once for each row evaluated by the outer query, unlike a regular subquery that runs just once.
Example: Correlated Subqueries in WHERE
CREATE TABLE products (id INT, category TEXT, price INT);
INSERT INTO products VALUES (1, 'Books', 10), (2, 'Books', 40), (3, 'Toys', 20);
SELECT * FROM products p1
WHERE price > (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category);EXISTS Operator in WHERE
The EXISTS operator checks if a subquery returns any rows, without caring what those rows actually contain. If the subquery returns at least one row, the EXISTS condition evaluates to true, otherwise it's false.
Example: EXISTS Operator in WHERE
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1);
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: