SQL Subqueries — Nested SELECT Statements

Table Hierarchy & Schema

Database → tables

CREATE TABLE (reference)json

1CREATE TABLE users (
2  user_id   INTEGER PRIMARY KEY,
3  email     VARCHAR(255) NOT NULL UNIQUE,
4  is_active BOOLEAN NOT NULL DEFAULT TRUE
5);
6
7CREATE TABLE orders (
8  order_id     INTEGER PRIMARY KEY,
9  customer_id  INTEGER NOT NULL,
10  status       VARCHAR(20) NOT NULL DEFAULT 'pending',
11  total_amount DECIMAL(10, 2) NOT NULL,
12  FOREIGN KEY (customer_id) REFERENCES users (user_id)
13);

Entity-Relationship Diagram

users ↔ orders ER

Subquery Flow

Inner query result feeds outer query

Sample Data

usersjson

1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |

ordersjson

1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |

Average order total in sample: (49.99 + 12.50 + 8.00) / 3 = 23.50.

Scalar Subquery in WHERE

Orders above the average totaljson

1SELECT order_id, customer_id, total_amount
2FROM orders
3WHERE total_amount > (
4  SELECT AVG(total_amount) FROM orders
5);

Expected outputjson

1| order_id | customer_id | total_amount |
2|---|---|---|
3| 1001 | 1 | 49.99 |

IN Subquery

Users who have at least one shipped orderjson

1SELECT email
2FROM users
3WHERE user_id IN (
4  SELECT customer_id FROM orders WHERE status = 'shipped'
5);

Expected outputjson

1| email |
2|---|
3| [email protected] |

EXISTS Preview

Same filter with EXISTSjson

1SELECT email
2FROM users u
3WHERE EXISTS (
4  SELECT 1 FROM orders o
5  WHERE o.customer_id = u.user_id AND o.status = 'shipped'
6);

EXISTS vs IN

EXISTS stops at the first match and handles NULLs safely in many cases. Prefer EXISTS for large related tables.

Subquery vs JOIN

Approach	Strength	Trade-off
Subquery in WHERE	Reads like a question in plain English	May run inner query repeatedly if correlated
JOIN	Often faster; returns columns from both tables	Can duplicate rows without careful GROUP BY

Constraints Reminder

Constraint	Role in subqueries
PRIMARY KEY (user_id / order_id)	Uniquely identifies each row; join anchor
FOREIGN KEY (customer_id)	Order must reference a valid users.user_id
NOT NULL	Required columns cannot be missing on INSERT/UPDATE
UNIQUE (email)	No duplicate emails in users
DEFAULT (status, is_active)	Value applied when column omitted on INSERT

Common Mistakes

Scalar subquery returning multiple rows → runtime error.
NOT IN with NULLs in the subquery result → unexpected empty results.
Over-nesting subqueries when a JOIN would be clearer.

Practice Prompts

Find users whose user_id is not in the orders customer_id list (never ordered).
Which order has the maximum total_amount? (Hint: scalar subquery or ORDER BY LIMIT 1.)
Rewrite the IN subquery above as an INNER JOIN and compare results.

Try These Tools

SQL Query Explainer

Break down nested SELECT logic

Open tool

SQL Validator

Check subquery syntax

Open tool

Continue Learning

LEFT JOIN

Join alternative for optional matches

GROUP BY & Aggregates

Aggregates inside subqueries

Frequently Asked Questions

What is a subquery?

A SELECT nested inside another statement — commonly in WHERE, FROM, or SELECT. The inner query runs first (or per row for correlated subqueries).

When should I use IN with a subquery?

When you need rows where a column value appears in a set returned by another query, e.g. customer_id IN (SELECT …).

What is a scalar subquery?

A subquery that returns exactly one row and one column — often an aggregate like AVG(total_amount) used in a comparison.

Subquery vs JOIN?

Many subqueries can be rewritten as JOINs. Subqueries read clearly for filters; JOINs often perform better on large data.

What is EXISTS?

EXISTS (subquery) returns true if the subquery returns any row — useful for "users who have at least one order" without listing order ids.